infinitizon
MIS
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
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.
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.