| Author |
Topic |
|
DaveBF
Yak Posting Veteran
89 Posts |
 Posted - 2010-08-17 : 10:14:17
|
| Hi,I'm trying to create a statement using a parameterized query that looks like this:Select * from Mytable where user_ID in ('30','40')In my VB code, I have something like this. e.command.Parameters("@user_ID").value = "'30','40'"But the query that gets produced by visual studio ends with this:@user_ID=N'''30'',''40'''.This doesn't give the results I'm looking for (nothing's returned from the Select). I'm not even sure how many single quotes I'm hoping to see in the above line.I made my double quotes red above. The rest are all single quotes.Thanks for any help.Dave |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 10:23:28
|
| is user_ID a varchar field? if not just pass value as csv list 30,40 etc and use it in IN------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
DaveBF
Yak Posting Veteran
89 Posts |
Posted - 2010-08-17 : 10:41:55
|
| I'm getting closer.Currently I have my user_ID parameter defined as String. This is so I can pass in a string like "30,40". The SQL field is currently a Int but I can change it to char if that makes this easier.I'm now getting @User_ID=N'30,40' generated by Visual studio but SQL gives an error "converting the nvarchar value '30,40' to data type int." |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 10:58:33
|
| whats the currently used query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
DaveBF
Yak Posting Veteran
89 Posts |
Posted - 2010-08-17 : 10:58:47
|
| So, the question seems to come down to how I can convert a parameterized string like N'43,30'to a comma separated list of integers that will work in my "WHERE IN" clause? |
|
|
|
DaveBF
Yak Posting Veteran
89 Posts |
Posted - 2010-08-17 : 11:03:43
|
| Here's my query:ConnectionString="<%$ ConnectionStrings:HumanResourcesConnectionString %>" SelectCommand="Select r.*, s.*, c.*, a.*, n.*, t.*, d.*,v.* from Requests r left join Approvals a on r.Req_Approval_ID = a.Approval_ID left join LU_CostCenters s on r.req_LU_CostCenters_ID = s.LU_CostCenters_ID left join LU_Servicenames n on n.LU_Servicenames_ID = s.Cos_LU_Servicenames_ID left join Sections c on r.Req_Sections_ID = c.Section_ID left join LU_PositionsAndCodes t on r.Req_LU_PositionsAndCodes_ID=t.LU_PositionsAndCodes_ID left join LU_DutyStations d on r.req_LU_DutyStations_ID=d.LU_DutyStations_ID left join Vices v on v.Vice_ID=r.Req_Vice_ID where r.RequestStatus='Approved' and n.LU_ServiceNames_ID in (@UserService_ID)"> <SelectParameters> <asp:Parameter Name="UserService_ID" Type="String" /> </SelectParameters>And here's how I'm trying to set my parameter: e.Command.Parameters("@UserService_ID").Value = "43,30"Notice how my parameter is type string, but I'm trying to use that string as a CSV. How do I do that? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 11:06:38
|
| change and n.LU_ServiceNames_ID in (@UserService_ID)to and ',' + @UserService_ID + ',' LIKE '%,' + CAST(n.LU_ServiceNames_ID AS varchar(100)) + ',%'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
DaveBF
Yak Posting Veteran
89 Posts |
Posted - 2010-08-17 : 11:13:16
|
| That worked. I would never have figured that out.Thank you very much! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 11:13:44
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tpa32
Starting Member
3 Posts |
Posted - 2010-10-12 : 11:10:15
|
| Thank you very much.This example solved my lots of time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-17 : 02:52:23
|
| glad to hear that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Single quote problem