SQL insert error on C#.Net

Question

I want to insert a string list of device name and related seq number into a table on SQL server 2014. The IDE is Visual Studio 2015, the programming language is C#.

When I run the program and click the button14, the error is:

The parameterized query '(@ID int,@NAME nvarchar(4000),@RSSI int)INSERT BeaconInfo (ID, N' expects the parameter '@NAME', which was not supplied.

I am not sure my codes of inserting sql is correct or not.

public partial class Form1 : Form
{
    int seqnumber = 333;
    List<string> items;
    string tmp_name;
    BluetoothDeviceInfo[] devices;

    public Form1()
    {
       items = new List<string>();
       InitializeComponent();
    }

private void startScan()
{
    listBox1.DataSource = null;
    listBox1.Items.Clear();
    items.Clear();
    Thread bluetoothScanThread = new Thread(new ThreadStart(scan));
    bluetoothScanThread.Start();
}

private void scan()
{
    updateUI("Starting Scan..");
    BluetoothClient client = new BluetoothClient();
    devices = client.DiscoverDevicesInRange();
    updateUI("Scan complete");
    updateUI(devices.Length.ToString() + " devices discovered");

    foreach (BluetoothDeviceInfo d in devices)
    {
      items.Add(d.DeviceName);
    }
    updateDeviceList();
}


private void button14_Click(object sender, EventArgs e)
{
    System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection("....");
    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "INSERT BeaconInfo (ID, Name, RSSI) VALUES (@ID, @NAME, @RSSI)";
    cmd.Parameters.AddWithValue("@ID", seqnumber);
    cmd.Parameters.AddWithValue("@NAME", tmp_name);
    cmd.Parameters.AddWithValue("@RSSI", 55);
    cmd.Connection = sqlConnection1;

    for (int j = 0; j < items.Count; j++)
    {
      seqnumber = seqnumber + 1;
      tmp_name = items[j];
      sqlConnection1.Open();
      cmd.ExecuteNonQuery();
      sqlConnection1.Close();
    }
}

`tmp_name` is null. [Look at this question](http://stackoverflow.com/questions/13451085/exception-when-addwithvalue-parameter-is-null) — Jonesopolis, May 24 '16 at 18:13
Note that changing the value of `tmp_name` in the loop doesn't change the value of the `@NAME` parameter. — Jon Skeet, May 24 '16 at 18:18

madoxdev · Accepted Answer · 2016-05-24T19:41:54.650

1

Please try to change your code like this:

private void button14_Click(object sender, EventArgs e)
{
    using (System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection("....")) 
    {

       sqlConnection1.Open();

       for (int j = 0; j < items.Count; j++)
       {
           seqnumber = seqnumber + 1;
           tmp_name = items[j];



           using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) 
           {
              cmd.CommandType = System.Data.CommandType.Text;
              cmd.CommandText = "INSERT BeaconInfo (ID, Name, RSSI) VALUES (@ID, @NAME, @RSSI)";
              cmd.Parameters.AddWithValue("@ID", seqnumber);
              cmd.Parameters.AddWithValue("@NAME", tmp_name);
              cmd.Parameters.AddWithValue("@RSSI", 55);
              cmd.Connection = sqlConnection1;


              try 
              {
                   cmd.ExecuteNonQuery();
              } 
              catch (Exception ex) 
              {
                   //Here handle if error
              }

           }
        }
    }
}

Generaly is not good practice to write sql statements in handlers, so at Form qctually. It would be better to create class doing this operation, and call it inside handler.

Please note as comments under your question suggested, tmp_name is not set and parameters at SqlClient are not references so changing value after association to variable tmp_name has not any impact on value of parameters.

edited May 24 '16 at 19:41

answered May 24 '16 at 18:34

madoxdev

3,770
1
24
39

1

`sqlConnection1.Close();` if you wrap the `sqlConnect1` object around a using as well you will not need the `.Close()` method call. also I would not assume that the Insert would always work and or execute so I would wrap the `cmd.ExecuteNonQuery()` call around a try catch as well.. – MethodMan May 24 '16 at 18:36
actually you can have multiple nested using(){} perhaps you are not familiar with how to do it.. I use multiple using's all the time.. so I am not wrong – MethodMan May 24 '16 at 18:41
I misread your comment previously :) So I deleted my comment which was not right. Anyway, I would listen to suggestions and add it to answer. – madoxdev May 24 '16 at 18:42
1

no problem also if the OP has the `using System.Data.SqlClient` in the header section of the Class there would also be no reason to fully qualify the name space in the body of your using statement as well :) – MethodMan May 24 '16 at 18:44
yeah, but I think it is easier to know what is what when this code at least looks like question. – madoxdev May 24 '16 at 18:47
I still got the {"The connection was not closed. The connection's current state is open."} from sqlConnection1.Open();. I am not sure it need a sentence to close the sql connection. – QuickLearner May 24 '16 at 19:18
check now. Connection.Open should be of course outside of the loop. My mistake, sorry. – madoxdev May 24 '16 at 19:42
Great, it works well now. In addition, do I need to add a line to close to sql connection or just keep the sql connection? – QuickLearner May 24 '16 at 21:24
No, necaise connection is now in using statement, it means after it gets to end of it, Dispose method of IDisposable interface is called and in this case Dispose also closing connection. – madoxdev May 25 '16 at 03:54

SQL insert error on C#.Net

1 Answers1