[RESOLVED] Does Adapter.fill have a query length restriction?

[Zapper]

Basically!

I have a database with around 100 columns and when i go:

Me.Validate()
Adapter.Update(Table)

It fails after i edit the table, giving me an error:

Syntax error (missing operator) in query expression '((ID = ?) AND ((? = 1 AND Record IS NULL) OR (Record = ?)) AND ((? = 1 AND Details_FileCreated IS NULL) OR (Details_FileCreated = ?)) AND ((? = 1 AND Details_Particulars IS NULL) OR (Details_Particulars = ?)) AND ((? = 1 AND Details_Date IS NULL) OR (Deta'.

The code works fine with a smaller database, so i was just wondering the problem is that the database is too big and the adapter isn't able to handle such a long query.

I was just wondering if anyone knows what kind of limits there are?

Thanks,

[jmcilhinney]

What happens if you omit the Record column? Does it work then?

[Zapper]

Hey jmcilhinney,

I did some more testing and research and i found this:

http://blogs.msdn.com/spike/archive/...ng-access.aspx

Too many "Ands".Im planning to just go create multiple tables. Can you think of a better way to work around it?

[jmcilhinney]

One possibility is removing the optimistic concurrency built into that query. Only do that if you don't need it though. That would remove vertually the entire WHERE clause.

[Zapper]

I just googled "optimistic concurrency" and from what i understand, it would not affect me if i were to remove optimistic concurrency as, for my purpose, only one person uses the database at one time.

So may i ask how?

i have a datagridview class, heres part of the code for it:

Public Class DatagridClass
    '... Variables goes here (omitted) ...

    Public Sub New(ByVal _FilePath As String, ByVal _TableName As String, ByRef _DataGrid As DataGridView)

        Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _FilePath)
        Sql = New OleDbCommand
        Adapter = New OleDbDataAdapter("SELECT * FROM " & TableName, Connection)
        Builder = New OleDbCommandBuilder(Adapter)
        Table = New DataTable

        Adapter.Fill(Table)
        DataGrid.DataSource = Table

    End Sub

    Public Sub Save()
         Parent.Validate()
        Adapter.Update(Table)
    End Sub

Thanks,

[jmcilhinney]

I believe that you would set the ConflictOption property of your CommandBuilder to OverwriteChanges. Note that you should be setting the MissingSchemaAction property of your DataAdapter to AddWithKey, so that your DataTable contains primary key information that your CommandBuilder can use and therefore avoid using the other columns to identify which record to update/delete.

[Zapper]

Thanks so much, as again, jmcilhinney.

Builder.ConflictOption = ConflictOption.OverwriteChanges <- Works like a wiz