Limit to number of columns?

[DaveP]

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

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

 

[crvasquez]

Are you sure the error is in your insert command because of the columns? What I usually find is that there is an issue with the data. Just a thought.

 

[cjard]

I can't belive that there is a very low limit on the number of columns I can write to using OLEDB.

I never heard of this limitation. Perhaps you have called one of your columns something problematic, like a reserved word, or put spaces in it..

Note that, as youre using vs2008, youre doing your data access in nearly completly the wrong way.. Try following the DW2 link in my sig and read the Creating a Simple Data App topic.

Note: DW2 is is targeted at .net 2. I havent thus far investigated how .net 3 does data access, but if youre not linqing then i think its largely the same as 2. Read any links that are more relevant to your version..