467,117 Members | 1,036 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,117 developers. It's quick & easy.

Parameter with auto increment

Hello,

I have an autoincrement field and I'm not sure how to use a parameter
with it to insert data:

this.sqlCommand1.CommandText = "INSERT INTO SickAndTired (String_ID,
Input_string) VALUES (@Parameter1,@Parameter2)";

this.sqlCommand1.Connection = this.sqlConnection1;

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter1",
System.Data.SqlDbType.UniqueIdentifier, 0, "String_ID"));

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter2",
System.Data.SqlDbType.NVarChar, 0, "Input_String"));

this.sqlConnection1.Open();

string TB1 = this.textBox1.Text;

sqlCommand1.Parameters["@Parameter1"].Value= (What do I put
here???????????????????)

sqlCommand1.Parameters["@Parameter2"].Value=TB1;

sqlCommand1.ExecuteNonQuery();

this.sqlConnection1.Close();

Thanks

Mike
Nov 17 '05 #1
  • viewed: 6866
Share:
4 Replies
Mike,

For something like this, you aren't going to get it through a parameter
in a select. Rather, you have to issue a select on the same open connection
on the @@IDENTITY value, which will return the last inserted identity value
for that session.

You have to be careful, since the identity can be changed by triggers
(if you insert into other tables with auto-generated identity fields then
that value will be returned).

Generally, this is a good argument for managing the id generation
yourself. Personally, I prefer to use GUIDs, since I can generate them
before the operation starts, and know exactly what my id will be (before and
after).

If you want to get this in a parameter, then create a stored procedure
which will select the @@IDENTITY value and return that in a parameter.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Mike" <am******@verizon.net> wrote in message
news:N6_Re.121$tx.8@trndny02...
Hello,

I have an autoincrement field and I'm not sure how to use a parameter
with it to insert data:

this.sqlCommand1.CommandText = "INSERT INTO SickAndTired (String_ID,
Input_string) VALUES (@Parameter1,@Parameter2)";

this.sqlCommand1.Connection = this.sqlConnection1;

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter1",
System.Data.SqlDbType.UniqueIdentifier, 0, "String_ID"));

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter2",
System.Data.SqlDbType.NVarChar, 0, "Input_String"));

this.sqlConnection1.Open();

string TB1 = this.textBox1.Text;

sqlCommand1.Parameters["@Parameter1"].Value= (What do I put
here???????????????????)

sqlCommand1.Parameters["@Parameter2"].Value=TB1;

sqlCommand1.ExecuteNonQuery();

this.sqlConnection1.Close();

Thanks

Mike

Nov 17 '05 #2
scope_identity() is probably safer to use. @@identity can potentially
produce very hard to find bugs in your app.

"Mike" wrote:
Hello,

I have an autoincrement field and I'm not sure how to use a parameter
with it to insert data:

this.sqlCommand1.CommandText = "INSERT INTO SickAndTired (String_ID,
Input_string) VALUES (@Parameter1,@Parameter2)";

this.sqlCommand1.Connection = this.sqlConnection1;

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter1",
System.Data.SqlDbType.UniqueIdentifier, 0, "String_ID"));

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter2",
System.Data.SqlDbType.NVarChar, 0, "Input_String"));

this.sqlConnection1.Open();

string TB1 = this.textBox1.Text;

sqlCommand1.Parameters["@Parameter1"].Value= (What do I put
here???????????????????)

sqlCommand1.Parameters["@Parameter2"].Value=TB1;

sqlCommand1.ExecuteNonQuery();

this.sqlConnection1.Close();

Thanks

Mike

Nov 17 '05 #3
All I did was left that parameter out and it incremented by 1.Should I have
made it more complicated?

"carion1" <ca*****@discussions.microsoft.com> wrote in message
news:06**********************************@microsof t.com...
scope_identity() is probably safer to use. @@identity can potentially
produce very hard to find bugs in your app.

"Mike" wrote:
Hello,

I have an autoincrement field and I'm not sure how to use a parameter
with it to insert data:

this.sqlCommand1.CommandText = "INSERT INTO SickAndTired (String_ID,
Input_string) VALUES (@Parameter1,@Parameter2)";

this.sqlCommand1.Connection = this.sqlConnection1;

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter1",
System.Data.SqlDbType.UniqueIdentifier, 0, "String_ID"));

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter2",
System.Data.SqlDbType.NVarChar, 0, "Input_String"));

this.sqlConnection1.Open();

string TB1 = this.textBox1.Text;

sqlCommand1.Parameters["@Parameter1"].Value= (What do I put
here???????????????????)

sqlCommand1.Parameters["@Parameter2"].Value=TB1;

sqlCommand1.ExecuteNonQuery();

this.sqlConnection1.Close();

Thanks

Mike

Nov 17 '05 #4
Were you trying to insert a value into the identity column? If you need to
do that for what ever reason you can use set identity_insert <table name> on.
You don't have to increment the identity column yourself unless your app
depends on the identity field not having gaps from deleted records.

"Mike" wrote:
All I did was left that parameter out and it incremented by 1.Should I have
made it more complicated?

"carion1" <ca*****@discussions.microsoft.com> wrote in message
news:06**********************************@microsof t.com...
scope_identity() is probably safer to use. @@identity can potentially
produce very hard to find bugs in your app.

"Mike" wrote:
Hello,

I have an autoincrement field and I'm not sure how to use a parameter
with it to insert data:

this.sqlCommand1.CommandText = "INSERT INTO SickAndTired (String_ID,
Input_string) VALUES (@Parameter1,@Parameter2)";

this.sqlCommand1.Connection = this.sqlConnection1;

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter1",
System.Data.SqlDbType.UniqueIdentifier, 0, "String_ID"));

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Parameter2",
System.Data.SqlDbType.NVarChar, 0, "Input_String"));

this.sqlConnection1.Open();

string TB1 = this.textBox1.Text;

sqlCommand1.Parameters["@Parameter1"].Value= (What do I put
here???????????????????)

sqlCommand1.Parameters["@Parameter2"].Value=TB1;

sqlCommand1.ExecuteNonQuery();

this.sqlConnection1.Close();

Thanks

Mike


Nov 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Bruce Stockwell | last post: by
2 posts views Thread by Tom | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.