Insert and select the just inserted id

[infinitizon]

Hi all,
I have this little challenge, I'm creating a mini helpdesk in my small site and want a functionality done.

I want to insert into the sql server and also retrieve the recently inserted value. I am currently tring something that seems to be working, however on the live web server where there may be concurrent users accessing the site simultaneously, I think I might have a challenge.

Here's my code

  Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
    Dim insertNewQuery As String = "INSERT INTO [IT Helpdesk Raised Tickets]"
    insertNewQuery &= "(UserId,[Contact No],[Job Title],[Asset ID],[Location],[Department],[Category],[Sub Category],[Item],[Subject]"
    insertNewQuery &= ",[Description],[Notify Email],[Service Category],[Attachments],[Date/Time Created])"
    insertNewQuery &= "VALUES"
    insertNewQuery &= "(@UserId,@ContactNo,@JobTitle,@AssetID,@Location,@Department,@Category,@SubCategory,@Item,@Subject"
    insertNewQuery &= ",@Description,@NotifyEmail,@ServiceCategory,@Attachments,@DateTimeCreated)"

    If insert_updateHelpdesk(insertNewQuery) Then
      Dim myda As SqlDataAdapter
      'create the dataset
      Dim ds As DataSet = New DataSet
      Dim mycn As New SqlConnection(RemWebTablesConnString)

      Dim TicketNo As String = "SELECT MAX([Ticket No])[Ticket No] FROM [IT Helpdesk Raised Tickets]"
      'TicketNo &= "WHERE [Date/Time Created] = '" & Session("thedate") & "'"
      myda = New SqlDataAdapter(TicketNo, mycn)
      myda.Fill(ds, "TicketNo")
      For Each row As DataRow In ds.Tables("TicketNo").Rows
        ShowAlertMessage("Your ticket No is \n\r#" & row("Ticket No").ToString() & "\n\rPlease use this for any reference with the IT dept")
      Next
      gdvMonitorTickets.DataBind()
    End If
  End Sub

  Protected Function insert_updateHelpdesk(ByRef theCommand As String, Optional ByRef type As String = "") As Boolean
    Dim connection As New SqlConnection(RemWebTablesConnString)

    Try
      Dim insertCmd As New SqlCommand(theCommand, connection)
      addParams4LeaveReq(insertCmd)
      Return True
    Catch ex As Exception
      ShowAlertMessage("Something went wrong. " & ex.Message)
      Return False
    End Try
  End Function

  Protected Sub addParams4LeaveReq(ByRef cmd As SqlCommand)
    ' Add the parameters.
    cmd.Parameters.AddWithValue("@UserId", User.Identity.Name)
    cmd.Parameters.AddWithValue("@ContactNo", Trim(txtContactNo.Text))
    cmd.Parameters.AddWithValue("@JobTitle", txtJobTitle.Text)
    cmd.Parameters.AddWithValue("@AssetID", txtAssetId.Text)
    cmd.Parameters.AddWithValue("@Location", txtLocation.Text)
    cmd.Parameters.AddWithValue("@Department", txtDept.Text)
    cmd.Parameters.AddWithValue("@Category", ddlCat.SelectedValue)
    cmd.Parameters.AddWithValue("@SubCategory", ddlSubCat.SelectedValue)
    cmd.Parameters.AddWithValue("@Item", txtItem.Text)
    cmd.Parameters.AddWithValue("@Subject", txtSubject.Text)
    cmd.Parameters.AddWithValue("@Description", txtDesc.Text)
    cmd.Parameters.AddWithValue("@NotifyEmail", txtEmail2Notify.Text)
    cmd.Parameters.AddWithValue("@ServiceCategory", ddlServiceCat.SelectedValue)

    Dim imageBytes(fupAttachment.PostedFile.InputStream.Length) As Byte
    fupAttachment.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
    cmd.Parameters.AddWithValue("@Attachments", imageBytes)

    Session("thedate") = Date.Now
    cmd.Parameters.AddWithValue("@DateTimeCreated", Session("thedate"))

    InsertUpdateData(cmd)
  End Sub

  Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

    Dim connection As New SqlConnection(RemWebTablesConnString)
    cmd.Connection = connection
    Dim added As Integer = 0
    Try
      connection.Open()
      added = cmd.ExecuteNonQuery()
      clearControls()
      Return True
    Catch ex As Exception
      ShowAlertMessage("Error transaction failed. " & ex.Message)
      Return False
    Finally
      connection.Close()
      connection.Dispose()
    End Try
  End Function

Is there a better way to do this?

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[whatsthehampton]

Yes, use stored procedures with SQL Server and SCOPE_IDENTITY

http://www.sql-server-performance.com/2003/stored-procedures-basics/

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Cheers,

Jeff