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.SqlCo mmand(lNewVaria ble, objConnection)

objCmd.Paramete rs.Add("@ID", SqlDbType.BigIn t).Direction = ParameterDirect ion.ReturnValue

objCmd.ExecuteN onQuery()

TextBox1.Text = objCmd.Paramete rs("@ID").Valu e

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.ExecuteN onQuery()

'Get the auto-generated primary key
objCmd.CommandT ext = "Select @@IDENTITY"
TextBox1.Text = objCmd.ExecuteS calar

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.SqlCo mmand(lNewVaria ble, objConnection)

objCmd.Paramete rs.Add("@ID", SqlDbType.BigIn t).Direction = ParameterDirect ion.ReturnValue

objCmd.ExecuteN onQuery()

TextBox1.Text = objCmd.Paramete rs("@ID").Valu e

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.SqlCo mmand(lNewVaria ble, objConnection)

objCmd.Paramete rs.Add("@ID", SqlDbType.BigIn t).Direction = ParameterDirect ion.ReturnValue

objCmd.ExecuteN onQuery()

TextBox1.Text = objCmd.Paramete rs("@ID").Valu e

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.