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

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
Mon Jun 27 18:28:58 CDT 2005

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.
__________________________________

"wdli" <wdli@discussions.microsoft.com> wrote in message
news:B58B2EA9-611E-49DB-B436-F3DDC79DAA89@microsoft.com...
>
>
> 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.
>

wdli
Mon Jun 27 18:38:02 CDT 2005

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" wrote:

> 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.
> __________________________________
>
> "wdli" <wdli@discussions.microsoft.com> wrote in message
> news:B58B2EA9-611E-49DB-B436-F3DDC79DAA89@microsoft.com...
> >
> >
> > 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
Mon Jun 27 19:18:24 CDT 2005

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.
__________________________________

"wdli" <wdli@discussions.microsoft.com> wrote in message
news:19BF3F1C-1322-4B13-BBAD-6B65629E6D8C@microsoft.com...
> 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" wrote:
>
>> 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.
>> __________________________________
>>
>> "wdli" <wdli@discussions.microsoft.com> wrote in message
>> news:B58B2EA9-611E-49DB-B436-F3DDC79DAA89@microsoft.com...
>> >
>> >
>> > 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.
>> >
>>
>>
>>

Mark
Tue Jun 28 04:59:41 CDT 2005

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:e8Avjb3eFHA.3028@TK2MSFTNGP09.phx.gbl...

> 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...