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.

Recommended Answers

All 13 Replies

>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.

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

Ah I see. I have removed the spacing in the access table and in the coding, still no luck. It's still the same error. I can upload the application if you want? I've been using http://www.homeandlearn.co.uk/NET/nets12p9.html for the codings.

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

Thanks.

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

Yes.

Rename following fields (columns).

1. 1stlineaddress
2. 2ndlineaddress

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.

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.