在vb.net中执行make table查询时出现问题 [英] Problem executing a make table query in vb.net

问题描述

我正在尝试在我的vb.net程序中执行make table查询。我正在访问的数据库是一个Access 2000格式的数据库。我正在使用的SQL代码已经从在Access的

设计视图中设计查询的SQL View中剪切并粘贴了
。


有问题的代码如下，我为SQL的长度道歉

声明


Dim SqlComText As String =" SELECT TblCentreSession .ID，

TblCentreSession.Session AS [SessionID]，TblSession.SessionYear AS [Session

Year]，TblSession.SessionSeason AS [Session Season]，TblCentre.CentreName AS

[中心名称]，TblExaminer.ExaminerCode AS [Examiner Code]，

TblExaminer.ExaminerSName AS [Examiner Surname]，TblExaminer.ExaminerMName

AS [Examiner Middle Name]，TblExaminer.ExaminerFName AS [Examiner First

Name]，TblSyllabus.SyllabusName AS [Syllabus Name]，

CLng（[TblCandidate]。[CandidateID] ）AS [候选人ID]，TblCandidate.SName AS

[候选姓氏]，TblCandidate.MName AS [候选中间名]，

TblCandid ate.FName AS [候选人名字]，CLng（[TblSubject]。[SubjectID]）

AS [主题ID]，TblSubject.SubjectName AS [主题名称]，

CLng（[TblGrade]。[GradeID]）AS [等级ID]，TblGrade.GradeName AS [等级

名称]，TblExamResults.Result，TblExamResults.AdjResult1 AS [调整后的结果

1]，TblExamResults.AdjResult2 AS [调整后的结果2]，tblNote.Notes AS

[调整说明] INTO [结果表] IN''" &安培; ChosenFilename& "''FROM FROM

tblNote RIGHT JOIN（（（（（（（TblCentreSession LEFT JOIN TblCentre ON

TblCentreSession.Centre = TblCentre.CentreCode）LEFT JOIN TblSession ON

TblCentreSession.Session = TblSession.SessionID）LEFT JOIN TblSyllabus ON

TblCentreSession.Syllabus = TblSyllabus.SyllabusID）LEFT JOIN TblExaminer ON

TblCentreSession.Examiner = TblExaminer。 ExaminerCode）RIGHT JOIN

（（（TblCandidate RIGHT JOIN TblExamResults ON TblCandidate.CandidateID =

TblExamResults.CandidateID）LEFT JOIN TblSubject ON TblExamResults.Subject =

TblSubject.SubjectID）LEFT JOIN TblGrade ON TblExamResults.Grade =

TblGrade.GradeID）ON TblCentreSession.ID = TblExamResults.CentreSession）ON

tblNote.ExamID = TblExamResults。 ID WHERE（（（TblCentreSession.Session）="&

selectedSessionID&"）AND（（TblExamResults.Result）不为空且

（TblExamResults。结果）> 0））按TblCentr排序eSession.ID"


Dim cnn1作为新的OleDb.OleDbConnection（" Provider = Microsoft.Jet.OLED B.4.0; Jet

OLEDB：系统数据库= QUOT; &安培; systemdblocation& ;数据来源= &

examdblocation& " ;; User ID =" &安培;用户&英寸;密码=" &安培; PWord）


Dim cmd1作为新的OleDb.OleDbCommand

cnn1.Open（）


Dim runningcount As Integer = 0


尝试

使用cmd1

.Connection = cnn1

.CommandText = SqlComText < br $>
.ExecuteScalar（）

结束

Catch ex As Exception

MsgBox（ex.ToString）

结束尝试


cnn1.Close（）


我尝试过执行它但每次收到以下错误：


" System.Data.OleDb.OleDbException：IErrorInfo.GetDescription失败，

E_FAIL（0x80004005）。

at System .Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling（Int32

hr）



System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult（tagDBPARAMS

dbParams，Object& executeResult）

at System.Data.OleDb.OleDbCommand.ExecuteCommandText（Object&

executeResult）
$ System.Data.O上的b $ b leDb.OleDbCommand.ExecuteCommand（Comm andBehavior

behavior，Object& executeResult）

在System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al（CommandBehavior

行为，字符串方法）

在System.Data.OleDb .OleDbCommand.ExecuteScalar（）

at MYDB.ExportForm.btnExport_Click（Object sender，EventArgs e）

C：\ Document and Settings \Administrator \ My Documents \视觉工作室

Projects\myDB\myDB\ExportForm.vb：第146行;


任何帮助，了解我在做什么错误的，或说明如何更好地做这件事我会非常感激。

I am trying to execute a make table query in my vb.net program. The db that
I am accessing is an Access 2000 format db. The SQL code that I am using has
been cut and pasted from the SQL View having designed the query in the
design view of Access.

The code in question is below, I apologise for the length of the SQL
statement

