VB.NET updating .accdb records

Question

I'm working on a VB.Net application that interfaces with an .accdb file to create and (eventually) update records on two tables in the same database. I'm able to write new information to a table no problem, however it is updating/changing/adding additional information to that same row in the table I'm having issues with. My code for writing updates to an existing row is at the bottom of my post.

The biggest issue I'm having is, after I execute this subroutine, it fails at the objCmd.ExecuteNonQuery() with the error message IErrorInfo.GetDescription failed with E_FAIL(0x80004005). I've combed through here and Google, trying different methods and moving things around and I cannot figure out what I'm missing. As far as I can tell, I am not using any reserved words in my SQL query. The block under the Else statement does work for creating new rows (I don't have issues with that side of my program), maybe the syntax is different for doing UPDATE commands? Any help/insight is greatly appreciated.

Private Sub WriteToDatabase()
    strcs = txtSerialNumber.Text
    strOrderType = orderType
    strPoRMA = txtPoRMA.Text
    strtech = cboTech.Text
    strDate = calendarTest.SelectionStart

    'Write to database if Production
    If strOrderType = "PO" Then
        'Check database for duplicate record
        strSQL = "SELECT * FROM [New Camera Database] WHERE cameraSer=" & strcs
        objCmd = New OleDbCommand(strSQL, dbconn)
        dr = objCmd.ExecuteReader
        dr.Read()

        If dr("calCompleteDate").ToString <> "" Then
            MsgBox("Camera S/N " & strcs & " completed " & dr("calCompleteDate") & ".  Use Lookup to reprint Cert. of Compliance", vbOK + vbExclamation,
               "Camera S/N " & strcs & " already completed")
            exitFlag = True
        Else
            'Write to New Camera Database Table
            strSQL = "UPDATE [New Camera Database] SET poNum=@poNum , calCompleteDate=@calCompleteDate, calCompleteTech=@calCompleteTech WHERE cameraSer=" & strcs
            objCmd = New OleDbCommand(strSQL, dbconn)
            objCmd.Parameters.AddWithValue("@poNum", strPoRMA)
            objCmd.Parameters.AddWithValue("@calCompleteDate", strcs)
            objCmd.Parameters.AddWithValue("@calCompleteTech", strtech)
            objCmd.ExecuteNonQuery()

            'Write to up2DateTravelers Table
            strSQL = "UPDATE up2DateTravelers SET poRMANum = @poRMANum, calCompleteDate = @calCompleteDate, calCompleteTech = @calCompleteTech WHERE cameraSer=" & strcs
            objCmd = New OleDbCommand(strSQL, dbconn)
            objCmd.Parameters.AddWithValue("@poRMANum", strPoRMA)
            objCmd.Parameters.AddWithValue("@calCompleteDate", strcs)
            objCmd.Parameters.AddWithValue("@calCompleteTech", strtech)
            objCmd.ExecuteNonQuery()
        End If
    ElseIf strOrderType = "RMA" Then
        'Create new functions, userform, etc (TBD)
    End If
    btnClear.PerformClick()
End Sub

Not sure why the block of code under the Else is looking like it is commented (red), as it is not in my program. Same with the outside End If and the btnClear.PerformClick() — Alex, Aug 30 '16 at 21:50
It looks like you are taking `strcs` which is the value of `txtSerialNumber.Text` and trying to use it as a date (`calCompleteDate`) in your UPDATE statements. Also, as @JonathanAllen says, you shouldn't be using *dynamic SQL*; you should use `... WHERE cameraSer = @oneMoreParameter`. — Gord Thompson, Aug 30 '16 at 22:33
Also remember that for an OleDbCommand, parameters are *strictly positional*. You can give them names like `@poRMANum`, but System.Data.OleDb *ignores* those names and only pays attention to the order in which the parameter placeholders appear in the CommandText. — Gord Thompson, Aug 30 '16 at 22:37
@GordThompson, I'm trying to tell the program to update the database line where the `CameraSer` is `strcs`, as this information is previously filled out in another section of my program. How could I update my SQL UPDATE statement to include one more parameter? The user specifies the `strcs` value via my program, it isn't static. — Alex, Aug 30 '16 at 22:40
You can put a parameter in the WHERE clause just like you put them in the SET clause. The SET values aren't (necessarily) static, so it's not a problem if the WHERE value isn't, either. — Gord Thompson, Aug 31 '16 at 01:54
Does it fail at the first or second `ExecuteNonQuery` ? Also, Use [Using](http://stackoverflow.com/documentation/vb.net/3204/disposable-objects/10981/basic-concept-of-idisposable#t=201608310845377332083) — Martin Verjans, Aug 31 '16 at 08:45
@SuperPeanut it fails on the first `ExecuteNonQuery`. Your observation below, as well as @Gord Thompson's, fixed it. Thank you both! — Alex, Aug 31 '16 at 14:39

Martin Verjans · Accepted Answer · 2017-09-22T16:08:24.930

I guess this line :

objCmd.Parameters.AddWithValue("@calCompleteDate", strcs)

is a mistake and that you wanted to use the Date :

objCmd.Parameters.AddWithValue("@calCompleteDate", strDate)

Also, Use Using and parametrized queries :

'Write to New Camera Database Table
strSQL = "UPDATE [New Camera Database] SET poNum=@poNum , calCompleteDate=@calCompleteDate, calCompleteTech=@calCompleteTech WHERE cameraSer=@cameraSer"
Using objCmd As New OleDbCommand(strSQL, dbconn)
    objCmd.Parameters.AddWithValue("@poNum", strPoRMA)
    objCmd.Parameters.AddWithValue("@calCompleteDate", strDate)
    objCmd.Parameters.AddWithValue("@calCompleteTech", strtech)
    objCmd.Parameters.AddWithValue("@cameraSer", strcs)
    objCmd.ExecuteNonQuery()
End Using

'Write to up2DateTravelers Table
strSQL = "UPDATE up2DateTravelers SET poRMANum = @poRMANum, calCompleteDate = @calCompleteDate, calCompleteTech = @calCompleteTech WHERE cameraSer=@cameraSer"
Using objCmd As New OleDbCommand(strSQL, dbconn)
    objCmd.Parameters.AddWithValue("@poRMANum", strPoRMA)
    objCmd.Parameters.AddWithValue("@calCompleteDate", strDate)
    objCmd.Parameters.AddWithValue("@calCompleteTech", strtech)
    objCmd.Parameters.AddWithValue("@cameraSer", strcs)
    objCmd.ExecuteNonQuery()
End Using

Oh jeez, i can't believe i didn't see that i was using the wrong variable that both you and @Gord Thompson mentioned! I'll swap that out once I get into work and see if that fixes my issue. — Alex, Aug 31 '16 at 13:22
that did it. It finally wrote successfully. What a silly mistake. Question regarding the Using function, what makes it a better practice than just calling all of the parameters? This is the first time I've seen that function. — Alex, Aug 31 '16 at 14:36
Using blocks and Parametrized queries are two different things. But it is usually better to use them. Reason to use [Using](http://stackoverflow.com/a/75483/5897829). Reason to use parametrized queries : see the link in my answer. — Martin Verjans, Aug 31 '16 at 15:43

VB.NET updating .accdb records

1 Answers1