473,320 Members | 1,804 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,320 software developers and data experts.

Use ExecuteScalar to return identity or Guid newID?

How can I return the new id that is created when an insert command is used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException: Object
reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't create a
stored procedure for this for various reasons.
Nov 19 '05 #1
9 10158
include this in your sql statement
select scope_identity() as returnidentity

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException: Object
reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't create a
stored procedure for this for various reasons.

Nov 19 '05 #2
include this in your sql statement
select scope_identity() as returnidentity

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException: Object
reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't create a
stored procedure for this for various reasons.

Nov 19 '05 #3
Actually, you will probably need to run two commands to get it done.
Keep your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException: Object
reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't create a
stored procedure for this for various reasons.

Nov 19 '05 #4
Actually, you will probably need to run two commands to get it done.
Keep your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException: Object
reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't create a
stored procedure for this for various reasons.

Nov 19 '05 #5
That worked great, thanks for your help! fyi, I was able to put it into one
qry:
"qry = "insert into tblClients (lname, fname) values ('smith', 'joe');
select newid()"
(or select scope_identity for identity fields)
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry).tostring

"Clint Hill" <cl********@nospamath3osoftware.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Actually, you will probably need to run two commands to get it done. Keep
your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is
used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException:
Object reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't
create a stored procedure for this for various reasons.


Nov 19 '05 #6
That worked great, thanks for your help! fyi, I was able to put it into one
qry:
"qry = "insert into tblClients (lname, fname) values ('smith', 'joe');
select newid()"
(or select scope_identity for identity fields)
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry).tostring

"Clint Hill" <cl********@nospamath3osoftware.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Actually, you will probably need to run two commands to get it done. Keep
your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is
used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text, qry)

I tried this and received an error "System.NullReferenceException:
Object reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't
create a stored procedure for this for various reasons.


Nov 19 '05 #7
Never mind, that didn't work. The scope_Identity() works for identity
fields, what about uniqueidentifier fields? Is there a way to return the
new id from a uniqueidentifier field?

Thanks for all your help.
"eagle" <ea***@yahoo.com> wrote in message
news:u6**************@TK2MSFTNGP09.phx.gbl...
That worked great, thanks for your help! fyi, I was able to put it into
one qry:
"qry = "insert into tblClients (lname, fname) values ('smith', 'joe');
select newid()"
(or select scope_identity for identity fields)
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry).tostring

"Clint Hill" <cl********@nospamath3osoftware.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Actually, you will probably need to run two commands to get it done. Keep
your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is
used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry)

I tried this and received an error "System.NullReferenceException:
Object reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't
create a stored procedure for this for various reasons.

Nov 19 '05 #8
Never mind, that didn't work. The scope_Identity() works for identity
fields, what about uniqueidentifier fields? Is there a way to return the
new id from a uniqueidentifier field?

Thanks for all your help.
"eagle" <ea***@yahoo.com> wrote in message
news:u6**************@TK2MSFTNGP09.phx.gbl...
That worked great, thanks for your help! fyi, I was able to put it into
one qry:
"qry = "insert into tblClients (lname, fname) values ('smith', 'joe');
select newid()"
(or select scope_identity for identity fields)
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry).tostring

"Clint Hill" <cl********@nospamath3osoftware.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Actually, you will probably need to run two commands to get it done. Keep
your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
How can I return the new id that is created when an insert command is
used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry)

I tried this and received an error "System.NullReferenceException:
Object reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't
create a stored procedure for this for various reasons.

Nov 19 '05 #9
Well, you can use ROWGUIDCOL in the select clause, however you will need
to add some method of grabbing it against the most recent data inserted.

Same concept, just using different select statement.

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:
Never mind, that didn't work. The scope_Identity() works for identity
fields, what about uniqueidentifier fields? Is there a way to return the
new id from a uniqueidentifier field?

Thanks for all your help.
"eagle" <ea***@yahoo.com> wrote in message
news:u6**************@TK2MSFTNGP09.phx.gbl...
That worked great, thanks for your help! fyi, I was able to put it into
one qry:
"qry = "insert into tblClients (lname, fname) values ('smith', 'joe');
select newid()"
(or select scope_identity for identity fields)
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry).tostring

"Clint Hill" <cl********@nospamath3osoftware.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl.. .
Actually, you will probably need to run two commands to get it done. Keep
your connection open for both and use it in both.

psuedo code:

SqlConn.Open()
InsertCommand.Execute(SqlConn) -- using your insert statement
int id = IdCommand.Execute(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Close()

Clint Hill
H3O Software
http://www.h3osoftware.com

eagle wrote:

How can I return the new id that is created when an insert command is
used?
for example:

qry = "insert into tblClients (lname, fname) values ('smith', 'joe')"
Dim xyz as string = command.executescalar(strconn, commandtype.text,
qry)

I tried this and received an error "System.NullReferenceException:
Object reference not set to an instance of an object"

Although the insert does take place. How would I do this? I can't
create a stored procedure for this for various reasons.



--
Clint Hill
H3O Software
http://www.h3osoftware.com
Nov 19 '05 #10

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

Similar topics

5
by: Filips Benoit | last post by:
Sorry I'm new in this, Add a record using T-SQL and Connection.Execute How can i insert the identity in a VB-variable Dim objConn As ADODB.Connection Set objConn = New ADODB.Connection...
1
by: Filips Benoit | last post by:
Dear All, I try this but it keeps returning zero ! Dim newid As Long CurrentProject.Connection.Execute "INSERT INTO (ORD_P_ID, ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" &...
24
by: Ilija_G | last post by:
Hi, Is there any replace for "Select @@identity" that could return "just inserted" GUID as a primary key? Has anyone tested what's faster, working with Guid or Autonumber ?
0
by: eagle | last post by:
How can I return the new id that is created when an insert command is used? for example: qry = "insert into tblClients (lname, fname) values ('smith', 'joe')" Dim xyz as string =...
3
by: ASP .NET Newbie | last post by:
I am trying to insert a new record into my database, and have it return a uniqueidentifier as the "newcatid". I don't use integers as my "id"'s, but rather uniqueidentifiers. Here's my Stored...
12
by: Geoff Jones | last post by:
Hi How do I set a primary key to be a GUID? That is, using the following code, I can set an integer primary key, but how do I change it to a GUID? Dim databaseCommand As OleDbCommand = New...
3
by: Susanne Klemm | last post by:
Hello! I use a procedure to insert a new row into a table with an identity column. The procedure has an output parameter which gives me the inserted identity value. This worked well for a long...
14
by: Roy | last post by:
Is there an equivalent class function in C# to generate sequencial guid just like the NewSequentialID() function in SQL Server 2005?
0
JustRun
by: JustRun | last post by:
Dears, I want to Insert row and retrieve the Identity : SqlCommand cmd1 = new SqlCommand( "INSERT INTO SalesTable (ProductId, CustomerId, Qty, SaleDate, SalePrice,...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: 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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.