CODEWITHSUNDEEP
sqlvb.netms-access
Nee-Z
Nee-Z

Reputation: 45

error in query for multiple result set using a dataset

I was going through a lot of resources on how to populate a data set.And i kind of got an error when trying to populate the created data set 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.

    str1 = "SELECT [description] FROM [project] WHERE [project number]=@project"
    str2 = "SELECT [material number],[material type],[unit],[work_item_material_cost].[quantity],[rate],[cost/unit] FROM [work_item_material_cost]" & _
  "WHERE [work_item_material_cost].[project number]=@wimproject"
    str3 = "SELECT [labour number],[labour title],[work_item_labor_cost].[quantity],[work_item_labor_cost].[utilization factor],[indexed hourly cost],[total hourly cost] FROM [work_item_labor_cost]" & _
   "WHERE [work_item_labor_cost].[project number]=@wilproject"
    str4 = "SELECT [equipment number],[equipment type],[work_item_equipment_cost].[quantity],[work_item_equipment_cost].[utilization factor],[hourly rental rate],[total rental rate] FROM [work_item_equipment_cost]" & _
 "WHERE [work_item_equipment_cost].[project number]=@wieproject"

    currentProject = New DataSet()
    project = New DataTable("project")
    wimaterial = New DataTable("work_item_material")
    wilabour = New DataTable("work_item_labour")
    wiequipment = New DataTable("work_item_equipment")

    cmd.CommandText = str1
    cmd.Parameters.AddWithValue("@project", str)
    Dim data_reader = cmd.ExecuteReader()
    project.Load(data_reader)
    currentProject.Tables.Add(project)

Upvotes: 0

Views: 153

Answers (1)

Steve
Steve

Reputation: 216293

It seems that you are using MSAccess as your database.
If this is the case then MSAccess doesn't support multiple select statements as your query above.
You have no alternative than issuing every select command by itself. (or use a different database).

string q1 = "SELECT [description] FROM [project] WHERE [project number]=?"
string q2 = "SELECT [material number],[material type],[unit],[quantity],[rate],[cost/unit] " & _
            "FROM [work_item_material_cost] WHERE [work_item_material_cost].[project number]=?" 
string q3 = "SELECT [description] FROM [project] WHERE [project number]=?"
string q4 = "SELECT [material number],[material type],[unit],[quantity],[rate],[cost/unit] " & _
            "FROM [work_item_material_cost] WHERE [work_item_material_cost].[project number]=?" 
string q5 = "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]=?"
string q6 = "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]=?"

Dim ds As DataSet = new DataSet()
Dim dt1 = new DataTable("dt1")
Dim dt2 = new DataTable("dt2")
Dim dt3 = new DataTable("dt3")
Dim dt4 = new DataTable("dt4")
Dim dt5 = new DataTable("dt5")
Dim dt6 = new DataTable("dt6")

cmd = New OleDbCommand()
cmd.Connection = conn
cmd.CommandText = q1
cmd.Parameters.AddWithValue("@p1", str)
Dim rd = cmd.ExecuteReader()
dt1.Load(reader)
ds.Tables.Add(dt1)

cmd.CommandText = q2
rd = cmd.ExecuteReader()
dt2.Load(reader)
ds.Tables.Add(dt2)

.... and so on for the other queries ....

Upvotes: 1

Related Questions