data type mismatch vb net

Question

can you help me with this syntax? i have a table (datapenjualan) and i want to update data with this code, but always error in ExecuteQuery "data type is mismatch" here my database in access

Table Description

and here my code with datetime in primarykey 'Archive' to update data, 1 combobox to explain choose storage, 2 groupbox to ordner and lemari, and combobox again in groupbox and datetime

Private Sub save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click

    If storage.SelectedIndex = 1 Then
        str = "Update datapenjualan set BulanOrdner = '" & bulanordner.Text & "', TahunOrdner = '" & tahunordner.Text & "' Where Archive = '" & tanggalarchive.Text & "'"
        proses.ExecuteNonQuery(str)
        MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
        Call bersih()
    Else
        If storage.SelectedIndex = 2 Then
            str = "Update datapenjualan set Lemari = '" & lemari.Text & "', BulanLemari = '" & bulanlemari.Text & "', TahunLemari = '" & tahunlemari.Text & "'Where Archive = '" & tanggalarchive.Text & "'"
            proses.ExecuteNonQuery(str)
            MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
            Call bersih()
        End If
    End If
End Sub

Private Sub storage_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles storage.SelectedIndexChanged
    If storage.SelectedIndex = 1 Then
        groupordner.Visible = "True"
        grouplemari.Visible = "False"
    Else
        If storage.SelectedIndex = 2 Then
            groupordner.Visible = "False"
            grouplemari.Visible = "True"
        End If
    End If
End Sub

Your code has serious sql-injection vulnerability. Always use parameterized queries instead of catenated strings! That will most likely solve your original problem also, since it looks like some column in the database is not char/varchar as your code suggests? — Esko
the error will occur if any of your strings have more than 255 characters long, but your main problem is using tahunlemari.Text and tahunordner.Text instead of tahunlemari.Value and tahunordner.Value ms access is failing to convert that into a date time and Esko is correct, use oledb parameters on your query...you can watch the video below if you need some examples on how to do it > youtu.be/UtE3kkQR7_w?t=419 — LordKhutz
i've edit my code with tahunlemari.Value but it's still error with datatype mismatch :( cause i can input data in archive.value but can't update that value @KhuthadzoTshikotshi — Ajeng Shaffira A

Tetsuya Yamamoto Tetsuya Yamamoto · Accepted Answer · 2018-09-04T04:15:28

The main problem is you're passing date-formatted string to a column which has type of DateTime (remember that date/time values must always passed as either DateTime or TimeSpan depending on context). You should use parameters (with Parameters.Add or Parameters.AddWithValue) and convert any date-formatted strings to DateTime with Convert.ToDateTime() or better using DateTime.ParseExact() if you have specific date format in textbox:

Private Sub save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click

    If storage.SelectedIndex = 1 Then
        str = "Update datapenjualan set BulanOrdner = @BulanOrder, TahunOrdner = @TahunOrder Where Archive = @TanggalArchive"
        proses.Parameters.Add("@BulanOrder", OleDbType.VarChar).Value = bulanordner.Text
        proses.Parameters.Add("@TahunOrder", OleDbType.Date).Value = Convert.ToDateTime(tahunordner.Text) 
        ' or DateTime.ParseExact(tahunordner.Text, "[date_format_here]", CultureInfo.InvariantCulture)

        proses.Parameters.Add("@TanggalArchive", OleDbType.Date).Value = Convert.ToDateTime(tanggalarchive.Text) 
        ' or DateTime.ParseExact(tanggalarchive.Text, "[date_format_here]", CultureInfo.InvariantCulture)

        proses.ExecuteNonQuery(str)
        MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
        Call bersih()
    Else
        If storage.SelectedIndex = 2 Then
            str = "Update datapenjualan set Lemari = @Lemari, BulanLemari = @BulanLemari, TahunLemari = @TahunLemari Where Archive = @TanggalArchive"
            proses.Parameters.Add("@Lemari", OleDbType.VarChar).Value = lemari.Text
            proses.Parameters.Add("@BulanLemari", OleDbType.VarChar).Value = bulanlemari.Text
            proses.Parameters.Add("@TahunLemari", OleDbType.Date).Value = Convert.ToDateTime(tahunlemari.Text)
            ' or DateTime.ParseExact(tahunlemari.Text, "[date_format_here]", CultureInfo.InvariantCulture)

            proses.Parameters.Add("@TanggalArchive", OleDbType.Date).Value = Convert.ToDateTime(tanggalarchive.Text)
            ' or DateTime.ParseExact(tanggalarchive.Text, "[date_format_here]", CultureInfo.InvariantCulture)

            proses.ExecuteNonQuery(str)
            MsgBox("Data Has Been Saved", MessageBoxButtons.OK)
            Call bersih()
        End If
    End If
End Sub

Note that the parameter order should be in proper order (the leftmost parameter defined first, from left to right) to make query statement work.

data type mismatch vb net

1 Answers