How to Insert Rows to MS Access 2003 Table in VB.net using Oledb Adapter / Command?

Question

I have created a Form for Managing Contacts such as PhoneBook so i have created nearly 12 fields in MS Access 2003 including ID as a Primary Key(Auto Number) the prob is while inserting the Data's (got from the user) into the Table.

Imports System.Data.OleDb
Public Class FrmMain

    Private Sub FrmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connection_open()
    End Sub
    Dim adp As OleDbDataAdapter
    Dim dt As DataTable



    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
        Dim sql As String

        sql = "insert into TblContacts (FName, Company, Contact_1, Contact_2, Address, Email_1, Email_2, Web, SNS, Date_Saved, Cont_Image) values ('" & TxtFName.Text & "' ,'" & TxtCompany.Text & "','" & TxtCont_1.Text & "','" & TxtCont_2.Text & "','" & TxtAddress.Text & "','" & TxtEmail_1.Text & "','" & TxtEmail_2.Text & "','" & TxtWeb.Text & "','" & TxtSNS.Text & "',#" & TxtDate.Text & "#,'" & TextBox10.Text & "')"

        Try
            adp = New OleDbDataAdapter
            adp.InsertCommand = New OleDbCommand(sql, con)
            adp.InsertCommand.ExecuteNonQuery()


            MsgBox("Saved Successfully!")

        Catch ex As Exception

            MsgBox(ex.ToString)

        End Try
    End Sub

    Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
        Try
            dt = New DataTable
            adp = New OleDbDataAdapter("Select * from TblContacts", con)
            adp.Fill(dt)

            DataGridView1.DataSource = dt


        Catch ex As Exception

            MsgBox(ex.Message)

        End Try
    End Sub


    'Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
    '    ToolStripTextBox2.Text = Now
    'End Sub

End Class

**Connection Module:**

Imports System.Data.OleDb

Module connection_module

    Public con As New OleDbConnection
    Public Sub connection_open()
        Try
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Contacts.mdb"
            con.Open()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try
    End Sub
End Module

https://picasaweb.google.com/105381696083885067883/VBNet?authuser=0&authkey=Gv1sRgCPvqiqCrjqCAfQ&feat=directlink

Answer 1

Name and image are reserved words in MS Access and need to be enclosed in brackets [name]

Is Date_Saved really text? If not the delimiters are #, not '

And, or course, @Lars point.

Finally, you might like to consider parameters or Passing parameter to query for Access database