473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Output parameters for dynamic SQL

Hi everyone,

I am saving a record to SQL and want to return the ID of the record
added as an output parameter, can you do this in dynamic SQL, without
the use of a stored procedure??

i.e.

comm = new sqlcommand
comm.commandtext = "Insert (name) values (@name); select @id =
SCOPE_IDENTITY();

' add parameters to command etc

comm.executeNonQuery()

dim a as integer = ctype(comm.parameters.item("@id").value, integer)

Is this possible?? I know you can do it in SQL but we have over 80
databases that this data layer will connect to and i am not one for
duplicate code over each database, just encase one database had a
different version of the sproc for whatever reason.

Aug 18 '06 #1
2 3657
Nemisis,

Try this:

Dim oTestCon As SqlConnection
Dim oTestCmd As SqlCommand
Dim result As Int16

oTestCon = New SqlConnection("Data Source=XXXX;Initial Catalog=XXX;User
Id=XXX;Password=XXX;")
oTestCmd = New SqlCommand("INSERT INTO [table] (field) VALUES (value)
SELECT @@identity", oTestCon)

oTestCon.Open()
result = oTestCmd.ExecuteScalar()
oTestCon.Close()
Response.Write(result)

Should give you what you're looking for (result = newly added rec id).
Let me know if I misunderstood what you needed.

dkb

Aug 18 '06 #2
Hi,

Yes, you can add output parameters to queries:

Dim command As New SqlCommand("SELECT @ID = 55", conn)
command.Parameters.Add("@ID", System.Data.SqlDbType.Int)
command.Parameters.Items("@ID").Direction = System.Data.ParameterDirection.Output

command.ExecuteNonQuery()

Dim id As Integer = CType(command.Parameters.Items("@ID").Value, Integer);

--
Dave Sexton

"Nemisis" <da*********@hotmail.comwrote in message news:11**********************@m73g2000cwd.googlegr oups.com...
Hi everyone,

I am saving a record to SQL and want to return the ID of the record
added as an output parameter, can you do this in dynamic SQL, without
the use of a stored procedure??

i.e.

comm = new sqlcommand
comm.commandtext = "Insert (name) values (@name); select @id =
SCOPE_IDENTITY();

' add parameters to command etc

comm.executeNonQuery()

dim a as integer = ctype(comm.parameters.item("@id").value, integer)

Is this possible?? I know you can do it in SQL but we have over 80
databases that this data layer will connect to and i am not one for
duplicate code over each database, just encase one database had a
different version of the sproc for whatever reason.

Aug 18 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bari Allen | last post by:
I have a Stored procedure in SQL, that works, when tested in SQL, with one input & several output parameters, as follows: CREATE PROCEDURE myProcedure @MyID int , @First varchar(80) OUTPUT ,...
2
by: Lepa | last post by:
Hi I'm trying to make this to work and need help Here my SP and I'm building sql with output param. Alter PROCEDURE lpsadmin_getSBWReorderDollars ( @out decimal(10,2) output, @sType...
7
by: LineVoltageHalogen | last post by:
Greetings All, I have a very large query that uses dynamic sql. The sql is very large and it requires it to be broken into three components to avoid the nvarchar(4000) issue: SET @v_SqlString(...
11
by: Axel | last post by:
Hi, I am currently creating an ASP page that returns a recordset of search result based on multiple keywords. The where string is dynamically built on the server page and that part work quite...
8
by: FS Liu | last post by:
Hi, I am writing ATL Service application (XML Web service) in VS.NET C++. Are there any sample programs that accept XML as input and XML as output in the web service? Thank you very much.
2
by: Bari Allen | last post by:
ASP Classic question: I have a Stored procedure in SQL, that works, when tested in SQL, with one input & several output parameters, as follows: CREATE PROCEDURE myProcedure @MyID int , @First...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
1
by: Nemisis | last post by:
Hi everyone, I am saving a record to SQL and want to return the ID of the record added as an output parameter, can you do this in dynamic SQL, without the use of a stored procedure?? i.e. ...
2
by: gabosom | last post by:
Hi! I've been breaking my head trying to get the output variables from my Stored Procedure. This is my SP code CREATE PROCEDURE GetKitchenOrderDetail( @idService int, --outPut Variables ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.