ASP.Net \ C# SQL Parameters DBNull

Question

Am struggling to use SQL parameters for C# \ ASP.Net, not used C# with ASP.Net much so quite new to it, I have a web page with a dropdown box (drpdSerType) and a text box (txtRNumSer) and a button, the button code is below...

If I don't use the parameters as seen the commented code then it works fine...

            try
            {
            SqlConnection con = new SqlConnection();
            SqlDataAdapter sda = new SqlDataAdapter();
            DataTable dt = new DataTable();
            con.ConnectionString = "Server=server1;Database=db1;Integrated Security=True";
            sda.SelectCommand.Parameters.AddWithValue("@I", drpdSerType.SelectedValue.ToString());
            sda.SelectCommand.Parameters.AddWithValue("@S", txtRNumSer.Text);
            sda = new SqlDataAdapter("SELECT Number, Type, Qty, Date, num, ReceiptID FROM IssuedRN WHERE @I = @S", con);
            //This line works but no secure
            //sda = new SqlDataAdapter("SELECT Number, Type, Qty, Date, num, ReceiptID FROM IssuedRN WHERE " + drpdSerType.SelectedValue.ToString() + " = '" + txtRNumSer.Text + "'", con);
            sda.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();

I get the following error; System.NullReferenceException: 'Object reference not set to an instance of an object.' Related to line

sda.SelectCommand.Parameters.AddWithValue("@I", drpdSerType.SelectedValue.ToString());

I have also followed the guidance here; https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

Thanks for any help on this, I have tried many different ways to use parameters but all seem to do this or give the above error.

Thanks Karl

Update:

I have added in a switch statement driven by the drop down list, so the parameter @I is now hard coded...

string qryselect = drpdSerType.SelectedValue.ToString();
        string cmd = "";

        switch (qryselect)
        {
            case "Number":
                cmd = "SELECT Number, Type, Qty, Date, RNum, ReceiptID FROM IssuedRN WHERE Number = @S";
                break;
            case "RNum":
                cmd = "SELECT Number, Type, Qty, Date, RNum, ReceiptID FROM IssuedRN WHERE RNum = @S";
                break;
            case "ReceiptID":
                cmd = "SELECT Number, Type, Qty, Date, RNum, ReceiptID FROM IssuedRN WHERE ReceiptID = @S";
                break;
        }

Am guessing am missing something in the passing of the values in the drop down to the SQL parameter as the values are pre-populated and match the columns and querying, they are never null.

Do the answers to [Assign null to a SqlParameter](https://stackoverflow.com/questions/4555935/assign-null-to-a-sqlparameter/20806356) help? Also, just so you know: [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/), [AddWithValue is evil!](https://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html), and [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). — Andrew Morton, Oct 03 '21 at 14:50

Serge · Answer 1 · 2021-10-03T17:33:38.723

0

you have a bug

"SELECT Number, Type, Qty, Date, num, ReceiptID FROM IssuedRN WHERE @I = @S"

WHERE @I = @S" what is this?

and fix the rest

con.ConnectionString = "Server=server1;Database=db1;Integrated Security=True";

var sqlString="....fixed sql";
var cmd = new SqlCommand(sqlSting, con);

var value= drpdSerType.SelectedValue;
if(value == null)
        cmd.Parameters.AddWithValue("@I", DBNull.Value);
  else
        cmd.Parameters.AddWithValue("@I", value.ToString());

cmd.Parameters.AddWithValue("@S", txtRNumSer.Text);

 sda = new SqlDataAdapter();
sda.SelectCommand=cmd;
 sda.Fill(dt);
 .....

edited Oct 03 '21 at 17:33

answered Oct 03 '21 at 15:05

Serge

40,935
4
18
45

Hi, Thanks for the help... I am still getting the same message it makes it to the else "sda.SelectCommand.Parameters.AddWithValue("@I", value.ToString());" but still get "System.NullReferenceException: 'Object reference not set to an instance of an object.'". – karl_009 Oct 03 '21 at 15:41
@karl_009 You could check for `drpdSerType.SelectedIndex == -1` before trying to access its SelectedValue. Or is there a possibility that drpdSerType is null? – Andrew Morton Oct 03 '21 at 17:22
Hello.. The @I or drpdSerType is a dropdownlist on a ASP page, the @S which is a textbox so the WHERE @I = @S should be WHERE Number = 12345... where Number is there are other options which is why a dropdownlist is used, when I hard code the Number into the SQL its okay, but now when using dropdown with a parameter... – karl_009 Oct 03 '21 at 18:08
@AndrewMorton I checked that by outputting the value to a label on the page and the correct value is output... – karl_009 Oct 03 '21 at 18:21

score 0 · Answer 2 · answered Oct 03 '21 at 19:48

I would always strong datatype the parms

eg:

sda.SelectCommand.Parameters.Add("@I",SqlDbType.NVarChar).Value = drpdSerType.SelectedValue.ToString());

or if it a nummber, then use:

sda.SelectCommand.Parameters.Add("@I",SqlDbType.int).Value = drpdSerType.SelectedValue.ToString());

The main issue you have to deal with? Can the combo box be blank? In other words, when you load up teh grid, and set the actual value of the combo, you often have to translate null into a blank (empty string). And when sending back to database, you have to translate back from "" to null.

Worse yet? If the combo is data driven, then do you add a extra blank row to the combo to handle the above. Since if the list/data for the combo does not have a blank row choice then you can NOT shove into the combo a blank choice if it don't have one!! (but the database row driving the grid may well certainly have a null for that choice - so you have to ensure the combo drop allows this.

ASP.Net \ C# SQL Parameters DBNull

2 Answers2