Hi, Im trying to add a code which adds a record for me and then saves it. I am also trying to add a code which deletes the record for me.
So far, my "Add" button, clears the form, which is what I want. Then when I click on save, I get a error. I also get the same error when i Click Delete. This is my code for my "Save" button:

If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsnewrow As DataRow

dsnewrow = ds.Tables("SuperTronicsWorld").NewRow()
dsnewrow.Item("Customer ID") = txtcustid.Text
dsnewrow.Item("Title") = cbotitle.Text
dsnewrow.Item("First Name") = txtfirstname.Text
dsnewrow.Item("Last Name") = txtsurname.Text
dsnewrow.Item("Company Name") = txtcompanyname.Text
dsnewrow.Item("1st line address") = txtfirstlineadd.Text
dsnewrow.Item("2nd line address") = txtsecondlineadd.Text
dsnewrow.Item("City") = txtcity.Text
dsnewrow.Item("Post Code") = txtpostcode.Text
dsnewrow.Item("Country") = txtcountry.Text
dsnewrow.Item("Tel No") = txttelno.Text
dsnewrow.Item("Bank nane") = lstbank.Text
dsnewrow.Item("Account number") = txtaccno.Text
ds.Tables("SuperTronicsWorld").Rows.Add(dsnewrow)

da.Update(ds, "SuperTronicsWorld")
MsgBox("New Record Successfully appended to the Database!")
btnsave.Enabled() = False
btnadd.Enabled = True
btnedit.Enabled = True
btndelete.Enabled = True
End If
End Sub

This is my code for my Delete button:

Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
'message to promt for deletion first
If MessageBox.Show("Are you sure you want to delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
End If

ds.Tables("SuperTronicsWorld").Rows(inc).Delete()
MaxRows = MaxRows - 1
inc = 0
Navigaterecords()
da.Update(ds, "SuperTronicsWorld")
da.Update(ds, "SuperTronicsWorld")
btnsave.Enabled() = False
btnadd.Enabled = True
btnedit.Enabled = True
btndelete.Enabled = True


End Sub

When I click on save, I get a error which highlights the "da.Update(ds, "SuperTronicsWorld")" in yellow, and it says "Syntax error in INSERT INTO statement."


When I click delete, "txtcustid.Text = ds.Tables("SuperTronicsWorld").Rows(inc).Item(0)" gets highlighted in my "Navigate records" and it says "Deleted row information cannot be accessed through the row."

Please can someone tell me what Im doing wrong?

Thanks alot.

Edited 6 Years Ago by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks)

>Syntax error in INSERT INTO statement.

Remove blanks/space between MS-Access columns.

>Deleted row information cannot be accessed through the row.

..
da.Update(ds, "SuperTronicsWorld")
Navigaterecords()
..

>Syntax error in INSERT INTO statement.

Remove blanks/space between MS-Access columns.

>Deleted row information cannot be accessed through the row.

..
da.Update(ds, "SuperTronicsWorld")
Navigaterecords()
..

I have got rid of the extra da.update (ds, "SuperTronicsWorld") But i've still got the same problem.

And I have no blanks or space in my access tables. I have attached my database.

This is the error I get when I try and delete, i have attached a screenshot, thanks.

Attachments error.jpg 185.86 KB

Remove spaces between column names in access database table.

dsnewrow.Item("Customer ID") = txtcustid.Text
dsnewrow.Item("Title") = cbotitle.Text
dsnewrow.Item("First Name") = txtfirstname.Text
dsnewrow.Item("Last Name") = txtsurname.Text
dsnewrow.Item("Company Name") = txtcompanyname.Text
dsnewrow.Item("1st line address") = txtfirstlineadd.Text
dsnewrow.Item("2nd line address") = txtsecondlineadd.Text
dsnewrow.Item("City") = txtcity.Text
dsnewrow.Item("Post Code") = txtpostcode.Text
dsnewrow.Item("Country") = txtcountry.Text
dsnewrow.Item("Tel No") = txttelno.Text
dsnewrow.Item("Bank nane") = lstbank.Text
dsnewrow.Item("Account number") = txtaccno.Text

Edited 6 Years Ago by __avd: n/a

I have made it so that the code is:

da.Update(ds, "SuperTronicsWorld")
        Navigaterecords()

in the delete section.

but now the error is:

"FirstName IS NULL) OR (FirstName = ?)) AND ((? = 1 AND LastName IS NULL) OR (LastName = ?)) AND ((? = 1 AND CompanyName IS NULL) OR (CompanyName = ?)) AND ((? = 1 AND 1stline'."

I have removed all spaces between columns and execute following code successfully.

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\csnet\WindowsApplication2\SuperTronicsWorld.mdb;Persist Security Info=True")
        Dim adp As New OleDbDataAdapter("select * from tblCustomer", cn)
        Dim cmb As New OleDbCommandBuilder(adp)

        Dim ds As New DataSet
        adp.Fill(ds, "tblCustomer")

        Dim r As DataRow = ds.Tables("tblCustomer").NewRow
        r("CustomerID") = "A1"
        r("Title") = "A"
        r("FirstName") = "A"
        r("LastName") = "A"
        r("CompanyName") = "A"
        r("1stlineaddress") = "A"
        r("2ndlineaddress") = "A"
        r("City") = "A"
        r("PostCode") = "A"
        r("Country") = "A"
        r("TelNo") = 2929922
        r("Banknane") = "A"
        r("Accountnumber") = 11

        ds.Tables("tblCustomer").Rows.Add(r)

        adp.Update(ds, "tblCustomer")

Thanks a lot fo the help! I removed the spaces and closed the application and re opened it and its working now!

Can you please help me on the delete button now? Thanks

My delete button code:

Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        'message to promt for deletion first
        If MessageBox.Show("Are you sure you want to delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
            MsgBox("Operation Cancelled")
            Exit Sub
        End If

        ds.Tables("SuperTronicsWorld").Rows(inc).Delete()
        MaxRows = MaxRows - 1
        inc = 0
        da.Update(ds, "SuperTronicsWorld")
        Navigaterecords()
        btnsave.Enabled() = False
        btnadd.Enabled = True
        btnedit.Enabled = True
        btndelete.Enabled = True


    End Sub

Ive uploaded a new screenshot of the error message

Attachments error.jpg 171.5 KB

Thanks every so much for your help! Really appreciate it! Everything is working now. Thanks again!!

I may need some help again later with different things, I shall repost then if I need to.

This article has been dead for over six months. Start a new discussion instead.