Why Do I Get This Error???

[Jigabyte]

Hi Peeps,

I have the following code:-

        sSQL = "SELECT * FROM FILESTRUCTURE"
        oleDAFiles = New OleDb.OleDbDataAdapter
        oleDAFiles.SelectCommand = New OleDb.OleDbCommand(sSQL, oleDCData)
        oleDSFiles = New DataSet
        oleDAFiles.Fill(oleDSFiles, "FILESTRUCTURE")

        For iRowCount = 0 To oleDSFiles.Tables(0).Rows.Count - 1
            oleDSFiles.Tables(0).Rows(iRowCount).Delete()
            oleCBFiles = New OleDb.OleDbCommandBuilder(oleDAFiles)
            oleDAFiles.Update(oleDSFiles, "FILESTRUCTURE")
        Next

On the update I get a syntax error and do not no why?????

Thanks for any help,

Jiggy!

[Jigabyte]

Sorry the syntax error is:-

"Syntax error (missing operator) in query expression '((REFNO = ?) AND ((? = 1 AND PATH IS NULL) OR (PATH = ?)) AND ((? = 1 AND FTPPATH IS NULL) OR (FTPPATH = ?)) AND ((? = 1 AND FILENAME IS NULL) OR (FILENAME = ?)) AND ((? = 1 AND MODIFIEDDATETIME IS NULL) OR (MODIFIEDDATETIME = ?)) AND ((? = 1 AND TRANSFER'."

[Siddharth Rout]

What is the value of oleDCData?

[Jigabyte]

    Public oleDCData As OleDb.OleDbConnection

[Siddharth Rout]

Show me the connection string...

Also if you can include the complete code in that sub, I will go through it in one go..

[Jigabyte]

Thank u for your help; here is my code:-

  Private Sub CreateNewDatabase(ByVal sPath As String)

        Dim oleDAFiles As OleDbDataAdapter
        Dim oleDSFiles As DataSet
        Dim oleDRFiles As DataRow
        Dim oleCBFiles As OleDbCommandBuilder

        Dim sFileStructure As String
        Dim iLoop As Int16
        Dim sSQL As String

        sSQL = "SELECT * FROM FILESTRUCTURE"
        oleDAFiles = New OleDb.OleDbDataAdapter
        oleDAFiles.SelectCommand = New OleDb.OleDbCommand(sSQL, oleDCData)
        oleDSFiles = New DataSet
        oleDAFiles.Fill(oleDSFiles, "FILESTRUCTURE")

        iFileCount = -1
        sFileStructure = Dir(sPath & "\*.*", FileAttribute.Directory)

        While sFileStructure <> ""

            iFileCount = iFileCount + 1
            If sFileStructure <> "" And sFileStructure <> "." And sFileStructure <> ".." Then
                If (GetAttr(sPath & sFileStructure) And vbDirectory) = FileAttribute.Directory Then
                    iArrayPosition = iArrayPosition + 1
                    iFilePosition(iArrayPosition) = iFileCount

                    Call CreateNewDatabase(sPath & sFileStructure)
                    sFileStructure = Dir(sPath & "\*.*", FileAttribute.Directory)
                    iFileCount = 0
                    For iLoop = 0 To iFilePosition(iArrayPosition) - 1
                        sFileStructure = Dir()
                        iFileCount = iFileCount + 1
                    Next
                    iArrayPosition = iArrayPosition - 1
                Else
                    If UCase$(sFileStructure) <> "FTPSERVER.LDB" Then
                        oleDRFiles = oleDSFiles.Tables(0).NewRow
                        oleDRFiles("PATH") = "m:" & sPath.ToString.Substring(2, Len(sPath) - 2)
                        oleDRFiles("FTPPATH") = Mid$(sChangeSlashes(oleDRFiles("PATH")), 3, Len(oleDRFiles("PATH")) - 2)
                        oleDRFiles("FILENAME") = sFileStructure
                        oleDRFiles("MODIFIEDDATETIME") = FileDateTime(sPath & "\" & sFileStructure)
                        oleDSFiles.Tables(0).Rows.Add(oleDRFiles)

                        oleCBFiles = New OleDb.OleDbCommandBuilder(oleDAFiles)
                        oleDAFiles.Update(oleDSFiles, "FILESTRUCTURE")
                    End If
                End If
            End If
            sFileStructure = Dir()

        End While

    End Sub

[Siddharth Rout]

What is the value of sPath at the time of the error?

[jmcilhinney]

I think you might be chasing red herrings koolsid. The error message says that there's a syntax error in the SQL code. The most common cause of that is a query that uses a wildcard to return one or more columns whose names are reserved words or contain illegal characters. The way to fix it is to either change your column names or else escape the culprits in the query, e.g. instead of this:

SELECT * FROM MyTable

you do this:

SELECT [Column1], [Column2] FROM MyTable

The command builder should then follow your lead and the syntax error goes away.

[Siddharth Rout]

I think you might be chasing red herrings koolsid.

Possibly Jm

The error also mention Path error so I wanted to start off from the very start and cover every nook and corner of the code since I cannot test it.

[jmcilhinney]

The error also mention Path error

That would be a PATH column in the database table.

[Siddharth Rout]

That would be a PATH column in the database table.

Ah! In that case I was actually chasing red herrings