I can't belive that there is a very low limit on the number of columns I can write to using OLEDB.
My test database worked fine, but when I tried to access my real database which has 67 columns, I could fill the recordset but updating always fails telling me that there is an error in my Insertcommand.
If I start paring the columns off the database I find 25 columns doesnt work but 24 does. All the columns are numeric.
Admittedly when I look at the updatecommand and insertcommand they are very big, but there has to be a way round it. - Doesn't there?
Code
My test database worked fine, but when I tried to access my real database which has 67 columns, I could fill the recordset but updating always fails telling me that there is an error in my Insertcommand.
If I start paring the columns off the database I find 25 columns doesnt work but 24 does. All the columns are numeric.
Admittedly when I look at the updatecommand and insertcommand they are very big, but there has to be a way round it. - Doesn't there?
Code
VB.NET:
Private Sub AddToWorking(ByVal BlockNumber As Integer)
Dim conn As New OleDbConnection
Dim ds As New DataSet
Dim da As OleDbDataAdapter
Dim strPathAndName As String
Dim dsNewRow As DataRow
Dim tablename As String
tablename = "Results"
strPathAndName = "C:\temp\working29.mdb"
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _
& strPathAndName
conn.Open()
Dim sql As String = "Select * from " & tablename
da = New OleDbDataAdapter(sql, conn)
da.Fill(ds, "MyResults")
Dim cb As New OleDbCommandBuilder(da)
da.UpdateCommand = cb.GetUpdateCommand()
da.InsertCommand = cb.GetInsertCommand()
'add row to dataset
dsNewRow = ds.Tables("MyResults").NewRow()
'add data values
dsNewRow.Item("Field1") = 4
dsNewRow.Item("Field2") = 5
dsNewRow.Item("Field3") = 6
'commit row to database
ds.Tables("MyResults").Rows.Add(dsNewRow)
da.Update(ds, "MyResults")
conn.Close()
End Sub