Search:  
NamespacesDiscussions.NET v1.1Feedback
Executing Query that returns a parameter
Messages   Related Types
This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.


Julian Voelcker
OK, I'm being a bit thick here.

I usually do Inserts and Updates to a database just by executing a
query, however I now want to be able to get an Insert query to return
the id value for the record that is created.

Tim Musschoot over on the SqlHowTo list has suggested using the
following:-

INSERT INTO TABLE (FIELD) VALUES (VALUE) SELECT @@IDENTITY AS IDCOL

This seems logical, but how would I achieve this in C#?

Any pointers would be appreciated.

Cheers,

Julian Voelcker
The Virtual World (UK) Limited
Cirencester, United Kingdom
Reply to this message...
 
    
Peter Brunone
Julian,

    If you want a single value back from a query, use the ExecuteScalar()
method of the SqlCommand class. It returns an Object type, which you can
then cast as appropriate. Here's a snippet from my collection to get you
going:

'****************************
Dim objConn As New SqlConnection(strConn)
Dim objCmd As New SqlCommand(strSQL, objConn)
Dim strResult As Object

objConn.Open()
objCmd = New SqlCommand(strSQL, objConn)

Try
strResult = objCmd.ExecuteScalar
Catch e As Exception
LogEvent("Error calling ExecuteScalar: " & e.Message)
Finally
objCmd.Dispose()
objConn.Close()
End Try
'****************************

Have fun...

Peter

|-----Original Message-----
|From: Julian Voelcker [mailto:Click here to reveal e-mail address]
|
|OK, I'm being a bit thick here.
|
|I usually do Inserts and Updates to a database just by executing a
|query, however I now want to be able to get an Insert query to return
|the id value for the record that is created.
|
|Tim Musschoot over on the SqlHowTo list has suggested using the
|following:-
|
|INSERT INTO TABLE (FIELD) VALUES (VALUE) SELECT @@IDENTITY AS IDCOL
|
|This seems logical, but how would I achieve this in C#?
|
|Any pointers would be appreciated.
|
|Cheers,
|
|Julian Voelcker
|The Virtual World (UK) Limited
|Cirencester, United Kingdom
|
Reply to this message...
 
    
Iain Smallwood
Is it not the case that for the code below to work you need to use "SET
NOCOUNT ON" so that you do not get a resultset that says "One row
affected" for the insert, and another resultset for the actual identity?
I have always worked on this principle though have not tested it
recently. (May forestall a problem for you.)

Cheers,
Iain

-----Original Message-----
From: Peter Brunone [mailto:Click here to reveal e-mail address]
Sent: 20 June 2002 02:55
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Executing Query that returns a parameter

Julian,

    If you want a single value back from a query, use the
ExecuteScalar() method of the SqlCommand class. It returns an Object
type, which you can then cast as appropriate. Here's a snippet from my
collection to get you
going:

'****************************
Dim objConn As New SqlConnection(strConn)
Dim objCmd As New SqlCommand(strSQL, objConn)
Dim strResult As Object

objConn.Open()
objCmd = New SqlCommand(strSQL, objConn)

Try
strResult = objCmd.ExecuteScalar
Catch e As Exception
LogEvent("Error calling ExecuteScalar: " & e.Message) Finally
objCmd.Dispose()
objConn.Close()
End Try
'****************************

Have fun...

Peter

|-----Original Message-----
|From: Julian Voelcker [mailto:Click here to reveal e-mail address]
|
|OK, I'm being a bit thick here.
|
|I usually do Inserts and Updates to a database just by executing a
|query, however I now want to be able to get an Insert query to return
|the id value for the record that is created.
|
|Tim Musschoot over on the SqlHowTo list has suggested using the
|following:-
|
|INSERT INTO TABLE (FIELD) VALUES (VALUE) SELECT @@IDENTITY AS IDCOL
|
|This seems logical, but how would I achieve this in C#?
|
|Any pointers would be appreciated.
|
|Cheers,
|
|Julian Voelcker
|The Virtual World (UK) Limited
|Cirencester, United Kingdom
|

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Disclaimer
This message may contain information which is legally privileged and/or
confidential. If you are not the intended recipient, you are hereby
notified that any unauthorised disclosure, copying, distribution or use
of this information is strictly prohibited. Such notification not
withstanding, any comments or opinions expressed are those of the
originator, not of Taylor Made Computer Solutions, unless otherwise
explicitly stated.
Reply to this message...
 
    
Peter Brunone
Iain,

    Thanks for bringing up a valid point. Yes, you must SET NOCOUNT ON for the
