Dynamic Insert Statment

[Denis Correard]

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.

[Kerry Moorman]

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

[Tapio Kulmala]

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.