Filling Dataset from OleDBDataAdaptor - Unspecified Error

[rvermeulen]

I'm trying to connect to an EMPLOYEES table in an XP Access DB. I was able to earlier connect to a different table and return data. However, when I try to create an OleDBDataAdapter and fill the Dataset to be used to bind to a datagrid, I get an: Unspecified Error: E_FAIL(0x8000405) exception.

 

I'm not sure what I'm doing wrong, seems pretty straightforward.

Here is the web.config file where I set my connection string:

 

<appSettings>

<add key="ConnectionString" value="Provider = Microsoft.jet.oledb.4.0;data source=c:\InetPub\wwwroot\ASPSample\ASPSample.mdb" />

 

Here is my code I'm running in the Page_Load:

conn = New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("ConnectionString")

Dim SQL as string = "select UserID, FirstName, LastName, Address, City, State, ZipCode, HireDate, Position from EMPLOYEES"

Dim empDA as new OleDB.OleDbDataAdapter(SQL, conn)
Dim ds as new DataSet()

empDA.Fill (ds, "employees")
EmployeeGrid.DataSource = ds.Tables("employees").DefaultView
EmployeeGrid.DataBind()

The code fails on the Fill statement. Can anyone please help me. I'm having trouble figuring this out.

 

Thanks in advance.

Rick

Edited April 2, 2003 by Robby

[Robby]

try this...

I'm assigning the conn string locally only for this test.

dim conn as string = "Provider = Microsoft.jet.oledb.4.0;" & _
          "data source=c:\InetPub\wwwroot\ASPSample\ASPSample.mdb" 

Dim SQL as string = "select UserID, FirstName, LastName, " & _
     "Address, City, State, ZipCode, HireDate, Position from EMPLOYEES"

Dim empDA as new OleDB.OleDbDataAdapter(SQL, conn)
Dim ds as new DataSet()

empDA.Fill(ds)
ds.Tables(0).TableName = "employees"
EmployeeGrid.DataSource = ds
EmployeeGrid.DataMember = "employees"

[rvermeulen]

Robbie,

I'm still getting the same error. However, check this out. I took one of your suggestions from a different forum and tried to drag the connection to the webform. The connection gets established and the test succeeds. However, when I go through the OleDbDataAdapter wizard, it fails to generate the SELECT statement when it uses the column names in the select statement. If I make the select statement, Select * from EMPLOYEES, it creates successfully. However, it won't stay like this, it keeps going back to the specific column names.

 

Do you know why it wants to do that?

 

Rick

[Robby]

SO this works? ... "Select * from EMPLOYEES"

 

and when you specify fields it doesn't?

 

If so, then you may have miss-spelled a field name.

[rvermeulen]

Robby,

 

Thanks so much for all your help. Turns out that it chokes on the column Position. I switched it to Title instead and now I can select the individual columns I want and it works fine. I guess Position must be a keywork in Access?

 

Now that I have my data bound to the grid, how can I set the column widths so that the data doesn't wrap, etc. Do you have to do that programmatically, or can that be done at design time?

 

Thanks again.

Rick

[Robby]

Look here for a runtime solution http://www.xtremedotnettalk.com/showthread.php?s=&threadid=70325

 

I never did it at design time but I'm sure it's possible.