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]

RE: Dynamic Insert Statment

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:
[color=blue]
> 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[/color]

[Tapio Kulmala]

Re: Dynamic Insert Statment

You have "insert into" twice in your sqlcommand.



In article <#txIXpyKGHA.32 76@TK2MSFTNGP09 .phx.gbl>,
denis.correard@ shaw.co.uk says...[color=blue]
> 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.
>
>
>[/color]