Use ExecuteScalar to return identity or Guid newID?

[eagle]

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]

include this in your sql statement


select scope_identity() as returnidentity

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

 

[Clint Hill]

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]

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

 

[eagle]

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.

 

[Clint Hill]

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