Example for basic parameterized INSERT in asp.net 2.0?

[Steve Franks]

I'm new to ASP.NET and working with the 2.0 beta. As a classic ASP
developer I have a lot of code that looks like this:

sql = "insert into x values("
sql = sql & val1 & ", " & val2 & ", ' " & strVal3 & " ', " & val4 & ")"

Which of course gets very messy. As I understand it there is a way to use a
placeholder string with question marks like "values(?, ?, ?, ?)" and then
set the value of each ? placeholder with variable names. This would relieve
the messiness caused by worrying about missing quotes and readability
issues, etc.

Can someone provide a basic code snippet that demonstrates how to do this?
I know there are all sorts of toolbar things that can be dragged and dropped
to create some of this visually. But my need is so basic I just want to
have the minimal code to create a db connection, execute an insert, and
perhaps retrieve the @@identity value associated with the inserted row.

Thanks in advance!

Steve

[Grant Merwitz]

You should really be using a parametised stored procedure
Something like:

SQL
CREATE PROCEDURE exampleInsert
@val1 varchar(200),
@val2 varchar(200)
AS
INSERT INTO x VALUE(@val1, @val2)

c#

SqlCommand cmd = new SqlCommand("exa mpleInsert", MyConnection);
//MyConnection must already be declared as SqlConnection
cmd.Parameters. Add(new SqlPArameters(" @val1", SqlDbType.VarCh ar,
200)).Value = val1; //val 1 would be your value
cmd.Parameters. Add(new SqlPArameters(" @val2", SqlDbType.VarCh ar,
200)).Value = val2;
MyConnection.Op en()
cmd.ExecuteNonQ uery();
MyConnection.Cl ose();

HTH
"Steve Franks" <pl****@postrep lyhere.com> wrote in message
news:cM******** ************@co mcast.com...

I'm new to ASP.NET and working with the 2.0 beta. As a classic ASP
developer I have a lot of code that looks like this:

sql = "insert into x values("
sql = sql & val1 & ", " & val2 & ", ' " & strVal3 & " ', " & val4 & ")"

Which of course gets very messy. As I understand it there is a way to use
a placeholder string with question marks like "values(?, ?, ?, ?)" and
then set the value of each ? placeholder with variable names. This would
relieve the messiness caused by worrying about missing quotes and
readability issues, etc.

Can someone provide a basic code snippet that demonstrates how to do this?
I know there are all sorts of toolbar things that can be dragged and
dropped to create some of this visually. But my need is so basic I just
want to have the minimal code to create a db connection, execute an
insert, and perhaps retrieve the @@identity value associated with the
inserted row.

Thanks in advance!

Steve

[Steve Franks]

Thank you so much!. Unfortunately I cannot use stored procedures right now
but hope to down the road.

In the meantime how would I update the code snippet you provided to work
through all ASP.NET without a stored procedure?

Thank you again,

Steve

"Grant Merwitz" <gr***@workshar e.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

You should really be using a parametised stored procedure
Something like:

SQL
CREATE PROCEDURE exampleInsert
@val1 varchar(200),
@val2 varchar(200)
AS
INSERT INTO x VALUE(@val1, @val2)

c#

SqlCommand cmd = new SqlCommand("exa mpleInsert", MyConnection);
//MyConnection must already be declared as SqlConnection
cmd.Parameters. Add(new SqlPArameters(" @val1", SqlDbType.VarCh ar,
200)).Value = val1; //val 1 would be your value
cmd.Parameters. Add(new SqlPArameters(" @val2", SqlDbType.VarCh ar,
200)).Value = val2;
MyConnection.Op en()
cmd.ExecuteNonQ uery();
MyConnection.Cl ose();

HTH
"Steve Franks" <pl****@postrep lyhere.com> wrote in message
news:cM******** ************@co mcast.com...

I'm new to ASP.NET and working with the 2.0 beta. As a classic ASP
developer I have a lot of code that looks like this:

sql = "insert into x values("
sql = sql & val1 & ", " & val2 & ", ' " & strVal3 & " ', " & val4 & ")"

