• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

INSERT, then SELECT the inserted value's ID

I need a query to first insert data:

"INSERT INTO messages(callerID, messageContent, messageRead, userID) values(@CallerID, @Content, 0, @UserID)"

THEN select the messageID from the message that was just inserted in the INSERT statement. Can anyone help with this?

Thanks!
0
itprochris
Asked:
itprochris
  • 6
  • 5
1 Solution
 
itprochrisAuthor Commented:
... because my messageID is auto-increment, I can't get the ID. Thanks again!
0
 
Anthony PerkinsCommented:
Use the SCOPE_IDENTITY() function or if you are using SQL Server 7 use @@IDENTITY.  Like this:

DECLARE @MessageID integer

INSERT INTO messages(callerID, messageContent, messageRead, userID) values(@CallerID, @Content, 0, @UserID)"

SET @MessageID = SCOPE_IDENTITY()
0
 
itprochrisAuthor Commented:
how would I integrate this into my VB2005 project? I am trying to add the record, then return in a function the messageID that was just added. Thanks!
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Anthony PerkinsCommented:
>>how would I integrate this into my VB2005 project?<<
It depends.  The recommended way would be to wrap this in a stored procedure.  If you cannot do this than post your VB code.
0
 
itprochrisAuthor Commented:
Well I cannot put it into a SP because of limitations at the host, but here's the code:

Dim oConnection As New Data.SqlClient.SqlConnection("Data Source=*****;Initial Catalog=*****;Integrated Security=SSPI;")
        oConnection.Open()
        Dim sqlcmd As New Data.SqlClient.SqlCommand("INSERT INTO messages(callerID, messageContent, messageRead, userID) values(@CallerID, @Content, 0, @UserID)", oConnection)
        sqlcmd.Parameters.AddWithValue("@CallerID", callerID)
        sqlcmd.Parameters.AddWithValue("@MessageContent", message)
        sqlcmd.Parameters.AddWithValue("@UserID", userID)
        Dim affected As String
        affected = sqlcmd.ExecuteNonQuery()
        Return affected

... and I want to then return not the number of effected rows, but the ID of the message just added. Thanks for the help.
0
 
Anthony PerkinsCommented:
The stored procedure would look like something like this:

Create Procedure usp_AddMessage
                        @CallerID datatype,
                        @Content datatype,
                        @UserID datatype,
                        @MessageID integer OUTPUT

As

SET NOCOUNT ON

INSERT INTO messages(callerID, messageContent, messageRead, userID) values(@CallerID, @Content, 0, @UserID)

SET @MessageID = SCOPE_IDENTITY()
0
 
Anthony PerkinsCommented:
>>Well I cannot put it into a SP because of limitations at the host,<<
Fair enough, give me a minute.
0
 
Anthony PerkinsCommented:
You will have to make the following changes:

1. Change ExecuteNonQuery for ExecuteScalar
2. Change the select statement (see code)
3. You don't need affected.  It either contains 1 (successful) or 0 (failed)

Dim oConnection As New Data.SqlClient.SqlConnection("Data Source=*****;Initial Catalog=*****;Integrated Security=SSPI;")
oConnection.Open()
Dim SQL As String = "SET NOCOUNT ON;INSERT INTO messages(callerID, messageContent, messageRead, userID) values(@CallerID, @Content, 0, @UserID); Select Scope_Identity()"
Dim sqlcmd As New Data.SqlClient.SqlCommand(SQL, oConnection)
sqlcmd.Parameters.AddWithValue("@CallerID", callerID)
sqlcmd.Parameters.AddWithValue("@MessageContent", message)
sqlcmd.Parameters.AddWithValue("@UserID", userID)
'        Dim affected As String      
'        affected = sqlcmd.ExecuteNonQuery()
'        Return affected
Return sqlcmd.ExecuteNonQuery()
0
 
itprochrisAuthor Commented:
Thank you very much!!
0
 
Anthony PerkinsCommented:
And of course, this:
Return sqlcmd.ExecuteNonQuery()

Should have been:
Return sqlcmd.ExecuteScalar()
0
 
itprochrisAuthor Commented:
thanks!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now