bulk of your operations, and then just before returning the parameter, SET
NOCOUNT OFF.

Cheers,

Peter

|-----Original Message-----
|From: Iain Smallwood [mailto:Click here to reveal e-mail address]
|
|Is it not the case that for the code below to work you need to use "SET
|NOCOUNT ON" so that you do not get a resultset that says "One row
|affected" for the insert, and another resultset for the actual identity?
|I have always worked on this principle though have not tested it
|recently. (May forestall a problem for you.)
|
|Cheers,
|Iain
|
|-----Original Message-----
|From: Peter Brunone [mailto:Click here to reveal e-mail address]
|Sent: 20 June 2002 02:55
|To: ngfx-sqlclient
|Subject: [ngfx-sqlclient] RE: Executing Query that returns a parameter
|
|
|Julian,
|
|    If you want a single value back from a query, use the
|ExecuteScalar() method of the SqlCommand class. It returns an Object
|type, which you can then cast as appropriate. Here's a snippet from my
|collection to get you
|going:
|
|'****************************
|Dim objConn As New SqlConnection(strConn)
|Dim objCmd As New SqlCommand(strSQL, objConn)
|Dim strResult As Object
|
|objConn.Open()
|objCmd = New SqlCommand(strSQL, objConn)
|
|Try
| strResult = objCmd.ExecuteScalar
|Catch e As Exception
| LogEvent("Error calling ExecuteScalar: " & e.Message) Finally
| objCmd.Dispose()
| objConn.Close()
|End Try
|'****************************
|
|Have fun...
|
|Peter
|
||-----Original Message-----
||From: Julian Voelcker [mailto:Click here to reveal e-mail address]
||
||OK, I'm being a bit thick here.
||
||I usually do Inserts and Updates to a database just by executing a
||query, however I now want to be able to get an Insert query to return
||the id value for the record that is created.
||
||Tim Musschoot over on the SqlHowTo list has suggested using the
||following:-
||
||INSERT INTO TABLE (FIELD) VALUES (VALUE) SELECT @@IDENTITY AS IDCOL
||
||This seems logical, but how would I achieve this in C#?
||
||Any pointers would be appreciated.
||
||Cheers,
||
||Julian Voelcker
||The Virtual World (UK) Limited
||Cirencester, United Kingdom
||
|
|
|| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
|| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT
|
|Disclaimer
|This message may contain information which is legally privileged and/or
|confidential. If you are not the intended recipient, you are hereby
|notified that any unauthorised disclosure, copying, distribution or use
|of this information is strictly prohibited. Such notification not
|withstanding, any comments or opinions expressed are those of the
|originator, not of Taylor Made Computer Solutions, unless otherwise
|explicitly stated.
|
|
|
|| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
|| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT
|
Reply to this message...
 
    
David L. Penton
It isn't necessary to always use SET NOCOUNT ON (but I do recommend using it
where possible)

Here is an example:

http://www.davidpenton.com/testsite/scratch/insert_long_text_into_sql_statem
ent.aspx

David L. Penton, Microsoft MVP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Peter Brunone [mailto:Click here to reveal e-mail address]

Iain,

    Thanks for bringing up a valid point. Yes, you must SET NOCOUNT ON for the
bulk of your operations, and then just before returning the parameter, SET
NOCOUNT OFF.

Cheers,

Peter

