SELECT Queries with Multiple Tables

[kaybenleroll]

Hello, I'm using VB.NET to draw data from an Access Database. However, I get an error whenever I try to use any type of query which is not a simple

SELECT * FROM (table)

Specifically, I am trying to run the following SQL code:

SELECT date, Table1.close AS prod1, Table2.close AS prod2
FROM Table1, Table2
WHERE Table1.date = Table2.date

I use the following code to fill the datatable:

Dim myConnect As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=vol_engine.mdb")
Dim myAdapter As OleDbDataAdapter = New OleDbDataAdapter(SQLCommand, myConnect)
Dim myDataSet As DataSet = New DataSet()

Try
myConnect.Open()
myAdapter.Fill(myDataSet, "myData")

...

The myAdapter.Fill line throws an exception for any type of query which is not of the basic form:

SELECT * FROM (Table)

Even queries like

SELECT date, open, close FROM Table1

are causing it to throw an exception.

Any help or enlightenment would be greatly appreciated.

Thank you.

 

[vis781]

I think that you will find that the word 'date' is the problem here. It's a key word in access and it doesn't like it when used in queries. You should try to avoid using key words like that if possible but if you must then enclose them in square brackets..

[date]

 

[fomhoire]

hi the code you're trying to run the select date needs an associated table eg.

 SELECT [COLOR=Red]Table1.[/COLOR]date, Table1.close AS prod1, Table2.close AS prod2
FROM Table1, Table2
WHERE Table1.date = Table2.date

if thats not the problem could you send the error message.
g

 

[kaybenleroll]

Thanks for the quick response.

Unfortunately, I am still getting the same error. I tried to run the query

SELECT [date], open, close FROM Table1

and I still get the same error exception being thrown.

I will quote it here in full:

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Volatility_Engine.frmRiskApplication.CalculateVarCovar(String Product1, String Product2) in F:\Work\Ranelagh Capital\Volatility Engine\Volatility Engine\Form2.vb:line 105

Thanks for all help again.

 

[vis781]

Not sure about the other two words, they may also be keywords. Also have you checked to make sure that you have the field names spelled correctly with the correct case?

 

[fomhoire]

yep it's key word conflict try []'ing open and close.

 

[kaybenleroll]

Brilliant!

Thanks very much, the keyword thing never occurred to me and it was driving me crazy.

Hopefully it will work for the more complicated queries now.