I wanted to change the date format in the textbox to the format "dd-mmm-yy" but it did not work when in datagridview did not appear the date with the time and also I could not update in the textbox with blank and zero which caused the error "data type error". if I do not perform sql command for update in the "DATE" field then do not cause error. I use visual studio 2010 so the problem in the date column and if I update without a date column then it runs perfectly. Problem error : syntax error in update statement thanks jack

  Dim Path As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
    Dim cn As String = "provider=Microsoft.Jet.OLEDB.4.0; data source=" & Path & "; Extended Properties=dBase IV"
    Private connectionString As String
    Private con As OleDbConnection
    Private cmd As OleDbCommand
    Private da As OleDbDataAdapter
    Private sql As String
    Public x As Integer
    Public Sub dbConnection()
        connectionString = CStr(cn)
        con = New OleDbConnection(connectionString)
        con.Open()
    End Sub
    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        x = DataGridView1.Rows.IndexOf(DataGridView1.CurrentRow)
        txtCODE.Text = DataGridView1.Rows(x).Cells(0).Value.ToString()
        DateTimePicker1.Value = DataGridView1.Rows(x).Cells(1).Value.ToString()
NumericUpDown1.Value = DataGridView1.Rows(x).Cells(2).Value.ToString()
       
    End Sub
    Private Sub FillDataGridView()
        Try
            Dim query = "select CODE,DTE,QTY FROM EXAMPLE"
            Using con As OleDbConnection = New OleDbConnection(CStr(cn))
                Using cmd As OleDbCommand = New OleDbCommand(query, con)
                    Using da As New OleDbDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        da.Fill(dt)
                        Me.DataGridView1.DataSource = dt
                    End Using
                End Using
            End Using
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            dbConnection()
            x = DataGridView1.Rows.IndexOf(DataGridView1.CurrentRow)
            'sql = "UPDATE EXAMPLE SET QTY=? WHERE CODE=?"
            sql = "UPDATE EXAMPLE SET DTE=?,QTY=? WHERE CODE=?"
            cmd = New OleDbCommand(sql, con)
            cmd.Parameters.Add("DTE", OleDbType.Date)
            cmd.Parameters.Add("QTY", OleDbType.Numeric)
            cmd.Parameters.Add("CODE", OleDbType.VarChar)
            cmd.Parameters("DTE").Value = DateTimePicker1.Value
            cmd.Parameters("QTY").Value = NumericUpDown1.Value
            cmd.Parameters("CODE").Value = txtCODE.Text

            cmd.ExecuteNonQuery()
            MessageBox.Show("Successfully Updated...", "Update")
            con.Close()
            FillDataGridView()
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillDataGridView()
    End Sub

CodePudding user response：

Your SQL line must be written like:

sql = "UPDATE EXAMPLE SET DATE=@d,QTY=@q WHERE CODE=@c"

You set the values of these @xxx by adding more code:

cmd.Parameters.AddWithValue("@d", dtpDATE.Value)
cmd.Parameters.AddWithValue("@q", nudQTY.Value)
cmd.Parameters.AddWithValue("@c", txtCODE.Text)

Change your date time TextBox to be a datetimepicker instead. Instead of using textboxes for numeric values, use a NumericUpDown instead.

You must call AddWithValue("@xxx"... in the same order as the @xxx appear in the SQL. Access ignores the names and looks at the order Parameters appear in the SQL so it is important to call AWV on the same order. One day when you upgrade o using a different database it will support the name part of the parameter so you can add your values in any order but right now on access, order is important

You must write your SQL like I've shown. Never, ever do what you were doing before. Never use string concatenation to build a user-supplied data value into an SQL statement. It makes your program trivial to hack into. This might not matter while you're writing a tiny app to index grandma's record collection but it must never be done on any system of importance such as one used by many people