Question 88356 in ASP.NET Question posted in
The official documentation can be found here.

ASP.Net C# SQL Parameters DBNull

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.

Tags:

2

Answers


  1. 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);
 .....
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search