A quick question: How to use variables in the SQL insertion string

[Guest]

Hello,

I am new to ADO.NET and SQL. What I am trying to do is very basic - to
insert some variable values into database. The following is the C# code:

private static void InsertIntoSqlDb(OleDbDataReader reader)
{

string insStr = "";
string list_no = reader.GetString(0);
string item_no = reader.GetString(1);
string description = reader.GetString(2);

string priceStr = reader.GetString(3);

insStr = "INSERT INTO dliTestDb (list, item, description, price) VALUES
(list_no, item_no, description, priceStr)";


SqlCommand cmd = new SqlCommand(insStr, conn);

//Insert
//
try
{
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine(ex);
}

}


Upon execution of the above, I got exception:



System.Data.SqlClient.SqlException: The name 'list_no' is not permitted in
this
context. Only constants, expressions, or variables allowed here. Column
names ar
e not permitted.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Doesn't any one know how to use variable names exactly in the SQL insertion
string?

Thanks.

 

[William \(Bill\) Vaughn]

You mean besides building a Command object and a Parameters collection?
We've talked about this a million times...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Hi Bill,

I am not sure I understand. As I said, I am new to the whole windows
programming. Things that are so obvious to you might not be so for me. So if
you are a "mentor", then pls answer the question in plain English or give a
reference. Otherwise. I don't see the usefulness.
Regards,

David

 

[William \(Bill\) Vaughn]

Sorry, I was looking at another message and typed the response.
Your problem is that you are trying to build an INSERT statement without
properly concatenating the variables into the string. Frankly, this is a
dangerous practice as it leads to SQL injection attacks. As I describe in my
article on handling parameters (see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp)
we suggest you create a Parameters collection and Add a Parameter for each
column in the INSERT. While this is more work, it deals with a litany of
problems. Yes, this article talks about stored procedures, but the Command
object construction is about the same.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Mark Rae]

we suggest you create a Parameters collection and Add a Parameter for each
column in the INSERT. While this is more work, it deals with a litany of
problems.

It certainly is a lot more work, but the benefits by far outweigh the
additional effort.

I actually ended up writing a small WinForms utility to generate this code
automatically, so this process now takes me around 10 seconds per SQL Server
table...