Alex Alex - 3 months ago 17
Vb.net Question

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

Answer

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
Comments