Which of course gets very messy. As I understand it there is a way to
use a placeholder string with question marks like "values(?, ?, ?, ?)"
and then set the value of each ? placeholder with variable names. This
would relieve the messiness caused by worrying about missing quotes and
readability issues, etc.

Can someone provide a basic code snippet that demonstrates how to do
this? I know there are all sorts of toolbar things that can be dragged
and dropped to create some of this visually. But my need is so basic I
just want to have the minimal code to create a db connection, execute an
insert, and perhaps retrieve the @@identity value associated with the
inserted row.

Thanks in advance!

Steve

[Grant Merwitz]

been a while since i done that.
Something like:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO x VALUE(@val1, @val2)";
cmd.Connection = MyConnection; //conn already set up
cmd.Parameters. Add(new SqlParameter("@ val1", SqlDbType.VarCh ar, 200)).Value
= val1;
cmd.Parameters. Add(new SqlParameter("@ val2", SqlDbType.VarCh ar, 200)).Value
= val2;

"Steve Franks" <pl****@postrep lyhere.com> wrote in message
news:h9******** ************@co mcast.com...

Thank you so much!. Unfortunately I cannot use stored procedures right
now but hope to down the road.

In the meantime how would I update the code snippet you provided to work
through all ASP.NET without a stored procedure?

Thank you again,

Steve

"Grant Merwitz" <gr***@workshar e.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

You should really be using a parametised stored procedure
Something like:

SQL
CREATE PROCEDURE exampleInsert
@val1 varchar(200),
@val2 varchar(200)
AS
INSERT INTO x VALUE(@val1, @val2)

c#

SqlCommand cmd = new SqlCommand("exa mpleInsert", MyConnection);
//MyConnection must already be declared as SqlConnection
cmd.Parameters. Add(new SqlPArameters(" @val1", SqlDbType.VarCh ar,
200)).Value = val1; //val 1 would be your value
cmd.Parameters. Add(new SqlPArameters(" @val2", SqlDbType.VarCh ar,
200)).Value = val2;
MyConnection.Op en()
cmd.ExecuteNonQ uery();
MyConnection.Cl ose();

HTH
"Steve Franks" <pl****@postrep lyhere.com> wrote in message
news:cM******** ************@co mcast.com...

I'm new to ASP.NET and working with the 2.0 beta. As a classic ASP
developer I have a lot of code that looks like this:

sql = "insert into x values("
sql = sql & val1 & ", " & val2 & ", ' " & strVal3 & " ', " & val4 & ")"

Which of course gets very messy. As I understand it there is a way to
use a placeholder string with question marks like "values(?, ?, ?, ?)"
and then set the value of each ? placeholder with variable names. This
would relieve the messiness caused by worrying about missing quotes and
readability issues, etc.

Can someone provide a basic code snippet that demonstrates how to do
this? I know there are all sorts of toolbar things that can be dragged
and dropped to create some of this visually. But my need is so basic I
just want to have the minimal code to create a db connection, execute an
insert, and perhaps retrieve the @@identity value associated with the
inserted row.

Thanks in advance!

Steve

[natzol]

Check this out - might be helpful:
How to avoid writing SQL for ADO.NET DataAdapter(By Dejan Grujic )
http://www.codeproject.com/cs/databa...andBuilder.asp
"Steve Franks" wrote:

I'm new to ASP.NET and working with the 2.0 beta. As a classic ASP
developer I have a lot of code that looks like this:

sql = "insert into x values("
sql = sql & val1 & ", " & val2 & ", ' " & strVal3 & " ', " & val4 & ")"

Which of course gets very messy. As I understand it there is a way to use a
placeholder string with question marks like "values(?, ?, ?, ?)" and then
set the value of each ? placeholder with variable names. This would relieve
the messiness caused by worrying about missing quotes and readability
issues, etc.

Can someone provide a basic code snippet that demonstrates how to do this?
I know there are all sorts of toolbar things that can be dragged and dropped
to create some of this visually. But my need is so basic I just want to
have the minimal code to create a db connection, execute an insert, and
perhaps retrieve the @@identity value associated with the inserted row.

Thanks in advance!

Steve