How can I select a row from a datatable using two variable values?

Question

Alright, so what I'm attempting to do here, is select from a datatable all the rows that match two variables. First, I queried a SPList into a datatable. The datatable has two columns, Client ID, and Model Details. Here's some of the code, so hopefully you can get an idea of what I'm attempting.

//These are contained within a foreach loop, so will be different through each
//iteration through.
int iClientID = gdbData.GetClientID(iJobNumberID);
string strModelDetails = xeApprovalUpdate.GetAttribute("ModelDetails");
string strClientID = iClientID.ToString()
//Here my datatable is populated from the sharepoint list I query
DataTable dtabModelDetails = queryModelDetails(splModelDetails);
DataRow[] drModelDetails = dtabModelDetails.Select(strClientID, strModelDetails);

Seeing as that didn't work, I attempted to do the following select statement:

DataTable dtabModelDetails = queryModelDetails(splModelDetails);
string strDataExpression = "ClientID = " + strClientID + " AND Title = " + strModelDetails;
DataRow[] drModelDetails = dtabModelDetails.Select(strDataExpression);

Now I'm getting a syntax error for the select statement, but I'm pretty sure my syntax is correct? Would I get this error if my select statement returned no rows?

score 4 · Answer 1 · answered Jun 15 '11 at 19:53

4

Yes, you can add parameters to the SqlCommand object. Add one param for each param in your select statement.

Also, if you have spaces in your column names, you need to put them in brackets.

    DataRow[] drModelDetails = dtabModelDetails.Select("[Client ID] = @ClientID AND [Model Details] =@ModelDetails")

    cmd.Parameters.Add(new SqlParameter("@ClientID", ClientID));
    cmd.Parameters.Add(new SqlParameter("@ModelDetails", ModelDetails));

answered Jun 15 '11 at 19:53

Chris Kooken

32,730
15
85
123

2

What is cmd here? And why are you adding SqlParameters to the Datatable Select expression? – MNIK Jun 15 '11 at 20:08
Yeah, I'm a bit confused on that as well. – OneFreeFitz Jun 15 '11 at 20:24
In my opinion, your best bet would be to add an expression to the Select statement as shown in here or as in my example (correct me if I'm wrong) http://msdn.microsoft.com/en-us/library/det4aw50.aspx – MNIK Jun 15 '11 at 20:34

score 1 · Accepted Answer · answered Jun 15 '11 at 19:58

1

Yes, you can add where clause to the DataTable Select method like this....

DataRow[] drModelDetails = dtabModelDetails.Select("Client ID = '" + clientId + "' AND Model Details = '" + modelDetails + "'");

answered Jun 15 '11 at 19:58

MNIK

1,581
3
16
22

This isn't parameterized like he is asking and should be avoided at all costs. It is vulnerable to SQL injection attacks and does not encode variables that have special sql characters in them, like ' so if your variable value has an apostrophe in it, you will get an exception. – Chris Kooken Jun 15 '11 at 20:01
Yes, I agree - but I wouldn't add a 'SqlParameter' to a Datatable Select expression either. You would also get a compilation error complaining about 'cmd' variable. – MNIK Jun 15 '11 at 20:17

How can I select a row from a datatable using two variable values?

2 Answers2

Linked