473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 5155
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Kulnor | last post by:
My hosting company upgraded yesterday to pHp 4.3.10. Since then, all my INSERT/UPDATE/DELET query on our MS-SQL database seem to fail. No specific error message is returned by...
4
by: Not Me | last post by:
Hi, I have a stored procedure, that works perfectly when run from the query analyser, however if I run it through access vba, (using exec) I get a runtime error 208: invalid object name...
1
by: js | last post by:
I am using SQL Server 2000. I am getting the following error when executing the following query. The query joins a view .dbx.dbo.vwreports that resides on a linked server. I can sort on fields...
0
by: Davy Faassen | last post by:
Hi, In the CLI guide and reference i've read about the possibility of getting status information when using arrays to input parameter values. This i've achieved as described with the...
3
by: Neil Hindry | last post by:
I wonder if you can help me. I have setup an address-book database in Access XP. I have the first name & surname as separate fields. As I wanted to sort my database by surname and then by first...
0
by: Neil Robbins | last post by:
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...
6
by: Not4u | last post by:
Hello Config : SQL 2000 on WIN 2000 (IIS 5.0) In my ASP page for some queries i have this error : Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired
8
by: Daz | last post by:
Hi everyone. I was faced with the choice of whether my problem is indeed a PHP problem or a MySQL. I have decided it's a PHP problem as I don't experience the same problem when I execute the...
4
by: raghuvendra | last post by:
Hi I have a jsp page with 4 columns: namely Category name , Category order, Input field and a submit button. All these are aligned in a row. And Each Category Name has its corresponding Category...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.