DataTableAdapter.Fill Error: Command is missing required clause

[rkulp]

I am trying to read three dBase IV tables. The code works fine with two of the tables but not the third. The exception message is not particularly informative as all three are identical except for names.The problem is near the end on file csInd.dbf. Since the required extension for connecting to dBase files, I had to check for that file extension and copy files if necessary. That works fine. Here is the code:

  Private Sub BuildDB()
        Try
            ' Checking for existence and naming of required data tables
            ' Family Data Table -- This will form dataset and parent table
            If Not (My.Computer.FileSystem.FileExists(UserFolder + "csFamily.dbf")) Then
                If My.Computer.FileSystem.FileExists(UserFolder + "csfamily.udb") Then
                    My.Computer.FileSystem.CopyFile(UserFolder + "csfamily.udb", UserFolder + "csfamily.dbf")
                Else
                    gMsg = "Neither file " + UserFolder + "csFamily.udb nor file " + UserFolder + "csFamily.dbf was found."
                    MsgBox(gMsg, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly)
                    btnEnd.PerformClick()
                End If
            End If

            'Individual DataTable csInd.dbf  This is child table
            If Not (My.Computer.FileSystem.FileExists(UserFolder + "csInd.dbf")) Then
                If (My.Computer.FileSystem.FileExists(UserFolder + "csInd.udb")) Then
                    My.Computer.FileSystem.CopyFile(UserFolder + "csInd.udb", UserFolder + "csInd.dbf")
                Else
                    gMsg = "Neither file " + UserFolder + "csInd.udb nor file " + UserFolder + "csInd.dbf was found."
                    MsgBox(gMsg, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly)
                    btnEnd.PerformClick()
                End If
            End If
            'Using cdFamily to build dsFamily dataset and dtFamily datatable
                Dim csFamily As String = ""
                csFamily = "Provider=vfpoledb;Data Source=" & UserFolder & "csFamily.dbf;Extended Properties=dBASE IV;User ID=Admin;Password=;"
                Dim dcFamily As OleDb.OleDbConnection = New OleDb.OleDbConnection(csFamily)
                Dim cmdFamily As New OleDb.OleDbCommand("SELECT FAMILY_ID,FAM_NAME, ADDR1,ADDR2, CITY,STATE,ZIP,H_PHONE,GROUP_NAME,HEAD,HEAD_ID,SPOUSE,SPOUSE_ID,UDF1,UDF2 FROM csFamily ORDER BY GROUP_NAME", dcFamily)
                Dim daFamily As New OleDb.OleDbDataAdapter
                Dim dsFamily As New DataSet("dsFamily")
            Dim bsFamily As BindingSource = New BindingSource
            'Checking for Photo data table. Will incorporate photo path in dtFamily
            If Not (My.Computer.FileSystem.FileExists(UserFolder + "csphoto.dbf")) Then
                If My.Computer.FileSystem.FileExists(UserFolder + "csphoto.udb") Then
                    My.Computer.FileSystem.CopyFile(UserFolder + "csphoto.udb", UserFolder + "csphoto.dbf")
                Else
                    gMsg = "Neither file " + UserFolder + "csphoto.udb nor file " + UserFolder + "csphoto.dbf was found."
                    MsgBox(gMsg, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly)
                    btnEnd.PerformClick()
                End If
            End If

            Dim csPhoto As String = ""
                csPhoto = "Provider=vfpoledb;Data Source=" & UserFolder & "csphoto.udb;Extended PropELerties=dBASE IV;User ID=Admin;Password=;"
                Dim dcPhoto As OleDb.OleDbConnection = New OleDb.OleDbConnection(csPhoto)
                Dim cmdPhoto As New OleDb.OleDbCommand("SELECT PHOTO_ID, FILE_LIB FROM csPhoto.udb ORDER BY PHOTO_ID", dcPhoto)
                Dim cmdPhotoString As String = cmdPhoto.CommandText
                Dim daPhoto As New OleDb.OleDbDataAdapter
                Dim dsPhoto As New DataSet("dsPhoto")
                Dim dtPhoto As New DataTable
                Dim bsPhoto As BindingSource = New BindingSource
            'Finally building dsFamily and getting the photo path
            dcFamily.Open()
                daFamily.SelectCommand = cmdFamily
            'daFamily.Fill(dtFamily)
                daFamily.Fill(dsFamily, "dtFamily")
                bsFamily.DataSource = dtFamily
            dcPhoto.Open()
                daPhoto.SelectCommand = cmdPhoto
            ' daPhoto.Fill(dtPhoto)
                daPhoto.Fill(dsPhoto, "dsPhoto")
                bsPhoto.DataSource = dtPhoto
                Dim colcount As Integer = dtFamily.Columns.Count
                dtFamily.Columns.Add("PhotoLocation")
                Dim FamID As String = ""
            Dim RowCount As Integer = dtFamily.Rows.Count
            'Getting photo path and inserting it into dtFamily 
                For I = 0 To RowCount - 1
                    FamID = dtFamily.Rows(I).Item("Family_ID")
                    Try
                        Dim r() As DataRow = dtPhoto.Select("Photo_ID = " + FamID)
                        dtFamily.Rows(I).Item(colcount) = r(0).Item(1)
                    Catch ex2 As Exception
                    End Try
                Next
                With dgvFamily
                    .DataSource = dtFamily
                    .Show()
                End With
                nbrFamilies = dtFamily.Rows.Count
                'nbrFamilies = dgvFamily.RowCount
            'MsgBox("Number of Family Records: " + nbrFamilies.ToString)
            'Working on csInd to get datatable dtInd
            Dim csInd As String = "Provider=vfpoledb;Data Source=" & UserFolder & "csind.dbf;Extended Properties=dBASE IV;User ID=Admin;Password=;"
            Dim dtInd As New DataTable
            ' Dim dsInd As New DataSet("dsInd")
            Dim dcInd As OleDb.OleDbConnection = New OleDb.OleDbConnection(csInd)
            Dim cmdInd As New OleDb.OleDbCommand("SELECT Ind_ID, FAMILY_ID, FIRST_NAME, LAST_NAME,PREFERNAME,C_PHONE,EMAIL1,EMAIL2", dcInd)
            Dim cmdIndString As String = cmdInd.CommandText
            Dim daInd As New OleDb.OleDbDataAdapter
            daInd.SelectCommand = cmdInd
            Dim bsInd As BindingSource = New BindingSource
            dcInd.Open()
            daInd.Fill(dtInd)
            bsInd.DataSource = dtInd
            dsFamily.Tables.Add(dtInd)
            dsFamily.Relations.Add("FamilyMembers", dsFamily.Tables("dtFamily").Columns("FAMILY_ID"), dsFamily.Tables("dtInd").Columns("FAMILY_ID"))
            ' WordPrintDirectory()
            SingleColumnWordPrint()
        Catch ex As Exception
            gMsg = "The following error occurred building the database:" + vbCrLf + ex.Message.ToString + vbCrLf
            MsgBox(gMsg, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly)
            btnEnd.PerformClick()
        End Try
    End Sub

