How to supply @param "String" and use where id IN I@param using Data Adapter and DataSet?

Question

3.00/5 (2 votes)

See more:

I've got a set of unique IDs that belong to Users table, eg. 1478, 8906, 4677, etc. Now how do I get a DataSet back with full record set of these users using DataAdapter?

I tried following which returns null, unfortunately. (DataSet's query builder)

SQL

SELECT  Col1, Col2, id
FROM   Table1
WHERE        
(CAST(id AS NVarchar(20)) IN (@param))

Code Behind:

C#

Engineers.SM_UsersDataTable _dtUsers = new Engineers.SM_UsersDataTable();
EngineersTableAdapters.SM_UsersTableAdapter taUsers = new EngineersTableAdapters.SM_UsersTableAdapter();
String param = "123,124,125";
_dtUsers = taUsers.GetDataByStringedApprovals(param);

Am I doing something wrong here?

EDIT:

I have a set of unique IDs that I put into string, I need to use Table Adapter to query the full record set of these IDs.

I hope I make some sense.

Posted 24-Apr-12 2:24am

cUbeindaclUb

Updated 24-Apr-12 2:50am

v3

Add a Solution

Comments

Rahul Rajat Singh 24-Apr-12 8:32am

Not clear. please elaborate.

cUbeindaclUb 24-Apr-12 9:11am

1) GetDataByStringedApprovals(@param) is a query saved in DataSet.
2) The SQL Statement is above but if more than one id is passed, it returns NULL
3) I need to pass these IDs and compare each record against IDs in passed @param string

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Subinal · Answer 1 · 2012-04-24T03:22:00

Solution 2

Have you tried?

SQL

SELECT  Col1, Col2, id
FROM   Table1
WHERE
id IN (@param))

Posted 24-Apr-12 3:22am

Subinal

Comments

cUbeindaclUb 24-Apr-12 9:31am

This solution throws following exception: "Input string was not in correct format."
Please note that id is int and @param is string

Subinal 24-Apr-12 9:59am

Check this out.
http://stackoverflow.com/questions/4215426/passing-comma-seperated-value-from-net-to-stored-procedure-and-using-the-value

Member 7851700 · Answer 2 · 2012-04-30T08:53:00

The solution I found is quite simple, this works like a charm and there's no need for sps or other functions;

SQL:

SQL

SELECT whatever 
    FROM whatever
    WHERE (PATINDEX('%''' + CAST(id AS Varchar(20)) + '''%', @param) > 0)

C#:

C#

String param = "'''1234'',''4567'''";
dataTable1 = tableAdapter1.getYourValues(param);