Dim SqlComText As String = "SELECT TblCentreSession.ID,
TblCentreSession.Session AS [SessionID], TblSession.SessionYear AS [Session
Year], TblSession.SessionSeason AS [Session Season], TblCentre.CentreName AS
[Centre Name], TblExaminer.ExaminerCode AS [Examiner Code],
TblExaminer.ExaminerSName AS [Examiner Surname], TblExaminer.ExaminerMName
AS [Examiner Middle Name], TblExaminer.ExaminerFName AS [Examiner First
Name], TblSyllabus.SyllabusName AS [Syllabus Name],
CLng([TblCandidate].[CandidateID]) AS [Candidate ID], TblCandidate.SName AS
[Candidate Surname], TblCandidate.MName AS [Candidate Middle Name],
TblCandidate.FName AS [Candidate First Name], CLng([TblSubject].[SubjectID])
AS [Subject ID], TblSubject.SubjectName AS [Subject Name],
CLng([TblGrade].[GradeID]) AS [Grade ID], TblGrade.GradeName AS [Grade
Name], TblExamResults.Result, TblExamResults.AdjResult1 AS [Adjusted Result
1], TblExamResults.AdjResult2 AS [Adjusted Result 2], tblNote.Notes AS
[Adjustment Notes] INTO [Results Table] IN ''" & ChosenFilename & "'' FROM
tblNote RIGHT JOIN (((((TblCentreSession LEFT JOIN TblCentre ON
TblCentreSession.Centre = TblCentre.CentreCode) LEFT JOIN TblSession ON
TblCentreSession.Session = TblSession.SessionID) LEFT JOIN TblSyllabus ON
TblCentreSession.Syllabus = TblSyllabus.SyllabusID) LEFT JOIN TblExaminer ON
TblCentreSession.Examiner = TblExaminer.ExaminerCode) RIGHT JOIN
(((TblCandidate RIGHT JOIN TblExamResults ON TblCandidate.CandidateID =
TblExamResults.CandidateID) LEFT JOIN TblSubject ON TblExamResults.Subject =
TblSubject.SubjectID) LEFT JOIN TblGrade ON TblExamResults.Grade =
TblGrade.GradeID) ON TblCentreSession.ID = TblExamResults.CentreSession) ON
tblNote.ExamID = TblExamResults.ID WHERE (((TblCentreSession.Session)=" &
chosenSessionID & ") AND ((TblExamResults.Result) Is Not Null And
(TblExamResults.Result)>0)) ORDER BY TblCentreSession.ID"

Dim cnn1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Jet
OLEDB:System Database=" & systemdblocation & ";Data Source=" &
examdblocation & ";User ID=" & User & ";Password=" & PWord)

Dim cmd1 As New OleDb.OleDbCommand
cnn1.Open()

Dim runningcount As Integer = 0

Try
With cmd1
.Connection = cnn1
.CommandText = SqlComText
.ExecuteScalar()
End With
Catch ex As Exception
MsgBox(ex.ToString)
End Try

cnn1.Close()

I have tried executing it but each time I get the following error:

"System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with
E_FAIL(0x80004005).
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteScalar()
at MYDB.ExportForm.btnExport_Click(Object sender, EventArgs e) in
C:\Documents and Settings\Administrator\My Documents\Visual Studio
Projects\myDB\myDB\ExportForm.vb:line 146;"

Any help in understanding what I am doing wrong, or instructions on how to
do this better would me greatly appreciated.

推荐答案

嗨Neil，

首先，我想确认一下我对你的问题的理解。

从你的描述中，我知道你执行了一个SQL语句

对Access 2000数据库，你有错误。

我完全理解你了吗？如果有任何我误解的地方，请

随时告诉我。


您能否在发生错误时发布确切的错误消息？


您也可以尝试直接在Access中运行SQL语句，看看

你会得到什么。


请申请我上面的建议让我知道它是否有助于解决你的b $ b问题。

祝你好运，


Peter Huang
Microsoft在线合作伙伴支持


安全！ - www.microsoft.com/security

此帖子原样是按原样提供的。没有保证，也没有授予任何权利。

Hi Neil,

First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you execute a SQL statement
against a Access 2000 database, you got error.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

Can you post the exact error message when the error occurs?

Also you may try to run the SQL statement in Access directly to see what
you will get.

Please apply my suggestion above and let me know if it helps resolve your
problem.
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

嗨Neil，


首先，我想要确认我对你的问题的理解。

从你的描述中，我知道你对Access 2000数据库执行了一个SQL语句

，你得到了错误。

我完全理解你了吗？如果有任何我误解的地方，请

随时告诉我。


您能否在发生错误时发布确切的错误消息？


您也可以尝试直接在Access中运行SQL语句，看看

你会得到什么。


请申请我上面的建议让我知道它是否有助于解决你的b $ b问题。

祝你好运，


Peter Huang
Microsoft在线合作伙伴支持


安全！ - www.microsoft.com/security

此帖子原样是按原样提供的。没有保证，也没有赋予任何权利。

Hi Neil,

First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you execute a SQL statement
against a Access 2000 database, you got error.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.

Can you post the exact error message when the error occurs?

Also you may try to run the SQL statement in Access directly to see what
you will get.

Please apply my suggestion above and let me know if it helps resolve your
problem.
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

嗨Neil，


你试图成就什么，你想要选择一些东西，但是它会停留

无处。

而且当你第一次尝试时，首先选择简单的选择

" ;从mytable中选择*。


我也看到你在msdn上直接使用了这个样本。

我认为这是一个非常古老的样本。 />

在我看来你想做的就是

myaggregateValue = myCommand.ExecuteScalar（）


但是我在怀疑这是否是你想要的，所以告诉你想要什么

archieve


Cor

Hi Neil,

What you try to archieve, you want to select something however it stays
nowhere.
And than when you try things the first time, start with a simple select as
"Select * from mytable.

Also I saw you took this direct form a sample on msdn.
I think it is a very old sample.

In my opinion you want to do somenting as
myaggregateValue = myCommand.ExecuteScalar()

However I am in doubt if this is what you want, so tell what you want to
archieve

Cor

这篇关于在vb.net中执行make table查询时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！