DB Connection Error
Here is my code:
Dim
DBConnAs OleDb.OleDbConnectionDim DBCommAs OleDb.OleDbCommandDim DBReaderAs OleDb.OleDbDataReaderDBConn =New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db.mdb")
DBConn.Open()
strSQL ="Select FirstName, LastName From Names Order By LastName"
DBComm =
New OleDb.OleDbCommand(strSQL, DBConn)DBReader= DBComm.ExecuteReader()
While DBReader.Read()
strNames = DBReader("LastName") &" ," & DBReader("FirstName")
EndWhile
DBReader.Close()
DBConn.Close()
However I get this error
{"IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."}
I think the problem may be that the SQL Statement is using a reserved word
so try the following statements
Select FirstName, LastName From [Names] Order By LastName
Select [FirstName], [LastName] From [Names] Order By [LastName]
See if this helps.
It may be that Names causes the naming conflict, wrapping it in [ ] may make the issue go away but be careful with you field and table names that they dont match any other reserved words.
I'm in the same situation, and bracketing the fields in the SELECT statement does not do any good.
My example follows:
(further up in the class)
private const string connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\"db.mdb\";";
(in the method)
OleDbConnection conn = new OleDbConnection(connStr);
conn.Open(); // connection does open
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "SELECT [PositionID], [PositionDescription] FROM [Positions] WHERE [PositionID] NOT IN (SELECT DISTINCT [PositionID] FROM [StaffPosition]";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Notes:
This is based on a database with more tables than these, but these are the two you need to know about for the sample code:
Table: Position
Field Name Data Type
PositionID Autonumber (Long Integer)
PositionDescription Text(200)
Table: StaffPosition
FieldName Data Type
PositionID Long Integer
StaffID Long Integer
Also, note that when cut & paste into the queries section of Access, the query performs as expected.
Any ideas or workarounds, anyone?
