Datasets and Updating problems....

[a_jam_sandwich]

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

 

I've 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 i'm creating Relationship Links are a nice idea. The following CODE doesn't 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 you'll 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 don't 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.

 

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

 

-Nerseus

[a_jam_sandwich]

Tried your suggestion worked.

 

Thx a lot

 

Andy