INSERT query is executing but not inserting

Question

I have the following subroutine in my code for inserting a new record into a SQL Server database. After stepping through it, the code goes through it all, the message box appears, but the row is not actually inserted into the database. I know it's using the right connection as I can view records on the related grid that I manually inserted using SQL Server, and also my UPDATE and DELETE queries work, using the same connection, so something is wrong with my code but I can't work out what... Can anybody help me?

 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

        Dim ID As Integer
        ID = "1"

        Dc.CommandType = CommandType.Text
        Dc.CommandText = "INSERT INTO dbo.tblIncidents VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
        Dc.Transaction = tr
        Dc.Parameters.Add("@supportID", OleDbType.Integer).Value = ID
        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

        tr.Commit()

        MsgBox("Save successful")

    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

I think you need to call Dc.ExecuteNonQuery() before tr.Commit() — user5226582
– user5226582, Commented Aug 2, 2016 at 8:42
@user5226582 Yes, thank you, of course... Just something stupid — David
– David, Commented Aug 2, 2016 at 8:46

Akshey Bhat · Accepted Answer · 2016-08-02 08:47:20Z

2

you haven't executed your insert query. add the below code before tr.Commit()

Dc.ExecuteNonQuery()

answered Aug 2, 2016 at 8:47

Akshey Bhat

8,6872 gold badges24 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Over a year ago

Thanks Akshey, this has worked. One thing I'd like to change is that in this code, supportID is always entering as 1... How can I adapt the code to find the highest ID number in the database, add 1 to it, and insert this number?

Akshey Bhat Over a year ago

convert support-id column to an identity column. that way you will always get the required value.

David Over a year ago

I had this originally, but there is a point in the program where I needed it off as I had to insert explicit values

Akshey Bhat Over a year ago

when you want to insert explicit values into identity column you can enable identity column insertion for that table

Collectives™ on Stack Overflow

INSERT query is executing but not inserting

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related