CODEWITHSUNDEEP
vb.netms-access
Alex
Alex

Reputation: 83

VB.NET updating .accdb records

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

Upvotes: 2

Views: 707

Answers (1)

Martin Verjans
Martin Verjans

Reputation: 4796

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

Upvotes: 2

Related Questions