joe joe - 4 months ago 10
Vb.net Question

SQL query entering identical records

I have a SQL insert query in my project, and it works fine, in fact, perhaps a little too well, as it saves the data I entered, but saves it twice, each with two different ID autonumbers. Can anybody work out why?

// Save button code

Private Sub buttonSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim incidentSolved As Boolean = False

If cboxSolved.Checked Then
incidentSolved = True
End If

If txtClientSave.Text = "" Then
MsgBox("Client name cannot be blank")

ElseIf rtbProblem.Text = "" Then
MsgBox("Problem cannot be blank")

ElseIf cboxSolved.Checked = True And rtbSolution.Text = "" Then
MsgBox("Please enter solution")

Else
database.SaveNewIncident(txtClientSave.Text, dtpStart.Value, dtpEnd.Value, rtbProblem.Text, dtpStartTime.Value, dtpEndTime.Value, cboxSolved.Checked, rtbSolution.Text, _con)

MsgBox("Save successful")

txtClientSave.Text = ""
rtbProblem.Text = ""
rtbSolution.Text = ""
dtpStart.Value = Date.Today
dtpEnd.Value = Date.Today
dtpStartTime.Value = DateTime.Now
dtpEndTime.Value = DateTime.Now
cboxSolved.Checked = False

End If

loadIncidents()

End Sub


// Save SQL

Public Shared Function SaveNewIncident(ByVal clientName As String, dateStart As Date, dateEnd As Date, ByVal incidentProblem As String, ByVal timeStart As String, ByVal timeEnd As String, ByVal incidentSolved As Boolean, ByVal incidentSolution As String, _Con As OleDbConnection)

Dim tr As OleDbTransaction = Nothing

Try
tr = _Con.BeginTransaction()

Dim Dc As New OleDbCommand
Dc.Connection = _Con

Dc.CommandType = CommandType.Text
Dc.CommandText = "INSERT INTO dbo.tblIncidents VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
Dc.Transaction = tr
Dc.Parameters.Add("@clientName", OleDbType.VarChar).Value = clientName
Dc.Parameters.Add("@dateStart", OleDbType.Date).Value = dateStart
Dc.Parameters.Add("@dateEnd", OleDbType.Date).Value = dateEnd
Dc.Parameters.Add("@incidentProblem", OleDbType.LongVarChar).Value = incidentProblem
Dc.Parameters.Add("@timeStart", OleDbType.VarChar).Value = timeStart
Dc.Parameters.Add("@timeEnd", OleDbType.VarChar).Value = timeEnd
Dc.Parameters.Add("@incidentSolved", OleDbType.Boolean).Value = incidentSolved
Dc.Parameters.Add("@incidentSolution", OleDbType.LongVarChar).Value = incidentSolution
Dc.Parameters.Add("@SSMA_TimeStamp", OleDbType.DBTimeStamp)

Dim supportID As Integer
Dim Ds As New OleDbCommand

Ds.CommandType = CommandType.Text
Ds.CommandText = "SELECT SCOPE_IDENTITY() AS supportID"
Ds.Transaction = tr

supportID = CType(Dc.ExecuteScalar(), Integer)
Dc.ExecuteNonQuery()

tr.Commit()

Catch ex As Exception

mdInit.errorLog(ex.Message, ex.StackTrace)
MsgBox("Failed to save data, refer to error log")
tr.Rollback()

End Try

End Function

Answer
supportID = CType(Dc.ExecuteScalar(), Integer)
Dc.ExecuteNonQuery()   'you are doing it a second time here

there it is, you are executing it twice

comment out your second one

     supportID = CType(Dc.ExecuteScalar(), Integer)
    'Dc.ExecuteNonQuery()

the problem is in SaveNewIncident, you execute a scalar and get the supportId generated, then you execute the whole thing again as a nonquery - both are valid ways of executing your commands, but both the INSERT and SELECT commands are executed both times (in the second execute, ExecuteNonQuery, you don't pick up the value selected)


how about

Public Shared Function SaveNewIncident(ByVal clientName As String, dateStart As Date, dateEnd As Date, ByVal incidentProblem As String, ByVal timeStart As String, ByVal timeEnd As String,
                                   ByVal incidentSolved As Boolean, ByVal incidentSolution As String, _Con As OleDbConnection)

Dim tr As OleDbTransaction = Nothing

Try
    tr = _Con.BeginTransaction()

    Dim Dc As New OleDbCommand
    Dc.Connection = _Con

    Dc.CommandType = CommandType.Text
    Dc.CommandText = "INSERT INTO dbo.tblIncidents VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
    Dc.Transaction = tr
    Dc.Parameters.Add("@clientName", OleDbType.VarChar).Value = clientName
    Dc.Parameters.Add("@dateStart", OleDbType.Date).Value = dateStart
    Dc.Parameters.Add("@dateEnd", OleDbType.Date).Value = dateEnd
    Dc.Parameters.Add("@incidentProblem", OleDbType.LongVarChar).Value = incidentProblem
    Dc.Parameters.Add("@timeStart", OleDbType.VarChar).Value = timeStart
    Dc.Parameters.Add("@timeEnd", OleDbType.VarChar).Value = timeEnd
    Dc.Parameters.Add("@incidentSolved", OleDbType.Boolean).Value = incidentSolved
    Dc.Parameters.Add("@incidentSolution", OleDbType.LongVarChar).Value = incidentSolution
    Dc.Parameters.Add("@SSMA_TimeStamp", OleDbType.DBTimeStamp)

    Dim supportID As Integer
    Dc.CommandText += ";SELECT SCOPE_IDENTITY() AS supportID"

    supportID = CType(Dc.ExecuteScalar(), Integer)

    tr.Commit()

Catch ex As Exception

    mdInit.errorLog(ex.Message, ex.StackTrace)
    MsgBox("Failed to save data, refer to error log")
    tr.Rollback()

End Try

End Function

there is also a mix up between ds and dc