Problem executing a make table query in vb.net

[Neil Robbins]

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 TblCentreSessio n.ID,
TblCentreSessio n.Session AS [SessionID], TblSession.Sess ionYear AS [Session
Year], TblSession.Sess ionSeason AS [Session Season], TblCentre.Centr eName AS
[Centre Name], TblExaminer.Exa minerCode AS [Examiner Code],
TblExaminer.Exa minerSName AS [Examiner Surname], TblExaminer.Exa minerMName
AS [Examiner Middle Name], TblExaminer.Exa minerFName AS [Examiner First
Name], TblSyllabus.Syl labusName AS [Syllabus Name],
CLng([TblCandidate].[CandidateID]) AS [Candidate ID], TblCandidate.SN ame AS
[Candidate Surname], TblCandidate.MN ame AS [Candidate Middle Name],
TblCandidate.FN ame AS [Candidate First Name], CLng([TblSubject].[SubjectID])
AS [Subject ID], TblSubject.Subj ectName AS [Subject Name],
CLng([TblGrade].[GradeID]) AS [Grade ID], TblGrade.GradeN ame 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 (((((TblCentreS ession LEFT JOIN TblCentre ON
TblCentreSessio n.Centre = TblCentre.Centr eCode) LEFT JOIN TblSession ON
TblCentreSessio n.Session = TblSession.Sess ionID) LEFT JOIN TblSyllabus ON
TblCentreSessio n.Syllabus = TblSyllabus.Syl labusID) LEFT JOIN TblExaminer ON
TblCentreSessio n.Examiner = TblExaminer.Exa minerCode) RIGHT JOIN
(((TblCandidate RIGHT JOIN TblExamResults ON TblCandidate.Ca ndidateID =
TblExamResults. CandidateID) LEFT JOIN TblSubject ON TblExamResults. Subject =
TblSubject.Subj ectID) LEFT JOIN TblGrade ON TblExamResults. Grade =
TblGrade.GradeI D) ON TblCentreSessio n.ID = TblExamResults. CentreSession) ON
tblNote.ExamID = TblExamResults. ID WHERE (((TblCentreSes sion.Session)=" &
chosenSessionID & ") AND ((TblExamResult s.Result) Is Not Null And
(TblExamResults .Result)>0)) ORDER BY TblCentreSessio n.ID"

Dim cnn1 As New OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;Jet
OLEDB:System Database=" & systemdblocatio n & ";Data Source=" &
examdblocation & ";User ID=" & User & ";Password= " & PWord)

Dim cmd1 As New OleDb.OleDbComm and
cnn1.Open()

Dim runningcount As Integer = 0

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

cnn1.Close()

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

"System.Data.Ol eDb.OleDbExcept ion: IErrorInfo.GetD escription failed with
E_FAIL(0x800040 05).
at System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32
hr)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
behavior, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method)
at System.Data.Ole Db.OleDbCommand .ExecuteScalar( )
at MYDB.ExportForm .btnExport_Clic k(Object sender, EventArgs e) in
C:\Documents and Settings\Admini strator\My Documents\Visua l Studio
Projects\myDB\m yDB\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.

[Peter Huang]

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.

[Peter Huang]

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.

[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
myaggregateValu e = myCommand.Execu teScalar()

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

Cor

[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
myaggregateValu e = myCommand.Execu teScalar()

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

Cor

[Cor]

Hi Peter,

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

I thought he did I copied it for you, see below.

Cor

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

"System.Data.Ol eDb.OleDbExcept ion: IErrorInfo.GetD escription failed with
E_FAIL(0x800040 05).
at System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32
hr)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
behavior, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method)
at System.Data.Ole Db.OleDbCommand .ExecuteScalar( )
at MYDB.ExportForm .btnExport_Clic k(Object sender, EventArgs e) in
C:\Documents and Settings\Admini strator\My Documents\Visua l Studio
Projects\myDB\m yDB\ExportForm. vb:line 146;"

[Cor]

Hi Peter,

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

I thought he did I copied it for you, see below.

Cor

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

"System.Data.Ol eDb.OleDbExcept ion: IErrorInfo.GetD escription failed with
E_FAIL(0x800040 05).
at System.Data.Ole Db.OleDbCommand .ExecuteCommand TextErrorHandli ng(Int32
hr)
at
System.Data.Ole Db.OleDbCommand .ExecuteCommand TextForSingleRe sult(tagDBPARAM S
dbParams, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand Text(Object&
executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteCommand (CommandBehavio r
behavior, Object& executeResult)
at System.Data.Ole Db.OleDbCommand .ExecuteReaderI nternal(Command Behavior
behavior, String method)
at System.Data.Ole Db.OleDbCommand .ExecuteScalar( )
at MYDB.ExportForm .btnExport_Clic k(Object sender, EventArgs e) in
C:\Documents and Settings\Admini strator\My Documents\Visua l Studio
Projects\myDB\m yDB\ExportForm. vb:line 146;"

[Neil Robbins]

Hi Peter & Cor,

Thanks for the responses. That is the exact error message that Cor has
copied in the preceeding message. Your understanding seems correct to me
Peter. I am executing a SQL make table query statement and I get the error
message on the line '.ExecuteScalar ()'. I have also tried using
'.ExecuteNonQue ry' but I get the exact same error. The purpose of what I am
trying is to create a table that brings together data from a number of other
tables in the db and to then put this in a new database with a name of the
users choosing and at a location of the users choosing. Effectively to
export the data.

Any help is always much appreciated,

Regards,

Neil R.

[Neil Robbins]

Hi Peter & Cor,

Thanks for the responses. That is the exact error message that Cor has
copied in the preceeding message. Your understanding seems correct to me
Peter. I am executing a SQL make table query statement and I get the error
message on the line '.ExecuteScalar ()'. I have also tried using
'.ExecuteNonQue ry' but I get the exact same error. The purpose of what I am
trying is to create a table that brings together data from a number of other
tables in the db and to then put this in a new database with a name of the
users choosing and at a location of the users choosing. Effectively to
export the data.

Any help is always much appreciated,

Regards,

Neil R.

[Cor]

Hi Neil,

I understand now what you want to do, however I hate SQL.

I do not know how deep Peters knowledge is of that, however meanwhile you
can try to see if you get more information by setting this before the catch
ex as exception (you can let that stay)
\\\
Catch sqlEx As SqlException
MessageBox.Show (sqlEx.ToString )
Catch ex as Exception
.......
End try
///
Maybe you get than some better information.

Cor