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.execute scalar(strconn, commandtype.tex t, qry)
I tried this and received an error "System.NullRef erenceException : 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  10247 
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.execute scalar(strconn, commandtype.tex t, qry)
I tried this and received an error "System.NullRef erenceException : 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.execute scalar(strconn, commandtype.tex t, qry)
I tried this and received an error "System.NullRef erenceException : 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.E xecute(SqlConn) -- using your insert statement
int id = IdCommand.Execu te(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.execute scalar(strconn, commandtype.tex t, qry)
I tried this and received an error "System.NullRef erenceException : 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.E xecute(SqlConn) -- using your insert statement
int id = IdCommand.Execu te(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.execute scalar(strconn, commandtype.tex t, qry)
I tried this and received an error "System.NullRef erenceException : 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.execute scalar(strconn, commandtype.tex t,
qry).tostring
"Clint Hill" <cl********@nos pamath3osoftwar e.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.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.E xecute(SqlConn) -- using your insert statement
int id = IdCommand.Execu te(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.execute scalar(strconn, commandtype.tex t, qry)
I tried this and received an error "System.NullRef erenceException :
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.execute scalar(strconn, commandtype.tex t,
qry).tostring
"Clint Hill" <cl********@nos pamath3osoftwar e.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.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.E xecute(SqlConn) -- using your insert statement
int id = IdCommand.Execu te(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.execute scalar(strconn, commandtype.tex t, qry)
I tried this and received an error "System.NullRef erenceException :
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 uniqueidentifie r fields? Is there a way to return the
new id from a uniqueidentifie r field?
Thanks for all your help.
"eagle" <ea***@yahoo.co m> wrote in message
news:u6******** ******@TK2MSFTN GP09.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.execute scalar(strconn, commandtype.tex t,
qry).tostring
"Clint Hill" <cl********@nos pamath3osoftwar e.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.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.E xecute(SqlConn) -- using your insert statement
int id = IdCommand.Execu te(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.execute scalar(strconn, commandtype.tex t,
qry)
I tried this and received an error "System.NullRef erenceException :
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 uniqueidentifie r fields? Is there a way to return the
new id from a uniqueidentifie r field?
Thanks for all your help.
"eagle" <ea***@yahoo.co m> wrote in message
news:u6******** ******@TK2MSFTN GP09.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.execute scalar(strconn, commandtype.tex t,
qry).tostring
"Clint Hill" <cl********@nos pamath3osoftwar e.com> wrote in message
news:%2******** ********@TK2MSF TNGP09.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.E xecute(SqlConn) -- using your insert statement
int id = IdCommand.Execu te(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.execute scalar(strconn, commandtype.tex t,
qry)
I tried this and received an error "System.NullRef erenceException :
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 uniqueidentifie r fields? Is there a way to return the
new id from a uniqueidentifie r field?
Thanks for all your help.
"eagle" <ea***@yahoo.co m> wrote in message
news:u6******** ******@TK2MSFTN GP09.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.execute scalar(strconn, commandtype.tex t,
qry).tostri ng
"Clint Hill" <cl********@nos pamath3osoftwar e.com> wrote in message
news:%2****** **********@TK2M SFTNGP09.phx.gb l...
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 ()
InsertComman d.Execute(SqlCo nn) -- using your insert statement
int id = IdCommand.Execu te(SqlConn) -- using either scope_identity or
@@identity
SqlConn.Clos e()
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.execute scalar(strconn, commandtype.tex t,
qry)
I tried this and received an error "System.NullRef erenceException :
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
objConn = CurrentProject.Connection
objConn.Open
objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')
|
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,'" & Year(Date) & "-" &
Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as
'newID'"
Me.Requery
|
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 = 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
|
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 Proc:
ALTER PROCEDURE dbo.spNewCat
(
@newcatidID uniqueidentifier OUTPUT,
@catname varchar(50) )
AS
INSERT INTO Categories (catname) VALUES (@catname)
| | |
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 OleDbCommand("CREATE TABLE books
(" ID INT IDENTITY, Book VARCHAR(50), CONSTRAINT PRIMARY KEY (ID))",
myDatabaseConnection)
|
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 time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.
My Procedure:
|
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, SalePolicy, Notes) " +
" VALUES (@ProductId, @CustomerId, @Qty, @SaleDate, @SalePrice, @SalePolicy, @Notes)" +
" SELECT SCOPE_IDENTITY() ", dbModule.con);
each time the execute scaller return Empty (Guid should...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
