Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert and select the just inserted id

Status
Not open for further replies.
Jul 28, 2011
167
NG
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

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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top