mysql - VB.NET - 将日期插入 Mysql 数据库错误

Question

我试图将日期(仅)从dateTimePicker插入到mysql数据库，但发生错误

我使用的格式

DateTimePicker1.Value.Date.ToString("yyyy/MM/dd")

在我的 mysql 数据库中，我使用 DATE 数据类型

当我尝试在 vb 中显示日期时，它会显示

日期格式显示正确，yyyy/MM/dd，但我仍然无法将其插入到我的数据库中。

编辑:我的代码插入数据库

 Dim namaBarang, satuan, sql As String
        Dim idtoko = formStok.pIdToko
        Dim tglTransaksi = DateTimePicker1.Value.Date.ToString("yyyy/MM/dd")
        Dim qty, idBarang As Integer
        Dim noTrans = Label2.Text
        strcon.Open()
        For x As Integer = 0 To dataListTransaksi.Rows.Count - 1 Step 1
            namaBarang = dataListTransaksi.Rows(x).Cells(2).Value.ToString
            idBarang = dataListTransaksi.Rows(x).Cells(1).Value.ToString
            satuan = dataListTransaksi.Rows(x).Cells(3).Value.ToString
            qty = dataListTransaksi.Rows(x).Cells(4).Value.ToString

            sql = "BEGIN;
                    INSERT
            INTO
                    `tbl_transaksi`(
                    `tbl_transaksi`.`no_transaksi`,
                    `tbl_transaksi`.`tgl_transaksi`,
                    `tbl_transaksi`.`id_toko`
                    )
                VALUES(" & noTrans & ", " & tglTransaksi & ", " & idtoko & ");
                INSERT
            INTO
                    `tbl_transaksi_detail`(
                    `tbl_transaksi_detail`.`no_transaksi`,
                    `tbl_transaksi_detail`.`id_barang`,
                    `tbl_transaksi_detail`.`qty`,
                    `tbl_transaksi_detail`.`unit`
                    )
                VALUES(" & noTrans & ", " & idBarang & ", " & qty & ", '" & satuan & "');
                COMMIT;"

            Try

                'HOLDS THE DATA TO BE EXECUTED
                With cmd
                    .Connection = strcon
                    .CommandText = sql
                    'EXECUTE THE DATA
                    result = cmd.ExecuteNonQuery
                    'CHECKING IF THE DATA HAS EXECUTED OR NOT AND THEN THE POP UP MESSAGE WILL APPEAR
                    If result = 0 Then
                        MsgBox("FAILED TO SAVE THE DATA", MsgBoxStyle.Information)
                    Else
                        MsgBox("DATA HAS BEEN SAVE IN THE DATABASE")
                    End If
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        Next
        strcon.Close()

Answer 1

我忘记在日期值周围添加单引号

sql = "BEGIN;
                    INSERT
            INTO
                    `tbl_transaksi`(
                    `tbl_transaksi`.`no_transaksi`,
                    `tbl_transaksi`.`tgl_transaksi`,
                    `tbl_transaksi`.`id_toko`
                    )
                VALUES(" & noTrans & ", " & tglTransaksi & "<<==THE PROBLEM IS HERE, " & idtoko & ");
                INSERT
            INTO
                    `tbl_transaksi_detail`(
                    `tbl_transaksi_detail`.`no_transaksi`,
                    `tbl_transaksi_detail`.`id_barang`,
                    `tbl_transaksi_detail`.`qty`,
                    `tbl_transaksi_detail`.`unit`
                    )
                VALUES(" & noTrans & ", " & idBarang & ", " & qty & ", '" & satuan & "');
                COMMIT;"

所以我改变它

VALUES("& noTrans & ", '"& tglTransaksi & "', "& idtoko & ");

解决了!