Inability to access database 1

[Titan5]

I have a hard time accessing data from SQL and MS Acess database using c#. Please help. My XP computer has MS Access 10 (XP) installed with no logon password and it also has MS SQL 7.0 client (personal) edition installed. The following is the c# code to get the MS Access table. The table has one record.

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

string source = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"User ID=Admin;Password=;" +
"Database=MyAccessFile.mdb";
string select = "SELECT RecordID FROM MyTable";
OleDbConnection cnn = new OleDbConnection(source);
cnn.Open();
OleDbCommand cmd = new OleDbCommand(select, cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
MessageBox.Show("The key field data is " + rdr.GetString(0),"Data Reader"
rdr.Close();
cnn.Close();

When running, a message showed up: "An unhandled exception of type (System.Data.OleDb.OleDbException) occurred in System.Data.dll."
The line OleDbCommand cmd = new OleDbCommand(select, cnn); is then highlighted. Then another message box showed: There is no source code available for the current location.
Then I looked into the debug output window it showed:
Unhandled Exception: System.Data.OleDb.OleDbException: Could not find installable ISAM.
Do you know what I did wrong or what I should do to fix the problem? How do I install ISAM? Thru MSAcces or Visual Studio Development Environment?

Then I changed the code and tried to get data from the sql server. I used the default user id of sa. The table also has one record. C# code:

string source = "server=(local);" +
"uid=sa;" +
"database=MyFileSQL";

string select = "SELECT RecordID FROM MyTable";
SqlConnection cnn = new SqlConnection(source);
cnn.Open();
SqlCommand cmd = new SqlCommand(select, cnn);
SqlDataReader rdr = cmd.ExecuteReader();

object o = rdr["RecordID"];
MessageBox.Show("The key field data is " + o.ToString(),"Data Reader"
rdr.Close();
cnn.Close();

When running, a message showed up: "An unhandled exception of type System.NotSupportedException occurred in System.Data.dll. Additional information: Invalid attempt to read when no data is there."
Then another message box showed: There is no source code available for the current location
object o = rdr["RecordID"]; is highlighted.

Can anybody explain to me what the problems are? I thought I did a very basic code to get data from the database files. It simply did not work. Thanks in advance for your reply.

 

[LV]

This should help:

...
...
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
string s = rdr["RecordId"].ToString();
MessageBox.Show("The key field data is " + s);
}
rdr.Close();
...
...

 

[Titan5]

I've tried a way similar to yours like rdr[0].ToString(). It failed. It looks to me that I simply could not get to the database files in MS Access database or SQL database.

 

[LV]

The error "Invalid attempt to read when no data is there" is thrown if you don't call the Read method of SqlDataReader object. The rest of the errors are just consequences of that. All this, of course, in case if your database connection is successful and you get a recordset(might be empty) back. Set a break point at ExecuteReader line, start a trace on your SQL database and make sure your SELECT statement is executed. Also, it's a good practice is to use try/catch/finally blocks in your code. For example:

try
{
// code to execute reader
}
catch(Exception err)
{
MessageBox.Show(err.Message);
}
finally
{
// perform clean-up here, if needed
}

 

[Titan5]

Thanks, LV
Your tips are very helpful. I'll remember to write error handling routine. After I added Read() and revised to the following it worked. This solved my second question. Regarding the first question, I still couldn't understand why I couldn't get to MS Access database. Any suggestion?

// SQL data access
while(rdr.Read())
MessageBox.Show("The key field data is " +
rdr.GetString(0),"Data Reader”); // the following also works.
MessageBox.Show("The key field data is " +
rdr[0].ToString(),"Data Reader"


I am posting the above code so that other people can see. Thanks again.

 

[LV]

Honestly, I have not tried Access database, been working with SQL Server.

 

[Titan5]

I've found the solution for problem one about retrieving data from MS Access database. It's trial and error thing. Not so sure about the logic behind it but it works.

Original code:
string source = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"User ID=Admin;Password=;" +
"Database=MyAccessFile.mdb";
New code:
string source = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"User ID=Admin;Password=;" +
"Data source=MyAccessFile.mdb";