使用数据集查询多个结果集时出错 [英] error in query for multiple result set using a dataset

问题描述

大家好，

我正在经历大量关于如何填充数据集的资源。当我尝试使用4个select语句填充创建的数据集时，我遇到了一些错误。

但我的sql使用where子句，我看到的其他语句选择某些列并使用半列转到另一个select语句。



现在，当我在两个sql语句之间包含分号并尝试运行时发生错误，告诉我在SQL语句结束后找到字符。



当我省略半冒号时，另一个错误是查询表达式中出现语法错误。是否有办法解决问题？



感谢您的帮助。

Hi all,
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.

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 & "'"

推荐答案

数据集需要返回一组数据。您的SQL查询（如果在SSMS中运行）将返回四个单独的结果集。



您需要使用加入以合并所有这些信息...例如这个查询

A dataset expects a single set of data to be returned. Your sql query (if run in SSMS) would return four separate sets of results.

You need to use join to amalgamate all this information ... this query for example

SELECT [description],
[material number],[material type],[unit],[quantity],[rate],[cost/unit],
[labour number],[labour title],[quantity],[utilization factor],[indexed hourly cost],[total hourly cost] ,
[equipment number],[equipment type],[quantity],[utilization factor],[hourly rental rate],[total rental rate]
FROM [project]
INNER JOIN [work_item_material_cost] ON [work_item_material_cost].[project number]=[project].[project number]
INNER JOIN [work_item_labor_cost] ON [work_item_labor_cost].[project number]=[project].[project number]
INNER JOIN [work_item_equipment_cost] ON [work_item_equipment_cost].[project number]=[project].[project number]
WHERE [project].[project number]='" & str & "'"

其他一些需要考虑的事情

1）使用这样的字符串连接会让你容易受到SQL注入 - 使用参数代替 - 这是一个例子http://www.dotnetperls.com/sqlparameter [ ^ ]



2）考虑使用表别名来缩短你必须做的输入量 - 使用表别名 [ ^ ]



3）如果可能的话，避免在表名和列名中使用空格 - 通常认为避免它们是一种好习惯，它也不需要用方形包围列名称括号[]



查看Christian Graus关于CodeProject的文章以获得进一步的帮助 - 例如 SQL Wizardry第一集 - 加入 [ ^ ] [/ EDIT]



OP指出他们使用Access作为后端数据库...

1）参数仍然可以使用，例如 http://stackoverflow.com/questions/2930551/query-ms-access- database-in-vb-2008 [ ^ ]（注意使用OLEDbConnection等）



2）连接应该还可以 - 参见 http://msdn.microsoft.com/en-us/library/office/bb208854（ⅴ = office.12）.aspx [ ^ ]



3）作为最佳实践，这代表任何RDBMS

Some other things to consider
1) Using string concatenation like this leaves you vulnerable to SQL injection - use Parameters instead - here's an example http://www.dotnetperls.com/sqlparameter[^]

2) Consider the use of table aliases to shorten the amount of typing you have to do - Using table aliases[^]

3) If possible avoid using spaces in table and column names - it's usually considered good practice to avoid them and it also negates the need to surround the column name with square brackets []

Have a look at the articles on CodeProject by Christian Graus for further help - e.g. SQL Wizardry Episode One - Joins[^] [/EDIT]

OP has pointed out that they are using Access as the backend database...
1) Parameters can still be used e.g. http://stackoverflow.com/questions/2930551/query-ms-access-database-in-vb-2008[^] (Note the use of OLEDbConnection etc)

2) Joins should still be ok - see http://msdn.microsoft.com/en-us/library/office/bb208854(v=office.12).aspx[^]

3) As a best practice this stands for any RDBMS

这篇关于使用数据集查询多个结果集时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！