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. 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.
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.
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.
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.
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.
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.
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.
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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,'" &...
|
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 ?
|
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 =...
|
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...
|
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...
|
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...
|
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?
|
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,...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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...
| |