|-----Original Message-----
|From: Iain Smallwood [mailto:Click here to reveal e-mail address]
|
|Is it not the case that for the code below to work you need to use "SET
|NOCOUNT ON" so that you do not get a resultset that says "One row
|affected" for the insert, and another resultset for the actual identity?
|I have always worked on this principle though have not tested it
|recently. (May forestall a problem for you.)
|
|Cheers,
|Iain
|
|-----Original Message-----
|From: Peter Brunone [mailto:Click here to reveal e-mail address]
|
|Julian,
|
|    If you want a single value back from a query, use the
|ExecuteScalar() method of the SqlCommand class. It returns an Object
|type, which you can then cast as appropriate. Here's a snippet from my
|collection to get you
|going:
|
|'****************************
|Dim objConn As New SqlConnection(strConn)
|Dim objCmd As New SqlCommand(strSQL, objConn)
|Dim strResult As Object
|
|objConn.Open()
|objCmd = New SqlCommand(strSQL, objConn)
|
|Try
| strResult = objCmd.ExecuteScalar
|Catch e As Exception
| LogEvent("Error calling ExecuteScalar: " & e.Message) Finally
| objCmd.Dispose()
| objConn.Close()
|End Try
|'****************************
|
|Have fun...
|
|Peter
|
||-----Original Message-----
||From: Julian Voelcker [mailto:Click here to reveal e-mail address]
||
||OK, I'm being a bit thick here.
||
||I usually do Inserts and Updates to a database just by executing a
||query, however I now want to be able to get an Insert query to return
||the id value for the record that is created.
||
||Tim Musschoot over on the SqlHowTo list has suggested using the
||following:-
||
||INSERT INTO TABLE (FIELD) VALUES (VALUE) SELECT @@IDENTITY AS IDCOL
||
||This seems logical, but how would I achieve this in C#?
||
||Any pointers would be appreciated.
||
||Cheers,
||
||Julian Voelcker
||The Virtual World (UK) Limited
||Cirencester, United Kingdom
Reply to this message...
 
    
Iain Smallwood
No, but ExecuteScalar will only return the first item on the agenda.
Thus to use that particular syntax, which I kind of like, you DO need to
(and you want the identity). That was my point - please correct me if
I'm wrong.

Go England - steamroller Brazil! (no apologies for bemusing Americans in
their own little sporting world! :-) )

Cheers,
Iain

-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: 20 June 2002 15:32
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Executing Query that returns a parameter

It isn't necessary to always use SET NOCOUNT ON (but I do recommend
using it where possible)

Here is an example:

http://www.davidpenton.com/testsite/scratch/insert_long_text_into_sql_st
atem
ent.aspx

David L. Penton, Microsoft MVP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach" Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Peter Brunone [mailto:Click here to reveal e-mail address]

Iain,

    Thanks for bringing up a valid point. Yes, you must SET NOCOUNT
ON for the bulk of your operations, and then just before returning the
parameter, SET NOCOUNT OFF.

Cheers,

Peter

|-----Original Message-----
|From: Iain Smallwood [mailto:Click here to reveal e-mail address]
|
|Is it not the case that for the code below to work you need to use "SET

|NOCOUNT ON" so that you do not get a resultset that says "One row
|affected" for the insert, and another resultset for the actual
|identity? I have always worked on this principle though have not tested

|it recently. (May forestall a problem for you.)
|
|Cheers,
|Iain
|
|-----Original Message-----
|From: Peter Brunone [mailto:Click here to reveal e-mail address]
|
|Julian,
|
|    If you want a single value back from a query, use the
|ExecuteScalar() method of the SqlCommand class. It returns an Object
|type, which you can then cast as appropriate. Here's a snippet from my

|collection to get you
|going:
|
|'****************************
|Dim objConn As New SqlConnection(strConn)
|Dim objCmd As New SqlCommand(strSQL, objConn)
|Dim strResult As Object
|
|objConn.Open()
|objCmd = New SqlCommand(strSQL, objConn)
|
|Try
| strResult = objCmd.ExecuteScalar
|Catch e As Exception
| LogEvent("Error calling ExecuteScalar: " & e.Message) Finally
| objCmd.Dispose()
| objConn.Close()
|End Try
|'****************************
|
|Have fun...
|
|Peter
|
||-----Original Message-----
||From: Julian Voelcker [mailto:Click here to reveal e-mail address]
||
||OK, I'm being a bit thick here.
||
||I usually do Inserts and Updates to a database just by executing a
||query, however I now want to be able to get an Insert query to return
||the id value for the record that is created.
||
||Tim Musschoot over on the SqlHowTo list has suggested using the
||following:-
||
||INSERT INTO TABLE (FIELD) VALUES (VALUE) SELECT @@IDENTITY AS IDCOL
||
||This seems logical, but how would I achieve this in C#?
||
||Any pointers would be appreciated.
||
||Cheers,
||
||Julian Voelcker
||The Virtual World (UK) Limited
||Cirencester, United Kingdom

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Disclaimer
This message may contain information which is legally privileged and/or
confidential. If you are not the intended recipient, you are hereby
notified that any unauthorised disclosure, copying, distribution or use
of this information is strictly prohibited. Such notification not
withstanding, any comments or opinions expressed are those of the
originator, not of Taylor Made Computer Solutions, unless otherwise
explicitly stated.
Reply to this message...
 
    
Iain Smallwood
Sorry David - just took your point. Doh! Fair enough. Too excited by
footy to understand C#.

