(VB.NET)Syntax Error in INSERT INTO statement - MICROSOFT JET DATABASE ENGINE

Question

I need help, I developed a simple Access database and I create a VB .Net user interface. I can't INSERT data into the Table here's my sample code... I'm only a beginner in VB.Net i hope you could help me with this one

Dim con As New OleDbConnection
    Dim cmd As New OleDbCommand
    Dim da As New OleDbDataAdapter
    Dim ds As New DataSet
    Dim dr As DataRow


    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Documents and Settings\Test\Desktop\Privilege.Net\PrivilegeCard\Database\dbPrivilegeSystem.mdb"

    cmd.CommandText = "Select * From tblUsers;"

    Try
        con.Open()
        cmd.Connection = con

        cmd.CommandType = CommandType.Text

        da.SelectCommand = cmd
        da.Fill(ds, "tblUsers")

        dr = ds.Tables("tblUsers").NewRow


        'System.Convert.ToDateTime(dtPicker.Value).ToOADate()
        'I've been trying to use this conversion for date thinking if this could help me fix my problem

        dr(0) = txtUserN.Text
        dr(1) = txtPass.Text
        dr(2) = txtAccess.Text
        dr(3) = dtPicker.Value
        dr(4) = txtName.Text
        dr(5) = txtPos.Text
        dr(6) = cmbDept.Text

        ds.Tables("tblUsers").Rows().Add(dr)

        Dim cb As New OleDbCommandBuilder(da)

        da.Fill(ds)
        da.Update(ds, "tblUsers")

    Catch ex As OleDbException

        MessageBox.Show(ex.Message & " - " & ex.Source)
    End Try

Answer 1

hi,

take a look at my code ,it is easer

Imports System.Data
Partial Class theClassName
    Inherits System.Web.UI.Page
Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=" & MapPath("~/App_Data") & "/database.mdb"
        Dim dbconnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
        Dim queryString As String = "Insert into [customer] ([ID],[name],[address],[phone],[date_of_birth],[Nationality],[room_no],[number_of_days]) Values (" & txtid.Text & ", '" & txtname.Text & "', '" & txtaddress.Text & "', " & txtphone.Text & ", " & txtdate.Text & ", '" & txtnation.Text & "'," & txtroom.Text & "," & txtday.Text & ")"
        Dim dbcommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbcommand.CommandText = queryString
        dbcommand.Connection = dbconnection
        dbconnection.Open()
        Dim rowsAffected As Integer = 0

        Try
            rowsAffected = dbcommand.ExecuteNonQuery
            result.Text = "Record Saved"
        Catch
            result.Text = "Record not saved"
        Finally
            dbconnection.Close()
        End Try

           End Sub
 End Class