Syntax error in INSERT INTO statement with Oledb Command Builder.

[collin_ola]

Hi, I seem to be having a problem when attempting to insert a new record into my database through vb.net. From what I can gather, there seems to be an error in the code that the command builder (cBuilder) is creating for me.

Here's a copy of the code for the connection:

Private Sub btnLoadDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadDB.Click
        Provider = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;"
        Source = "Data Source = C:\Documents and Settings\Hp\My Documents\COMP4\COMP4 CW\WND.accdb"

        connection.ConnectionString = Provider & Source

        connection.Open()

        sqlCode = "SELECT * FROM Customers"
        dAdapter = New OleDb.OleDbDataAdapter(sqlCode, connection)
        dAdapter.Fill(dSet, "Customers")

        MsgBox("Database Loaded.", MsgBoxStyle.Information)
        connection.Close()

        maxRows = dSet.Tables("Customers").Rows.Count
        incr = -1

    End Sub

Here is the code for adding a new record:

If incr <> -1 Or maxRows = 0 Then

            Dim cBuilder As New OleDb.OleDbCommandBuilder(dAdapter)
            Dim dSetNewRow As DataRow

            dSetNewRow = dSet.Tables("Customers").NewRow

            dSetNewRow.Item(0)= txtCustomerID.text
            dSetNewRow.Item(1) = txtTitle.Text
            dSetNewRow.Item(2) = txtFirstName.Text
            dSetNewRow.Item(3) = txtLastName.Text
            dSetNewRow.Item(4) = txtDOB.Text
            dSetNewRow.Item(5) = txtAddress.Text
            dSetNewRow.Item(6)= txtTown.text
            dSetNewRow.Item(7) = txtPostcode.Text
            dSetNewRow.Item(8) = txtCounty.Text
            dSetNewRow.Item(9) = txtTelNumber.Text
            dSetNewRow.Item(10) = txtPostcode.Text

            dSet.Tables("Customers").Rows.Add(dSetNewRow)

            dAdapter.Update(dSet, "Customers")

            MsgBox("A new record has successfully been added to the database" _
                  , MsgBoxStyle.Information)
end if
end sub

I would use my own SQL statements, but I'm not too sure about how to put them into my program. Any assistance would be greatly appreciated.

Thanks :)

Collin

[Reverend Jim]

You can form the insert query by

query = "insert into TABLENAME _
   (CustomerID,Title,FirstName,LastName,DOB,Address,Town,Postcode,County,TelNumber) _
    values('" & txtCustomerID.Text & "','" _
              & txtTitle.Text      & "','" _
              & txtFirstName.Text  & "','" _
              & txtLastName.Text   & "','" _
              & txtDOB.Text        & "','" _
              & txtAddress.Text    & "','" _
              & txtTown.Text       & "','" _
              & txtPostcode.Text   & "','" _
              & txtCounty.Text     & "','" _
              & txtTelNumber.Text  & "')"

Note that VB will reformat the code to remove extra spaces. I left it as above so it would be easier to see how the query is built. How you apply the query to the database depends on how you connected. If you do it with ADO then it would look like[code]
Imports ADODB
.
.
.
conn = New Connection
conn.Open("Driver={SQL Server};Server=.\sqlexpress;Database=mydb;Trusted_Connection=yes;")
query = (use the code from above)
conn.Execute(query)
conn.Close()

[collin_ola]

Thank you, I will give the above a try and see if it works for me :)

[Reverend Jim]

My apologies. I mistyped the last line of code. It should have had the closing parenthesis inside the quotes as in

& txtTelNumber.Text & "')"

[collin_ola]

I'm sure that what I've done so far is okay, but I'm not too cure about what goes in between opening and closing the connection, as vb doesn't seem to have a

connection.execute

function. Here's what I've got:

Dim sqlCode As String
        Dim connection As New OleDb.OleDbConnection

        sqlCode = "INSERT INTO Customers VALUES ('" & txtCustomerID.Text & "','" & txtTitle.Text & "','" & _
        txtFirstName.Text & "','" & txtLastName.Text & "','" & txtDOB.Text & "','" & txtAddress.Text & "','" & _
        txtTown.Text & "','" & txtPostcode.Text & "','" & txtCounty.Text & "','" & txtTelNumber.Text & "','" & _
        txtEMail.Text & "')"



        Provider = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;"
        Source = "Data Source = C:\Documents and Settings\Hp\My Documents\COMP4\COMP4 CW\WND.accdb"

        connection.ConnectionString = Provider & Source

        connection.Open()




        MsgBox("Record Added.", MsgBoxStyle.Information)
        connection.Close()

Collin

[collin_ola]

Hi,

With your help and the help of another thread I found, I've managed to solve the problem using the sql code and the following lines:

connection.Open()
        cmd = New OleDb.OleDbCommand(sqlCode, connection)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        MsgBox("Record Added.", MsgBoxStyle.Information)
        connection.Close()

Thanks very much for your help, I really do appreciate it :)

Collin