Error When trying to add Customer to Database (syntax error in insert into statement oledb vb net)

Question

I am doing a college assignment and have been trying to figure it out for hours, but I cant seem to get my new customer to save to the database! Please, I would really, REALLY apreciate it if you could have a look at my code, make any suggestions, or let me know more efficient ways of doing this. Bellow I provide a sample of my code for this.

To begin with, on form load I determine the new customer ID to be put into the database:

Private Sub frmRegister_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Me.CustomerTableAdapter.Fill(Me.DatabasePizzaPalaceDataSet.Customer)

    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"

    conn.Open()

    Dim Rows As Integer
    Dim sql As String = "SELECT * FROM Customer"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    da = New OleDb.OleDbDataAdapter(sql, conn)

    da.Fill(ds, "Customer")

    Rows = ds.Tables("Customer").Rows.Count

    NewCustomerID.Text = Rows + 1

    Customer_IDTextBox.Text = NewCustomerID.Text

    conn.Close()

End Sub

Now that being said, here is the piece of code I run when clicking on my save button for the recrod to be added through a new data row.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"
    conn.Open()

    Dim inc As Integer
    Dim sql As String = "SELECT * FROM Customer"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Customer")
    inc = Customer_IDTextBox.Text

    If inc <> -1 Then

        Dim cb As New OleDb.OleDbCommandBuilder(da)

        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("Customer").NewRow()

        dsNewRow.Item("Customer_ID") = Customer_IDTextBox.Text
        dsNewRow.Item("Username_Email") = Username_EmailTextBox.Text
        dsNewRow.Item("Password") = PasswordTextBox.Text
        dsNewRow.Item("First_Name") = First_NameTextBox.Text
        dsNewRow.Item("Surname") = SurnameTextBox.Text
        dsNewRow.Item("Mobile") = MobileTextBox.Text
        dsNewRow.Item("House") = HouseTextBox.Text

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

        da.Update(ds, "Customer")

        MsgBox("New Record added to the Database")

        conn.Close()

        frmLogin.Show()

    End If

    'Dim cb As New OleDb.OleDbCommandBuilder(da)


    'Me.CustomerTableAdapter.Insert(Customer_IDTextBox.Text, Username_EmailTextBox.Text, PasswordTextBox.Text, First_NameTextBox.Text, SurnameTextBox.Text, MobileTextBox.Text, HouseTextBox.Text)
    'Me.CustomerTableAdapter.Fill(Me.DatabasePizzaPalaceDataSet.Customer)
    'Me.Validate()
    'Me.CustomerBindingSource.EndEdit()
        'Me.CustomerTableAdapter.Fill(DatabasePizzaPalaceDataSet.Customer)
        'Me.TableAdapterManager.UpdateAll(Me.DatabasePizzaPalaceDataSet)

    'da.Update(ds, "Customer")

    'MsgBox("You have been succesfully registerd with us. Thanks!")

    'conn.Close()

    'frmLogin.Show()
End Sub

In comments you can also see a code provided by my teacher, which we are supposed to improve, I just wish to find a way of making this work!

Thanks a lot, all help and suggestions are very appreciated.

I am not sure, I am trying to follow this tutorial to commit changes to Database - homeandlearn.co.uk/NET/nets12p10.html — user3684012
– user3684012, Commented May 28, 2014 at 14:24
However, the command builder and the update should do the saving — user3684012
– user3684012, Commented May 28, 2014 at 14:25
Are you certain that "Customer" is the name of the table inside your dataset? I tried your code and it worked for me. When you are debugging, is the code actually getting inside your IF Then Else statement? — Mark C.
– Mark C., Commented May 28, 2014 at 14:41
Hmm strange, it is customer both inside the database and also that is how I am naming the dataset...not sure if that causes confusion to the program? — user3684012
– user3684012, Commented May 28, 2014 at 14:42

Mark C. · Accepted Answer · 2014-05-28 14:47:05Z

1

Instead of ds.Tables("Customer") I used ds.Tables(0) (or whatever index your table is at inside your DataSet.)

    Dim con As New OleDbConnection(_myConn) ''_myConn should be your connection string
    con.Open()
    Dim da As OleDbDataAdapter
    Dim ds As New DataSet
    da = New OleDbDataAdapter("select * from customer", con)
    da.Fill(ds)

    Dim cb As New OleDbCommandBuilder(da)
    Dim dsNewRow As DataRow

    dsNewRow = ds.Tables(0).NewRow()
    dsNewRow.Item(1) = "1"
    dsNewRow.Item(2) = "Blah"
    dsNewRow.Item(3) = "Test"
    dsNewRow.Item(4) = "T"
    dsNewRow.Item(5) = "T"
    dsNewRow.Item(6) = "T"
    dsNewRow.Item(7) = "20000101"

    ds.Tables(0).Rows.Add(dsNewRow)
    da.Update(ds.Tables(0))

    con.Close()

It's important to realize your database schema also. If your first column is an identity auto increment column, you want to avoid trying to insert anything to that column. I prefer to use the Indexes because it's a lot easier to misspell a column name as a string, although it may not be as clear.

So, Customer_ID may be (or not be) an auto-increment field, which means trying to insert data into that column will result in an error.

answered May 28, 2014 at 14:47

Mark C.

6,5004 gold badges39 silver badges74 bronze badges

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

12 Comments

user3684012 Over a year ago

My database does not use auto incremented keys, but used to, I will just create the program again copy paste the code and create new connection, I'll let you know how.it goes thanks for all

Mark C. Over a year ago

No problem. Just make sure the data types are correct. This code works for me, so as long as your connection is correct and you are inserting the correct data types/number of fields into the database it should work.

user3684012 Over a year ago

I am getting the same error, I am referring to the tables as per index instead of names and same on items, but error when trying to update...I have had a look at my database before doing this, it is set to numbers, although it does have a primary key in place and relationships already on the database. Then I just created a new solution, copied and pasted old code created new connection, and just edited the code to what is here and nothing,However, thanks a lot for your help it helps me know a bit more about what could be going on and how it all works, let me know if you got any other ideas.

Mark C. Over a year ago

When you say " it is set to numbers, " what do you mean?

Mark C. Over a year ago

Let me expand on data types. So if Customer_ID is a Number field in the database, you need to wrap your textbox value like this: CInt(Customer_IDTextBox.Text). This converts the string value from the textbox to the data type that the database is holding/expecting.

|

Collectives™ on Stack Overflow

Error When trying to add Customer to Database (syntax error in insert into statement oledb vb net)

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related