ok you are going right when you are using IN operator
IN operator works like this
select * from table where GUID in ('123','23423','34535')
so you have to convert your GUIDs 44-33-22,55-33-44, 33-33-44 to
'44-33-22','55-33-44', '33-33-44'
before passing it to IN in select
so you have to create you own function to do this conversion
in this function first split you GUID string to arrays then create a loop to add single quotes to each item and then reconcatenate to make a single string.
try it if you are unable to do this, tell me i will give you the function
If you feel helped. Please mark this post as Answer
In your example I want to replase '123','23423','34535' whit a call to a sting, so I have
select * from table where GUID in (@mystring)
mystring=" '123','23423','34535' ";
var
cmd3 = newSqlCommand(sql3,
conn);
cmd3.Parameters.AddWithValue(
"guid",
mystring);
The problem in this is that this will give the result as if im searching for the hole string instead
of 3 values. And the reason I want to do it like this, Is that my numers of values in the instatement are changing
fernlle
None
0 Points
5 Posts
How to search with variable parameters?
Apr 02, 2012 12:27 PM|LINK
How to search with variable parameters?
I have a string
String GUID= ds.Tables[0].Rows[0][“GUID”] ToSring();
This returns some a string like:
GUID=“345676,455676,5667788”
or it could be GUID=”34567, 45567,556788, 55555,4444”
This gives me changing GUIDs to search for in my database, so I have tried:
SELECT * FROM database WHERE GUID IN (@guid);
cmd.Parameters.AddWithValue(“@guid”, GUID);
How can I get the SELECT statement to look for each GUID in my string?
neilpeterson
Member
282 Points
51 Posts
Re: How to search with variable parameters?
Apr 02, 2012 12:33 PM|LINK
use single comma for at begin and end for each guid and if column is int then convert it to varchar. i.e.:
SELECT * FROM database WHERE convert(varchar,GUID) IN ('34567, 45567,556788', '55555,4444');
tdmca
Contributor
2396 Points
661 Posts
Re: How to search with variable parameters?
Apr 02, 2012 12:35 PM|LINK
you are going right what you want to ask make more clear.
if you want to say GUID=345676,455676,5667788 are three GUIDs and these three you want to search
fernlle
None
0 Points
5 Posts
Re: How to search with variable parameters?
Apr 02, 2012 12:42 PM|LINK
The data is strings, and if I should hardcode I would write:
const string sql = "SELECT * FROM database WHERE guid IN('44-33-22', '55-33-44');
The problem is that sometimes I have 2 guids like here, but sometimes I have 100.
I get the guids in a string which could look like:
string guid= "44-33-22,55-33-44, 33-33-44" (here in this ex is 3 guids)
best Pernille
tdmca
Contributor
2396 Points
661 Posts
Re: How to search with variable parameters?
Apr 02, 2012 12:52 PM|LINK
ok you are going right when you are using IN operator
IN operator works like this
select * from table where GUID in ('123','23423','34535')
so you have to convert your GUIDs 44-33-22,55-33-44, 33-33-44 to '44-33-22','55-33-44', '33-33-44'
before passing it to IN in select
so you have to create you own function to do this conversion
in this function first split you GUID string to arrays then create a loop to add single quotes to each item and then reconcatenate to make a single string.
try it if you are unable to do this, tell me i will give you the function
fernlle
None
0 Points
5 Posts
Re: How to search with variable parameters?
Apr 02, 2012 01:04 PM|LINK
In your example I want to replase '123','23423','34535' whit a call to a sting, so I have
select * from table where GUID in (@mystring)
mystring=" '123','23423','34535' ";
var cmd3 = new SqlCommand(sql3, conn);
cmd3.Parameters.AddWithValue(
"guid", mystring);
The problem in this is that this will give the result as if im searching for the hole string instead of 3 values. And the reason I want to do it like this, Is that my numers of values in the instatement are changing
tdmca
Contributor
2396 Points
661 Posts
Re: How to search with variable parameters?
Apr 02, 2012 04:37 PM|LINK
dear i know this already that's whay i have given you above suggestion please follow it .
IN operator is used where multiple values have to passed for condition like your GUIDs
but you have to break single string to different GUIDs and then re-concatenate in a form so that IN operator can accept them as seperate elements.
create the function what i have told you already.
fernlle
None
0 Points
5 Posts
Re: How to search with variable parameters?
Apr 02, 2012 04:52 PM|LINK
Tanks...I found that I could do it like this:
string sql3part1 = "SELECT * FROM Scorecard WHERE Course_GUID IN (";
string sql3part2 = ") AND DateOfPlay BETWEEN @start AND @end";
string sql3 = sql3part1 + GUID + sql3part2;
conn.Open();
var cmd3 = new SqlCommand(sql3, conn);
Wher GUID is my string (GUID="'xxxx','xxxxx','xxxxx'";).
tdmca
Contributor
2396 Points
661 Posts
Re: How to search with variable parameters?
Apr 02, 2012 04:55 PM|LINK
fernlle if your problem solved?
dont forget to mark answer