[britslindy]

Hi!

I am using Visual Basic to control a Windows Access database. I have succeeded in adding a record to the database, it works fine. But now I am struggling to update the database!

I get the "Data type mismatch in criteria expression" error on the 'ERROR' line. On the line before, "7" is just another column name in the database.

Here is my code for a certain button :

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        Dim sql As String

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:/Users/Charles/My Documents/Hluhluwe Database/HluhluweConstruction.mdb"
        con.ConnectionString = dbProvider & dbSource

        con.Open()
        sql = "SELECT * FROM Faults"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Faults")
        Dim maxRows As Integer
        maxRows = ds.Tables("Faults").Rows.Count - 1
        Dim date1 As Date = dtp_date1.Value
        Dim mach As String = cb_machines.Text
        Dim forem As String = cb_foremen.Text
        Dim op As String = cb_operators.Text
        Dim isCreated As Boolean = False
        Dim currentRow As Integer
        For count = 0 To maxRows
            If ds.Tables("Faults").Rows(count).Item(4) = date1.Date() And ds.Tables("Faults").Rows(count).Item(3) = op And ds.Tables("Faults").Rows(count).Item(2) = forem And ds.Tables("Faults").Rows(count).Item(1) = mach Then
                isCreated = True
                currentRow = count
            End If
        Next
        If isCreated = False Then
            MsgBox("Click on Add Record Button")
        Else
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            ds.Tables("Faults").Rows(currentRow).Item("7") = ff7.Text
            da.Update(ds, "Faults") 'ERROR'
        End If
        con.Close()
    End Sub

I will appreciate any help! Thank you!

[britslindy]

Resolved! The following code was used instead :

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        Dim sql As String

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:/Users/Charles/My Documents/Hluhluwe Database/HluhluweConstruction.mdb"
        con.ConnectionString = dbProvider & dbSource

        con.Open()
        sql = "SELECT * FROM Faults"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Faults")
        con.Close()

        Dim maxRows As Integer
        maxRows = ds.Tables("Faults").Rows.Count - 1
        Dim date1 As Date = dtp_date1.Value
        Dim mach As String = cb_machines.Text
        Dim forem As String = cb_foremen.Text
        Dim op As String = cb_operators.Text
        Dim isCreated As Boolean = False
        Dim currentRow As Integer
        For count = 0 To maxRows
            If ds.Tables("Faults").Rows(count).Item(4) = date1.Date() And ds.Tables("Faults").Rows(count).Item(3) = op And ds.Tables("Faults").Rows(count).Item(2) = forem And ds.Tables("Faults").Rows(count).Item(1) = mach Then
                isCreated = True
                currentRow = count
            End If
        Next
        If isCreated = False Then
            MsgBox("Click on Add Record Button")
        Else
            Dim con2 As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:/Users/Charles/My Documents/Hluhluwe Database/HluhluweConstruction.mdb")
            Dim OLECmd As New OleDb.OleDbCommand
            Dim OLEStr As String
            OLEStr = "UPDATE Faults SET"
            OLEStr += " 7 = '" & ff7.Text & "'"
            OLECmd = New OleDb.OleDbCommand(OLEStr, con2)
            con2.Open()
            OLECmd.ExecuteNonQuery()
            con2.Close()
        End If
    End Sub

[BobRodes]

I generally prefer to use the SQL commands rather than the Update method, as it's easier to troubleshoot. However, you should be aware of injection attacks. Consider using stored procedures.