By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,704 Members | 1,122 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,704 IT Pros & Developers. It's quick & easy.

Dynamic Insert Statment

P: n/a
I have an application that create an insert statment on the fly (it would be to complex to create stored procedures for all senarios) like:
insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0)
so fare so good but I would like my statement to return the new PK_ID

So I tryed this:

Dim lNewVariable As String = "DECLARE @ID INT insert into insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0) SET @ID = @@Identity"

Dim objCmd As New SqlClient.SqlCommand(lNewVariable, objConnection)

objCmd.Parameters.Add("@ID", SqlDbType.BigInt).Direction = ParameterDirection.ReturnValue

objCmd.ExecuteNonQuery()

TextBox1.Text = objCmd.Parameters("@ID").Value

But I get 0 as return value.

Any ideas?

I cannot use the keywords OUTPUT NOR RETURN; I get errors.

any help would be apreciated.

thank you all in advance.

Feb 6 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Denis,

One option is to execute an Insert statememt using ExecuteNonQuery and then
execute a select @@Identity using ExecuteScalar:

'Execute the Insert statement:
objCmd.ExecuteNonQuery()

'Get the auto-generated primary key
objCmd.CommandText = "Select @@IDENTITY"
TextBox1.Text = objCmd.ExecuteScalar

Kerry Moorman
"Denis Correard" wrote:
I have an application that create an insert statment on the fly (it would be to complex to create stored procedures for all senarios) like:
insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0)
so fare so good but I would like my statement to return the new PK_ID

So I tryed this:

Dim lNewVariable As String = "DECLARE @ID INT insert into insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0) SET @ID = @@Identity"

Dim objCmd As New SqlClient.SqlCommand(lNewVariable, objConnection)

objCmd.Parameters.Add("@ID", SqlDbType.BigInt).Direction = ParameterDirection.ReturnValue

objCmd.ExecuteNonQuery()

TextBox1.Text = objCmd.Parameters("@ID").Value

But I get 0 as return value.

Any ideas?

I cannot use the keywords OUTPUT NOR RETURN; I get errors.

any help would be apreciated.

thank you all in advance

Feb 6 '06 #2

P: n/a
You have "insert into" twice in your sqlcommand.

In article <#t**************@TK2MSFTNGP09.phx.gbl>,
de************@shaw.co.uk says...
I have an application that create an insert statment on the fly (it would be to complex to create stored procedures for all senarios) like:
insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0)
so fare so good but I would like my statement to return the new PK_ID

So I tryed this:

Dim lNewVariable As String = "DECLARE @ID INT insert into insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0) SET @ID = @@Identity"

Dim objCmd As New SqlClient.SqlCommand(lNewVariable, objConnection)

objCmd.Parameters.Add("@ID", SqlDbType.BigInt).Direction = ParameterDirection.ReturnValue

objCmd.ExecuteNonQuery()

TextBox1.Text = objCmd.Parameters("@ID").Value

But I get 0 as return value.

Any ideas?

I cannot use the keywords OUTPUT NOR RETURN; I get errors.

any help would be apreciated.

thank you all in advance.


Feb 7 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.