Hi all,

I have a very annoying problem with adding new records to my Access Database, I'm not getting any syntax errors but there's obviously a symantic error of some kind causing this headache. I've been developing an RFID system for two months now and spent the last four weeks trying to fix this one issue, if anyone can see where i'm going wrong i'd be very gratful for your help, Thanks for your time.

Private Sub m_btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles m_btnSave.Click

        Dim da As New OleDb.OleDbDataAdapter
        Dim cb As New OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim sql As String
        Dim inc As Integer = 0

        dbProvider = "Provider = Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source = C:\myProjects\jmCarPark1\jmCarPark1\JMdataBase.accdb;Persist Security Info = True;"

        con.ConnectionString = dbProvider & dbSource

        con.Open()

        sql = "SELECT * FROM Table1"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "JMdataBase")

        Try
                dsNewRow = ds.Tables("JMdataBase").NewRow()

                sql = "INSERT INTO [Table1] (Tag_ID, fName, sName, Address, Contact, car_Make, car_Model, car_Reg) VALUES (@Tag_ID, @fName, @sName, @Address, @Contact, @car_Make, @car_Model, @car_Reg)"

                dsNewRow.Item("Tag_ID") = Tag_IDTextBox.Text
                dsNewRow.Item("fName") = FNameTextBox.Text
                dsNewRow.Item("sName") = SNameTextBox.Text
                dsNewRow.Item("Address") = AddressTextBox.Text
                dsNewRow.Item("Contact") = ContactTextBox.Text
                dsNewRow.Item("car_Make") = Car_MakeTextBox.Text
                dsNewRow.Item("car_Model") = Car_ModelTextBox.Text
                dsNewRow.Item("car_Reg") = Car_RegTextBox.Text

                ds.Tables("JMdataBase").Rows.Add(dsNewRow)

                dsNewRow.AcceptChanges()
                dsNewRow.BeginEdit()
                da.Update(ds, "JMdataBase")
                dsNewRow.EndEdit()

                MsgBox("New client details have been added")

                con.Close()

        Catch ex As System.Exception
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)

        Finally
            Windows.Forms.Cursor.Current = Cursors.Default

        End Try

    End Sub

If anyone has questions or anything, don't hesitate to ask.

Hi
You've defined your SQL insert statement but I don't see you doing anything with it.
For insertting records to a database I just do something like this as I find it easier: (actually I'd use a SQL Database with a stored procedure as it is more secure and SQL express is free)

sub SaveMyRecord ()
dim sql as string

sql = "INSERT INTO [Table1] (Tag_ID, fName, sName, Address, Contact, car_Make, car_Model, car_Reg) VALUES ( '" &Tag_IDTextBox.Text &"', '" &FNameTextBox.Text &"', '" &SNameTextBox.Text &"', '" &AddressTextBox.Text &"', '" &ContactTextBox.Text &"', '" &Car_MakeTextBox.Text &"', '" &Car_ModelTextBox.Text &"', '" &Car_RegTextBox.Text &"')"

dbProvider = "Provider = Microsoft.ACE.OLEDB.12.0;"        
dbSource = "Data Source = C:\myProjects\jmCarPark1\jmCarPark1\JMdataBase.accdb;Persist Security Info = True;"         
con.ConnectionString = dbProvider & dbSource         
con.Open()
dim cmd as OleDBCommand
with cmd
  .Connection = con
  .text = sql
end sub
cmd.ExecuteNonQuery()

Thank you very much G_Waddell, that's a very elegant solution to my problem and it works perfectly.


Gary

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.