[Shaggy Hiker]

Welcome to the forums. I wrapped your code in [CODE][/CODE] tags to format it a bit cleaner. You can do this by pressing the # button and pasting code between the resulting tags.

One point unrelated to your question is that you were creating datatables for each table, but appear to have switched to creating a different dataset for each table. There is no real harm in doing that, it's just pointless. A dataset is nothing more than a collection of datatables, so you are essentially just creating a series of collections, each of which has only one item. Using the datatables was better than that, as it would have slightly less overhead. Alternatively, create just one dataset and put all the datatables into that, which is probably the best way to go, since you have several different tables.

As to the question, can we assume that the error message you are getting is in the subject line of this thread (Fill Error: Command is missing required clause)? If not, it would be good to know what the error message is. Also, it sounds like you have identified the source of the error as being this line:

daPhoto.Fill(dsPhoto, "dsPhoto")

Is that correct?

[jmcilhinney]

Did you even bother to look for the cause of the issue? I don't think you did. The error message tells you what the issue is: your SQL code is missing a required clause. What clauses does a SQL query require? Did you give that any thought all? Here's your first SQL query:

SELECT FAMILY_ID,FAM_NAME, ADDR1,ADDR2, CITY,STATE,ZIP,H_PHONE,GROUP_NAME,HEAD,HEAD_ID,SPOUSE,SPOUSE_ID,UDF1,UDF2 FROM csFamily ORDER BY GROUP_NAME

Here's your second:

SELECT PHOTO_ID, FILE_LIB FROM csPhoto.udb ORDER BY PHOTO_ID

Here's your third:

SELECT Ind_ID, FAMILY_ID, FIRST_NAME, LAST_NAME,PREFERNAME,C_PHONE,EMAIL1,EMAIL2

I don't see how you could possibly have looked at that with a critical eye and not seen the issue.

[rkulp]

Thanks for both of your comments. I'll clean up the code. Clearly, I did not check the SQL statement properly. The problem is solved with putting in the " "FROM csInd.dbf" into the last SQL statement. It has been several years since I did any coding and am embarassed by this error.

[jmcilhinney]

It has been several years since I did any coding and am embarassed by this error.

We've all done embarrassing things at times. The important thing is to learn from those mistakes and minimise future embarrassment.

[ChrisE]

this look's like a typo

 Dim csPhoto As String = ""
                csPhoto = "Provider=vfpoledb;Data Source=" & UserFolder & "csphoto.udb;Extended PropELerties=dBASE IV;User ID=Admin;Password=;"

I'm sure you mean

Properties