Dynamic Insert Statment

6 Feb 2006 2:33 PM

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.

6 Feb 2006 3:45 PM

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

Show quoteHide quote

"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

7 Feb 2006 9:52 AM

Tapio Kulmala

You have "insert into" twice in your sqlcommand.

In article <#txIXpyKGHA.3***@TK2MSFTNGP09.phx.gbl>,
denis.corre***@shaw.co.uk says...
Show quoteHide quote

> 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.
>
>
>