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

The Screen Shot got lost somewhere? So we cannot see what your error message is? — RBarryYoung
– RBarryYoung, Commented Jul 13, 2012 at 18:18
Stab in the dark, make sure your column names are correct. Maybe SNS is SSN (social security number)? Also, learn to use parameters. Your query is vulnerable to sql injection. — LarsTech
– LarsTech, Commented Jul 13, 2012 at 18:22

Community · Accepted Answer · 2017-05-23 11:48:13Z

3

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

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Jul 13, 2012 at 18:26

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sree Vardhan R Over a year ago

i have re edited the question according to your advice and also added the link for the Images .. hope this will helpful to explain my question

Fionnuala Over a year ago

I have tested an outline of your code, and

FName = "abc" : ADate = Now : sql = "insert into Table1 (AText, ADate) values ('" & FName & "',#" & ADate & "#)" : MsgBox(sql)

works for me, so I think you should check what you are getting from the textbox. However, there is really no need for you to use a textbox for the save date, because it is today, so "insert into Table1 (AText, ADate) values ('" & FName & "',Now())" should suit.

Fionnuala Over a year ago

I should stress again that you will avoid an amazing number of problems with parameters. As it stands, if any of your textboxes contain a single quote ('), the query will fail.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related