Probs with reading AccessDB

Hi,

I am trying out some basic DB access using VB.NET and I am encountering unspecified errors.........

Here is the code that I am trying to run:

Sub Page_Load (Sender as Object, E as EventArgs)
Dim strConnection as String ="Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\Working\Options.mdb"
Dim strSQL as String="SELECT * FROM Names;"

Dim objConnect as new OleDbConnection(strConnection)
Dim objCommand as new OleDbCommand(strSQL, objConnect)
objConnect.Open()

Dim objReader as OleDbDataReader
objReader = objCommand.ExecuteReader()
datagrd.DataSource = objCommand.ExecuteReader()
datagrd.DataBind()

objConnect.Close()

End Sub

This is the error message I am getting.............


Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Unspecified error: E_FAIL(0x80004005)

Source Error:
Line 14:
Line 15: Dim objReader as OleDbDataReader
Line 16: objReader = objCommand.ExecuteReader()
Line 17: datagrd.DataSource = objCommand.ExecuteReader()
Line 18: datagrd.DataBind()


Source File: C:\Working\AccessDB.aspx Line: 16

Stack Trace:


[OleDbException (0x80004005): Unspecified error: E_FAIL(0x80004005)]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +154
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +57
System.Data.OleDb.OleDbCommand.ExecuteReader() +7
ASP.AccessDB_aspx.Page_Load(Object Sender, EventArgs E) in C:\Working\AccessDB.aspx:16
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +29
System.Web.UI.Page.ProcessRequestMain() +724


Can anyone please suggest what is wrong with this.........

Thanks & regds
praxs

i think its because you have a ; at the end of your SQL statement. remove that.

then add in try, catch, finally blocks so that you can actually see the error message that access/vb is kicking back like so:


I'm not sure about the catch ex as part, i think there is a different exception for sql string errors.

I found this in the MS .Net SDK [quoted] :

Note Using an Access (Jet) database as a data source for multithreaded applications, such as ASP.NET applications, is not recommended. If you must use Access as a data source for an ASP.NET application, and are unable to use an alternative such as SQL Server or MSDE, be aware that ASP.NET applications connecting to an Access database can encounter connection problems most commonly related to security permissions. For help troubleshooting connection problems using ASP.NET and an Access database, see article Q316675 "PRB: Cannot Connect to Access Database from ASP.NET" in the Microsoft Knowledge Base located at URL

If you read the article on the MS site you should get some pointers on how to hack through this issue!

Shouldn't the block:

Dim objReader as OleDbDataReader
objReader = objCommand.ExecuteReader()
datagrd.DataSource = objCommand.ExecuteReader()
datagrd.DataBind()


Read something more like:

Dim objReader as OleDbDataReader
objReader = objCommand.ExecuteReader()
datagrd.DataSource = objReader ' <--- Change here.
datagrd.DataBind()

Yes. I had this one. And it took me a good day to work out why it was doing this. And to make things hard, the error was not always in the same place.

As someone else said. remove the ; on the sql. This should not be there.

Also, this error happen when you have more then one connection open to the same access file. It is very important that you close one connection before you open another. If you say have a (for I loop or a while loop) and open a access connection. Look at a result, and based on this result then open another connection before closing the first one. Some times your code will work, sometimes it will not (that’s Access). I was walking day by day and seeing if a booking had been made. All worked ok, but if it had to walk more then 35 or so days in a while loop. The code would crash and bring up the error you are having.

Also do not forget to stop and start the IIS service. I found that after having this error. I could not connect to the database every time. If this is your live server to the world. I would do this without question.

I hope this helps

Wayne Hawkins
Network Manager
Christchurch Girls' High School