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("exampleInsert", MyConnection);
//MyConnection must already be declared as SqlConnection
cmd.Parameters.Add(new SqlPArameters("@val1", SqlDbType.VarChar,
200)).Value = val1; //val 1 would be your value
cmd.Parameters.Add(new SqlPArameters("@val2", SqlDbType.VarChar,
200)).Value = val2;
MyConnection.Open()
cmd.ExecuteNonQuery();
MyConnection.Close();

HTH

 

[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]

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.VarChar, 200)).Value
= val1;
cmd.Parameters.Add(new SqlParameter("@val2", SqlDbType.VarChar, 200)).Value
= val2;

 

[Guest]

Check this out - might be helpful:
How to avoid writing SQL for ADO.NET DataAdapter(By Dejan Grujic )
http://www.codeproject.com/cs/database/CustomCommandBuilder.asp