Pulling @@Identity Variable 1

[adamroof]

I have

strSQL = "DECLARE @Inserted_Key int INSERT INTO tblIncident (IncDesc, IncDate, StatusID, ClientID, ISStaffID) Values ('" & Replace(Problem.Text, "'", "''") & "', GetDate(),1," & strID & ",5) SET @Inserted_Key = @@Identity"

from samples and info that i found,
how can i get the @inserted_key into a local variable to use in a generated email?

.....
objConnection = New SqlConnection("Data Source=localhost;" _
& "Initial Catalog=mydb;integrated security=SSPI;" _
& "Connect Timeout=15;Network Library=dbmssocn;")

Dim objComm As New SqlCommand(strSQL)
objComm.Connection = objConnection
objConnection.Open()
objComm.ExecuteNonQuery()
objComm.Connection.Close()

Dim mailMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage
Dim message
mailMessage.From = "meme@meme.com"
mailMessage.To = "yoyo@yoyo.com"
mailMessage.CC = dgClients.Items(0).Cells(3).Text
mailMessage.Subject = "A Help Request Has Been Submitted"
mailMessage.BodyFormat = System.Web.Mail.MailFormat.Text

message = "Name: " & Employees.SelectedItem.Text & vbNewLine & vbNewLine _
& "Ext: " & dgClients.Items(0).Cells(0).Text & vbNewLine _
& "Location: " & dgClients.Items(0).Cells(1).Text & vbNewLine _
& "Computer: " & dgClients.Items(0).Cells(2).Text & vbNewLine & vbNewLine _
& "Problem: " & Problem.Text & vbNewLine & vbNewLine _
& "Ticket #: " & newIncNum
& "From IP: " & Request.ServerVariables("REMOTE_HOST")
mailmessage.body = message

System.Web.Mail.SmtpMail.SmtpServer = "mymailserver"
System.Web.Mail.SmtpMail.Send(mailMessage)

 

[SQLSister]

I would suggest you post this in the Vb or vba forum. This is not a SQL question. However, you should not use @@identity to pick up the identity value, use the scope_identity() function instead. @@identity will give you the wrong answer if your table has a trigger on it which inserts into another table.

 

[adamroof]

Then it was a sql question

 

[octothorpe]

strSQL = "INSERT INTO tblIncident (IncDesc, IncDate, StatusID, ClientID, ISStaffID) Values ('" & Replace(Problem.Text, "'", "''") & "', GetDate(),1," & strID & ",5) SELECT scope_identity()"

Use the ExecuteScalar method instead of ExecuteNonQuery. Cast the result of the ExecuteScalar method into a number (don't recall the VB syntax) and you have your identity.

 

[adamroof]

GOT IT!!!
Thanks dragon and veep!!!!

*******
Sub Button1_Click(sender As Object, e As EventArgs)
Dim strSQL, strID As String
Dim objConnection As SqlConnection

strID = dgClients.Items(0).Cells(4).Text
strSQL = "INSERT INTO tblIncident (IncDesc, IncDate, StatusID, ClientID, ISStaffID) Values ('" & Replace(Problem.Text, "'", "''") & "', GetDate(),1," & strID & ",5); SELECT @@Identity as 'inserted_Key'"

objConnection = New SqlConnection("Data Source=localhost;" _
& "Initial Catalog=helpdesk;integrated security=SSPI;" _
& "Connect Timeout=15;Network Library=dbmssocn;")

Dim insertedKey as Int32
Dim objComm As New SqlCommand(strSQL)
objComm.Connection = objConnection
Try
objConnection.Open()
insertedKey = Ctype(objComm.ExecuteScalar(), int32)
Finally
objComm.Connection.Close()
End Try

Dim mailMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage
Dim message
mailMessage.From = "IntranetUser@me.com"
mailMessage.To = "adam.roof@me.com"
'mailMessage.CC = dgClients.Items(0).Cells(3).Text
mailMessage.Subject = "A Help Request Has Been Submitted"
mailMessage.BodyFormat = System.Web.Mail.MailFormat.Text

message = "Name: " & Employees.SelectedItem.Text & vbNewLine & vbNewLine _
& "Ext: " & dgClients.Items(0).Cells(0).Text & vbNewLine _
& "Location: " & dgClients.Items(0).Cells(1).Text & vbNewLine _
& "Computer: " & dgClients.Items(0).Cells(2).Text & vbNewLine & vbNewLine _
& "Problem: " & Problem.Text & vbNewLine & vbNewLine _
& "Ticket #: " & insertedKey & vbNewLine _
& "From IP: " & Request.ServerVariables("REMOTE_HOST")
mailmessage.body = message

System.Web.Mail.SmtpMail.SmtpServer = "192.168.5.8"
System.Web.Mail.SmtpMail.Send(mailMessage)

Dim strjs as string = "<script language=""javascript"">"
strjs &= "window.alert('A Help Request Has Been Submitted');window.close()"
strjs &= "</script" & ">"
Literal1.Text = strjs
End Sub