|
Dear Rupesh,
As I don't have MS Access in my system.
So I was forced to solve the problem in SQL Server 2005. But the syntax will remain same in both the cases(or may be minor changes which I hope you can do).
I have created a table[TBLSEARCH] with the following values
a
b
c
a
b
c
AC
AD
AD
AE
AE
AF
AF
AFGTHY
BE
BE
And my Stored Proc goes like this
ALTER PROCEDURE sp_Search
-- Add the parameters for the stored procedure here
(@SEARCHSTRING VARCHAR(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- SEARCH WITH ABC COMBINATION
IF(UPPER(@SEARCHSTRING) = 'ABC')
BEGIN
SELECT *
FROM TBLSEARCH WHERE RTRIM(LTRIM(NAME)) LIKE '[A-C]%'
END
-- SEARCH WITH DEF COMBINATION
IF(UPPER(@SEARCHSTRING) = 'DEF')
BEGIN
SELECT *
FROM TBLSEARCH WHERE
RTRIM(LTRIM(NAME)) LIKE 'A[DEF]%' OR
RTRIM(LTRIM(NAME)) LIKE 'B[DEF]%' OR
RTRIM(LTRIM(NAME)) LIKE 'C[DEF]%'
END
-- SEARCH WITH GHI COMBINATION
IF(UPPER(@SEARCHSTRING) = 'GHI')
BEGIN
SELECT *
FROM TBLSEARCH WHERE
RTRIM(LTRIM(NAME)) LIKE 'A[DEF][GHI]%' OR
RTRIM(LTRIM(NAME)) LIKE 'B[DEF][GHI]%' OR
RTRIM(LTRIM(NAME)) LIKE 'C[DEF][GHI]%'
END
END
GO
N.B.~ The @SEARCHSTRING can assume values either 'ABC' or 'DEF' or 'GHI' [ As your button names ]
In my system I am getting the correct output.
Please check it and let me know if it is working as per your expectation or not.
Regards,
Niladri Biswas
Niladri Biswas
modified on Wednesday, June 24, 2009 8:40 AM
|
|
|
|
|
You can do letter groupings in a LIKE clause using SQL2005? cool!
|
|
|
|
|
|
first of all many many thanks. 5 from me
and sorry for late reply since i am busy with some other project.so i do not reply you
This query works well.
one more thing
whether you have any idea of SQLite. since i just check and run this query in sqlite. but i don't know the syntax which is similar to above syntax (as you suggest)
|
|
|
|
|
Hello,
we have huge SQL database on remote server which slow down the application performance because of low connection bandwidth and heavy traffic.
I want to create proxy server on local SOL server. I want that sync to happen automatically between servers and no user interaction required to do so.
Is it possible and how?
Thanks a lot.
|
|
|
|
|
Google SQL Server Replication.
|
|
|
|
|
Hi all,
i have a doubt about my database,i have a lot of currency fields in my database but i don't know(for my poor knowledge) which is the best option in my case to choose the right datatype money or decimal.
I give you more detail when i finish the database i will use it with a software (WPF) and i must use many currency field to apply calculation,percentage,etc etc. so i need your help if you can advice me to choose the right datatype in my case.
If you need more details don't hesitate to ask i will reply as soon as possible.
Thanks so much. 
|
|
|
|
|
Money Data Type
Money is compatible for currency with dollar and cent format. The datatype is accurate to 10 thousand times of the monetary unit.
If the field is capable enough to store large aggregates rather than just the largest value, it is recommended to go with Money data type.
Also with money, you have the choice to use thousand of separators.
Decimal Data Type
Decimal is more flexible, not specific to currency. Suppose you have to support 10 numbers and 2 decimals. In this context decimal is a better choice. Also, decimal is more precise( since we can set precision) and scale to numbers.
Coming to your question directly, what will be the datatype for calculation,percentage?
In case of percentage(e.g. 10.234), there is no case of currency involve. So decimal is the right choice.Also, you can apply precision over there.
For the choice of calculation, you didn't specify, what kind of calculation it is! However, if is mathematical calculations of currency or any aggregation of the same(e.g. Add,Multiply etc. which will be in fraction), go with money datatype.
Niladri Biswas
modified on Tuesday, June 23, 2009 11:45 PM
|
|
|
|
|
Hi Mr.Niladri,
thanks for your useful answer i can understand better about Money And Decimal;
Niladri_Biswas wrote: For the choice of calculation, you didn't specify, what kind of calculation it is! However, if is mathematical calculations of currency or any aggregation of the same(e.g. Add,Multiply etc. which will be in fraction), go with money datatype.
I need to do many and long calculations of currency as Add,Multiple,Divide and so on and if i understood well for this feature better choice money datatype.
Have a good day.
|
|
|
|
|
Niladri Biswas
|
|
|
|
|
I have written a program with a database attached. When I publish it on another computer and try to open up a form that reads from the database, I get an error message that the database is not found. When opening up the folders I find the database in another folder. After moving it to the folder the program looks for it works.
How can I correct this problem?
|
|
|
|
|
Check the configuration path and the credentials(i.e. User Id, Pwd, Database name etc.] .
May be the issue is lying there.
Niladri Biswas
|
|
|
|
|
I posted this question on MSDN as well and got a reply, which although it was wrong gave me the direction. In the application dialog I changed the setting for the database from "Include" to "Data" and then it deployed successfully.
|
|
|
|
|
What method of deployment are you using?
|
|
|
|
|
|
Where is your database publishing to, and where are you expecting it to publish?
ClickOnce kinda takes over when it comes to putting files in whatever locations. It stores some stuff at kind of a master level, then some stuff it will put into windows user specific folders. Something like:
C:\Documents and Settings\WindowsUserName\Local Settings\Apps\2.0\Data\PA61NQME.N93\0BG3BVYZ.BGR\AnotherWierdFolderName\Data
Where several folders are named with some random characters. This will be a different path on EVERY user's computer. So in your code you can't specify this path with a constant. You can reference it with this, though: My.Computer.FileSystem.SpecialDirectories.CurrentUserApplicationData
If you are new to using ClickOnce, I recommend reading up on it. If you plan to publish different versions of your app, you'll want to make sure you've got all the right settings, etc. Or the second version of your app may cause the database to overwrite when a user updates. Here are some references I've used:
microsoft's documentation[^]
More about data directory[^]
...there was another article that really helped me get started but I can't find it. I'll do some more looking and post if I find it.
Hope this helps.
|
|
|
|
|
Hi
I want to select records 11 to 20 in sql server. In MySQL this is how it is done. How can we do it in SQL SERVER 2005 and above?
SELECT * FROM employees LIMIT 10,10;
In MySQL, LIMIT x,y means skip the first x records, and then return the next y records.
Thanks
Pankaj
|
|
|
|
|
You need to use ranking function which are available in microsoft sql server.
|
|
|
|
|
select top 10 from employees where id between 11 and 20
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
what is id here in your select?
Basically, what I am looking for is to select rows paging like functionality. I don't want to bind any table column in select.
|
|
|
|
|
ID supposed to be column name of your table Employee.I used name ID of column but it may be different name.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
It is pretty simple in SQL SERVER 2005 (onwards) too.
Use ROW_NUMBER() function.
I have a table(say CHOOSERECORDS) with only 1 column(say RECORDS) of type int(Created for answering you question only)
Now I have inserted values from 1 to 100 there.
Next I want to select records from say 11 to 20. The query is here
SELECT A.RECORDS FROM (SELECT ROW_NUMBER() OVER (ORDER BY RECORDS) AS ROWID,RECORDS FROM CHOOSERECORDS) A
WHERE A.ROWID BETWEEN 11 AND 20
Hope this answers your question.
Vote me please
Niladri Biswas
|
|
|
|
|
Thanks Niladri. Exactly what I was looking for.
|
|
|
|
|
I can't figure it using the data GetSchema() returns.
Is there something I don't see?
Is there any other way?
Thanks!
Edit: I'm using SQL Server 2008 and SQLConnection
|
|
|
|
|
try linking the sysobjects table to the syscolumns table and find the datatype in the syscolumns table.
I'll try to come up with the query later.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
