ADO.net in Vb.net OLEDBCommandBuilder problem

[BodwadUK]

Hello peeps. I hope this in the right place as its a vb.net question relating to databases :P

I am new to ADO.net and no matter how many resources I try to use on the web I cant get my head around how to add records. I thought I had it sussed with the following code but it just wont add a record

The following code is trying to add a records to the Administrators table using the OLEDBCommandBuilder and oleDBDataAdapter objects, it all goes well until it hits the 'DataConnector.update' line where it throws a wobbly saying

"Syntax error in INSERT INTO statement"

Have I got the wrong end of the stick entirely with this stuff?

Any help or advice would be most welcome

       
            Dim DB As New OleDbConnection
            Dim Command As New OleDbCommand
            Dim DataConnector As New OleDbDataAdapter
            Dim Records As New DataSet
            Dim Builder As OleDbCommandBuilder
            Dim RecordsRow As DataRow

            'Open the database
            DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & System.AppDomain.CurrentDomain.BaseDirectory & "AdminServerSettings\Administrators.mdb"
            DB.Open()

            'Recordset information
            Command.Connection = DB
            Command.CommandText = "SELECT * FROM [Administrators] WHERE [ID] = " & ID

            'Set to the data connector
            Dataconnector.SelectCommand = Command

            'Allow changes to occur
            Builder = New OleDbCommandBuilder(DataConnector)

            'Populate the recordset 
            DataConnector.Fill(Records, "ADMINS")
 

                'ADD
                RecordsRow = Records.Tables("ADMINS").NewRow()

                'Store details
                RecordsRow("name") = Name
                RecordsRow("Information") = Information
                RecordsRow("Address") = Address
                RecordsRow("Email") = Email
                RecordsRow("Company") = Company
                RecordsRow("PostCode") = PostCode
                RecordsRow("Town") = Town
                RecordsRow("Telephone") = Telephone

                RecordsRow("Password") = Password
                RecordsRow("Username") = UserName
                RecordsRow("Status") = Status
                'Add
                Records.Tables("ADMINS").Rows.Add(RecordsRow)

                'Update
                DataConnector.Update(Records, "ADMINS")

[techgnome]

It has to do with the Select.... the command builders use the SELECT statement to work up the insert/update/delete commands.... the problem is your where clause.... I'm willing to bet that if you took out the "WHERE [ID] =" bits it'll work...

What I suspect is happening is that the "WHERE [ID] =" gets included in the insert (since it was part of the insert).... and that's an invalid syntax on INSERT....

-tg

[BodwadUK]

Thanks but it didnt work

Would it have anything to do with the fact there is an Autonumber in that table. I am assuming that it will automatically do that number

[techgnome]

It is defined as the PKey? ... Hmm.... not sure that's the problem either...that would be a constraint error, not a syntax....

Umm... try this... before doing an update.... MEssageBox.Show DataConnector.InsertCommand.ToString ..... see what comes out....

-tg

[e34m5]

This is how I did this...note that the table must have a primary key or the command builder will not work. My table has the [ID] field as PK and Autonumber.

    Private Sub Fill_DataGrid()
        Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath & "Drawings.mdb;Persist Security Info=False")

        adapter.SelectCommand = New OleDbCommand("SELECT * FROM " + GridSource, connection)
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)

        connection.Open()

        table.Clear()
        adapter.Fill(table)
        DataGrid.DataSource = table

        builder.GetUpdateCommand()

        connection.Close()

    End Sub

[brucevde]

The Autonumber field isn't a problem. The problem is that you have used keywords as fieldnames (name, password). Get the Builder to surround fieldnames with a delimiter.

'Allow changes to occur
Builder = New OleDbCommandBuilder(DataConnector)
Builder.QuotePrefix = "["
Builder.QuoteSuffix = "]"

[BodwadUK]

ooo that just raises the error

'Object reference not set to an instance of an object.'

on the line of code added :s

Now I have to work out whats not set and how to set it

[brucevde]

ooo that just raises the error

Which code raised the error?

[BodwadUK]

oo sorry I missed your post while I was testing the other code

That QuotePrefix did the trick

Your a star thanks. And thanks everyone else too