468,463 Members | 1,978 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,463 developers. It's quick & easy.

Problem executing a make table query in vb.net

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.
Nov 20 '05 #1
20 4901
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.

Nov 20 '05 #2
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.

Nov 20 '05 #3
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

Nov 20 '05 #4
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

Nov 20 '05 #5
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.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;"

Nov 20 '05 #6
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.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;"

Nov 20 '05 #7
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
'.ExecuteNonQuery' 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.
Nov 20 '05 #8
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
'.ExecuteNonQuery' 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.
Nov 20 '05 #9
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
Nov 20 '05 #10
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
Nov 20 '05 #11
Hi Cor,

Thanks for this I will put the code in and see what happens. I'll post the
results to this thread.

Regards,

Neil R.
"Cor" <no*@non.com> wrote in message
news:un**************@TK2MSFTNGP12.phx.gbl...
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

Nov 20 '05 #12
Hi Cor,

Thanks for this I will put the code in and see what happens. I'll post the
results to this thread.

Regards,

Neil R.
"Cor" <no*@non.com> wrote in message
news:un**************@TK2MSFTNGP12.phx.gbl...
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

Nov 20 '05 #13
Hi Neil,

I am sorry if I have any confusion.

But have you tried to run the Query statement in Access Application
directly?
Will you get any error?

If no, you may try to simplify your SqlComText.
e.g. SqlComText="Select * from tblNote"

So that we can isolate the problem to see what cause the problem.
Please make a try and let me know the result.

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.

Nov 20 '05 #14
Hi Neil,

I am sorry if I have any confusion.

But have you tried to run the Query statement in Access Application
directly?
Will you get any error?

If no, you may try to simplify your SqlComText.
e.g. SqlComText="Select * from tblNote"

So that we can isolate the problem to see what cause the problem.
Please make a try and let me know the result.

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.

Nov 20 '05 #15
Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Session in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKeywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o
ledbidbinfo__getkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o
lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

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.

Nov 20 '05 #16
Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Session in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKeywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o
ledbidbinfo__getkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o
lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

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.

Nov 20 '05 #17
Hi Peter,

thanks for this. the query does run without a problem in Access, but not in
the .net app. This information about the word 'Session' fits perfectly
though. When I remove it the query runs without a problem.

Thankyou for your help,

Regards,

Neil R.

""Peter Huang"" <v-******@online.microsoft.com> wrote in message
news:ET**************@cpmsftngxa06.phx.gbl...
Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Session in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKeywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o ledbidbinfo__getkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

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.

Nov 20 '05 #18
Hi Peter,

thanks for this. the query does run without a problem in Access, but not in
the .net app. This information about the word 'Session' fits perfectly
though. When I remove it the query runs without a problem.

Thankyou for your help,

Regards,

Neil R.

""Peter Huang"" <v-******@online.microsoft.com> wrote in message
news:ET**************@cpmsftngxa06.phx.gbl...
Hi Neil,

In addition, after I further research, it seems that there is reserved
keywords(Session in your query statement) which may cause the problem.

Here is the list of the reserved keywords of OleDb and Access you may take
a look.

IDBInfo::GetKeywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o ledbidbinfo__getkeywords.asp

Keywords
http://msdn.microsoft.com/library/de...us/oledb/htm/o lapkeywords.asp

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

So you may also try to change your querystring as below.
"Select [Session] From Table1"

That is to say, you may need to change the keyword in the select statement
into [keyword].

You may have a try and let me know the result.

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.

Nov 20 '05 #19
Cor
Peter,

Great.

As I have said that in this thread I hate SQL and this is one of the
reasons, so many impossible reserved words

Cor
Nov 20 '05 #20
Cor
Peter,

Great.

As I have said that in this thread I hate SQL and this is one of the
reasons, so many impossible reserved words

Cor
Nov 20 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Kulnor | last post: by
4 posts views Thread by Not Me | last post: by
reply views Thread by Davy Faassen | last post: by
reply views Thread by Neil Robbins | last post: by
4 posts views Thread by raghuvendra | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.