-----Original Message-----
From: Iain Smallwood
Sent: 20 June 2002 15:32
To: 'ngfx-sqlclient'
Subject: RE: [ngfx-sqlclient] RE: Executing Query that returns a
parameter

No, but ExecuteScalar will only return the first item on the agenda.
Thus to use that particular syntax, which I kind of like, you DO need to
(and you want the identity). That was my point - please correct me if
I'm wrong.

Go England - steamroller Brazil! (no apologies for bemusing Americans in
their own little sporting world! :-) )

Cheers,
Iain

-----Original Message-----
From: David L. Penton [mailto:Click here to reveal e-mail address]
Sent: 20 June 2002 15:32
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] RE: Executing Query that returns a parameter

It isn't necessary to always use SET NOCOUNT ON (but I do recommend
using it where possible)

Here is an example:

http://www.davidpenton.com/testsite/scratch/insert_long_text_into_sql_st
atem
ent.aspx

David L. Penton, Microsoft MVP
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach" Click here to reveal e-mail address

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol

-----Original Message-----
From: Peter Brunone [mailto:Click here to reveal e-mail address]

Iain,

    Thanks for bringing up a valid point. Yes, you must SET NOCOUNT
ON for the bulk of your operations, and then just before returning the
parameter, SET NOCOUNT OFF.

Cheers,

Peter

|-----Original Message-----
|From: Iain Smallwood [mailto:Click here to reveal e-mail address]
|
|Is it not the case that for the code below to work you need to use "SET

|NOCOUNT ON" so that you do not get a resultset that says "One row
|affected" for the insert, and another resultset for the actual
|identity? I have always worked on this principle though have not tested

|it recently. (May forestall a problem for you.)
|
|Cheers,
|Iain
|
|-----Original Message-----
|From: Peter Brunone [mailto:Click here to reveal e-mail address]
|
|Julian,
|
|    If you want a single value back from a query, use the
|ExecuteScalar() method of the SqlCommand class. It returns an Object
|type, which you can then cast as appropriate. Here's a snippet from my

|collection to get you
|going:
|
|'****************************
|Dim objConn As New SqlConnection(strConn)
|Dim objCmd As New SqlCommand(strSQL, objConn)
|Dim strResult As Object
|
|objConn.Open()
|objCmd = New SqlCommand(strSQL, objConn)
|
|Try
| strResult = objCmd.ExecuteScalar
|Catch e As Exception
| LogEvent("Error calling ExecuteScalar: " & e.Message) Finally
| objCmd.Dispose()
| objConn.Close()
|End Try
|'****************************
|
|Have fun...
|
|Peter
|
||-----Original Message-----
||From: Julian Voelcker [mailto:Click here to reveal e-mail address]
||
||OK, I'm being a bit thick here.
||
||I usually do Inserts and Updates to a database just by executing a
||query, however I now want to be able to get an Insert query to return
||the id value for the record that is created.
||
||Tim Musschoot over on the SqlHowTo list has suggested using the
||following:-
||
||INSERT INTO TABLE (FIELD) VALUES (VALUE) SELECT @@IDENTITY AS IDCOL
||
||This seems logical, but how would I achieve this in C#?
||
||Any pointers would be appreciated.
||
||Cheers,
||
||Julian Voelcker
||The Virtual World (UK) Limited
||Cirencester, United Kingdom

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Disclaimer
This message may contain information which is legally privileged and/or
confidential. If you are not the intended recipient, you are hereby
notified that any unauthorised disclosure, copying, distribution or use
of this information is strictly prohibited. Such notification not
withstanding, any comments or opinions expressed are those of the
originator, not of Taylor Made Computer Solutions, unless otherwise
explicitly stated.
Reply to this message...
 
 
System.Data.SqlClient.SqlCommand
System.Data.SqlClient.SqlConnection
Ad
MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT
 
 Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com