QuickLearner QuickLearner - 6 months ago 14
SQL Question

SQL Insert parameter in C# / .NET

I need to insert a string list into a table on SQL server 2014. The IDE is Visual Studio 2015, the programming language is C#.

When I run the program, the error is:


Incorrect syntax near 'j'.


In addition, I am confused about the type of second parameter in
VALUES (ID, Name, RSSI)
. When I set
Name
as "Jason" or "Alice", it works well. However, when I use
items[0]
or
items[1]
, it fails.

//items refer to the scanned Bluetooth devices
List<string> items;

//use Bluetooth scan devices and record them in items;
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();
}

// ........................

// use sql to insert the items into a table on SQL server

for (int j = 0; j < items.Count; j++)
{
cmd.CommandText = "INSERT BeaconInfo (ID, Name, RSSI) VALUES (171, items[j], 276)";
//ID is int type, Name is varchar type, RSSI is int type
cmd.Connection = sqlConnection1;

sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}

Answer

items[j] is not a thing that the SQL Server would have any clue about how to use. Everything you are passing in that string is just sent to the SQL Server to be processed there. You need to pass a SQL Parameter to your statement:

for (int j = 0; j < items.Count; j++)
{
     cmd.CommandText = "INSERT BeaconInfo (ID, Name, RSSI) VALUES (171, @item, 276)";
     //ID is int type, Name is varchar type, RSSI is int type
     cmd.Connection = sqlConnection1;

     sqlConnection1.Open();
     cmd.Parameters.Add(New SQLParameter("@item", items[j]));
     cmd.ExecuteNonQuery();
     sqlConnection1.Close();
}