Hi all,
I was going through a lot of resources on how to populate a dataset.And i kind of got an error when trying to populate the created dataset with 4 select statements.
but my sql uses the where clause,the other statements i saw select certain columns and go to the other select statement using a semi column.

Now when i include the semi-colon between the two sql statements and try to run an error occurs telling me that "Characters found after end of SQL statement".

And when i omit the semi colon another error comes with "Syntax error in query expression".Is there a way to fix the problem?

Thank you for your help.

cmd = New OleDb.OleDbCommand
        cmd.CommandText = "SELECT [description] FROM [project] WHERE [project number]='" & str & "' " _
            & "SELECT [material number],[material type],[unit],[quantity],[rate],[cost/unit] FROM [work_item_material_cost]" & _
            "WHERE [work_item_material_cost].[project number]='" & str & "' " _
            & "SELECT [labour number],[labour title],[quantity],[utilization factor],[indexed hourly cost],[total hourly cost] FROM [work_item_labor_cost]" & _
            "WHERE [work_item_labor_cost].[project number]='" & str & "' " _
            & "SELECT [equipment number],[equipment type],[quantity],[utilization factor],[hourly rental rate],[total rental rate] FROM [work_item_equipment_cost]" & _
            "WHERE [work_item_equipment_cost].[project number]='" & str & "'"

        cmd.Connection = conn
        cmd.Parameters.AddWithValue("@project", str)
        cmd.CommandType = CommandType.Text

        Using adp As New OleDb.OleDbDataAdapter(cmd)
            'Check Datatable for data and clears it
            'If currentProject.Rows.Count > 0 Then
            'currentProject.Rows.Clear()
            'End If
            currentProject.Tables.Clear()
            adp.Fill(currentProject)

I don't use datasets myself, but from what I can tell you can only populate a dataset with homegeneous data. In other words, all records must have the same number and type of fields. In your case you are trying to use multiple selects on different tables with different number and types of fields. If the tables contained the same data (perhaps identical fields but for different months or years) you could use UNION between the selects to cooncatenate the recordsets, however, in your case this cannot be done.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.