Datasets and Updating problems....

[a_jam_sandwich]

Ive not been on for a while (Off work) so happy new year to you all.

Ive started looking at ADO.NET and trying inserting a record into a ACCESS2000 database. The problems.... I what to use datasets as In the software im creating Relationship Links are a nice idea. The following CODE doesnt work with the error;

Syntax error in INSERT INTO statement. Anyone know what im doing wrong???? Also when looking at the SQL in the OleDbCommandBuilder all there is, is ?????????? where the values should be

        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\support.mdb"
        Dim SelectString As String = "SELECT * FROM SUPPORT"
        Dim MyConn As New OleDbConnection(ConnectionString)
        Dim MyAdapter As New OleDbDataAdapter()
        MyAdapter.SelectCommand = New OleDbCommand(SelectString, MyConn)
        Dim CommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(MyAdapter)

        Dim MyDataset As New DataSet()
        Dim MyTable As New DataTable()
        Try
            MyConn.Open()
            MyAdapter.FillSchema(MyDataset, SchemaType.Source, "Support")
            MyAdapter.Fill(MyDataset, "Support")

            MyTable = MyDataset.Tables("Support")

            Dim MyRow As DataRow = MyTable.NewRow
            MyRow(2) = "Mr D"
            MyTable.Rows.Add(MyRow)

            MsgBox(CommandBuilder.GetInsertCommand.CommandText.ToString)
            MyAdapter.Update(MyDataset, "Support")

        Catch ex As OleDbException
            Console.WriteLine(ex.Message)
        End Try

Cheers Andy.

 

[Nerseus]

The code you have should be working. What are the columns in your table? If any are reserved words, the default "INSERT" statement will fail. For example, if you have a column named "First" instead of "FirstName" it will fail as Access uses First as a reserved word. You CAN do it, but youll have to build your InsertCommand by hand, wrapping each column name with square brackets. For example:

INSERT INTO SUPPORT ([Col1], [Col2], [First]) VALUES ( ?, ?, ?)

Also, you dont need the line:
MyAdapter.FillSchema(MyDataset, SchemaType.Source, "Support")
When you perform .Fill(...), it will create the table for you. If you wanted only the table definition built, you could use FillSchema.

Youve probably noticed the performance hit when you run the MsgBox line. Once you get your tables setup correctly, its best to copy the auto-generated SQL statements from the CommandBuilder and insert them manually into your code. That way your code doesnt have to build them every time.

-Nerseus

 

[a_jam_sandwich]

Tried your suggestion worked.

Thx a lot

Andy