Pass Null Values In A SQL Command (For Two Columns) - Adding In Foreach Loop

Question

I want to select rows from my SQL Server table (it has 3 columns) and to display them into my Datagridview. My problem about null values. How can I pass null values? (Sometimes the first column and the second column are empty. ).

Below is my code:

    string cm = "SELECT column1, column2, column3 FROM mytablename WHERE column1='" 
   + sIS[mSI] + "' AND column2='" + sR[mSSI] + "' OR column1='" + sR[mSSI] 
   + "' AND column2='" + sIS[mSI] + "'";

Note: I edited my code.

Cam Bruce · Answer 1 · 2019-01-17T19:08:29.457

2

You should be using parameters to pass values into a SQL statement or else you will be vulnerable to SQL injection attacks. You can create a SqlCommand object, create SqlParameter objects, then set values on them. If any of your values are null, then you can pass DBNull.Value as the parameter value.

SqlCommand cmd = con.CreateCommand();
string cm = "SELECT column1, column2, column3 "
cm += "FROM mytablename "
cm += "WHERE column1=@mSI "
cm += "AND column2=@mSSI OR column1=@mYSI AND column2=@mSI";
cmd.CommandText = cm;

for(int mSSI=0; mSSI<sR.Count(); mSSI++)
{
   cmd.Parameters.AddWithValue("@mSI ", sR[mSI]);

   // check for a null value
   if (sr[mSSI] == null)
   {
     cmd.Parameters.AddWithValue("@mSSI", DBNull.Value);
   }
   else
   { 
     cmd.Parameters.AddWithValue("@mSSI", sR[mSSI]);
   }

   cmd.Parameters.AddWithValue("@mYSI", sR[mYSI]);

   SqlDataAdapter sd = new SqlDataAdapter(cmd);
   DataTable dat = new DataTable();
   sd.Fill(dat);
   // clear parameters after each iteration
   cmd.Parameters.Clear()
}

edited Jan 17 '19 at 19:08

answered Jan 17 '19 at 17:57

Cam Bruce

5,632
19
34

Can I add my values into foreach loop again? – heymycoder Jan 17 '19 at 18:06
yes. updated my answer. Since you're creating a new `SqlCommand` object during every iteration, you just re-add them. This of course can be optimized, but the code above will get the job done. – Cam Bruce Jan 17 '19 at 18:11
Should I create SQL commands between of two for loops? It's hard to understand, sorry. – heymycoder Jan 17 '19 at 18:21
It doesn't work. "SqlDataAdapter sd = new SqlDataAdapter(cmd, con);" it gives error. (Error Value of type 'System.Data.SqlClient.SqlConnection' cannot be converted to 'String') – heymycoder Jan 17 '19 at 18:34
fixed. was using wrong constructor. you only need to create the `SqlCommand` once - just clear the parameters after each iteration as I am doing in the sample – Cam Bruce Jan 17 '19 at 18:47
Why we use only one for loop? I need two. – heymycoder Jan 17 '19 at 18:48
i only included the relevant part of the code based on your question. your code will produce a `DataTable` for every iteration of the `for` loop. Is that what you intended? – Cam Bruce Jan 17 '19 at 19:01
Yes. But it doesn't work again. Error: "System.InvalidOperationException: ExecuteReader: CommandText property has been not initialized" – heymycoder Jan 17 '19 at 19:03
For string cm, why did we just write it once and never use it? – heymycoder Jan 17 '19 at 19:07
it needs to be set to the `SqlCommand.CommandText` property as shown in the answer – Cam Bruce Jan 17 '19 at 19:09
I'm trying again sir. – heymycoder Jan 17 '19 at 19:21
System.Data.SqlClient.SqlException: 'The variable name '@mSSI' has already been declared. Variable names must be unique within a query batch or stored procedure.' – heymycoder Jan 17 '19 at 19:28
do you have the `cmd.Parameters.Clear()` at the end of the loop? – Cam Bruce Jan 17 '19 at 19:38
Yes sir, as your answer. – heymycoder Jan 17 '19 at 19:41
You have two loops. You will need to modify the code so the parameters are only being created once per `da.Fill`. That is why you're getting the error – Cam Bruce Jan 17 '19 at 20:01

score -1 · Answer 2 · answered Jan 17 '19 at 19:02

-1

I think the answer to your question is using "DBNull.Value" as mentioned above, something like this;

            string cm = "SELECT column1, column2, column3 FROM mytablename WHERE column1='"
           + DBNull.Value + "' AND column2='" + DBNull.Value + "' OR column1='" + sR[mSSI]
           + "' AND column2='" + DBNull.Value + "'";

answered Jan 17 '19 at 19:02

Orhun Karapinar

45
6

You used only sR[mSSI], I will not select only for it. I need to select also sIS[mSI]. My command row is; string cm = "SELECT column1, column2, column3 FROM mytablename WHERE column1='" + sIS[mSI] + "' AND column2='" + sR[mSSI] + "' OR column1='" + sR[mSSI] + "' AND column2='" + sIS[mSI] + "'"; – heymycoder Jan 17 '19 at 19:34
I think there is a misunderstanding here; I gave that string as an example to show how you can pass "null" values in an SQL command. I was not checking or trying to fix your query. Anyways, a regular select query should bring empty values. I don't think you need a complex "WHERE" condition. – Orhun Karapinar Jan 17 '19 at 19:41
Sometimes the first column and the second column are empty. How can fix that null values in my "string cm = "SELECT column1, column2, column3 FROM mytablename WHERE column1='" + sIS[mSI] + "' AND column2='" + sR[mSSI] + "' OR column1='" + sR[mSSI] + "' AND column2='" + sIS[mSI] + "'";" row. I think I can't understand to your code, but thank you for answer. By the way, the answers must be complete. Because, I already don't know the correct answer and If you post missing code, I don't understand it sir. – heymycoder Jan 17 '19 at 19:50

Pass Null Values In A SQL Command (For Two Columns) - Adding In Foreach Loop

2 Answers2