Problem connecting to an MS Access Database [modified]
-
Greetings! I'm trying to connect to a database and read certain fields from it. I'm unable to read any fields from it and would appreciate your help debugging/fixing the problem. Here is what I have: 1. A file that acts has the database interface code:
public class AppDatabaseInterface { // Directory names private string m_cstrDefaultdbDir = "C:\\"; // File names private const string m_cstrDBFileName = "DefaultDatabase.mdb"; private System.Data.OleDb.OleDbConnection m_dbConn; public AppDatabaseInterface() { // Determine the App Root directory when this object is constructed System.Reflection.Assembly assm = System.Reflection.Assembly.GetAssembly(System.Type.GetType("AppDatabase.AppDatabaseInterface")); m_cstrDefaultdbDir = System.IO.Path.GetDirectoryName(assm.CodeBase.ToString()); m_cstrDefaultdbDir = m_cstrDefaultdbDir.Remove(0, 6); // Removes the leading file:\ header from the path. OpenDatabase(); } private void OpenDatabase() { lock (this) { // If the database connection hasn't been initialized, create it. // If it exists and is currently connected, close the connection // as we are about to establish a new connection. if (m_dbConn == null) { m_dbConn = new OleDbConnection(); } else { if (m_dbConn.State != ConnectionState.Closed) { m_dbConn.Close(); } } // Create the new connection string and try and attach to the new database. try { m_dbConn.ConnectionString = string.Format( @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}\{1};", m_cstrDefaultdbDir, m_cstrDBFileName); } catch { Console.WriteLine("Could not open database"); } m_dbConn.Open(); } }2. The "Makes" Method Here's the "Makes" method in the AppDatabaseInterface.cs filepublic string[] Makes { get { OleDbDataReader dbr = null; ArrayList Makes = new ArrayList(); OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM {0};", "Speakers"), m_dbConn); dbr = cmd.ExecuteReader(); // Read each record one at a time ... while (dbr.Read()) { // Load our arrays with the Field names // and values. NOTE: Reloading the field // names over and over again is probably // redundant, but I'm not sure that the order // column names are returned is fixed. for (int it = 0; -
Greetings! I'm trying to connect to a database and read certain fields from it. I'm unable to read any fields from it and would appreciate your help debugging/fixing the problem. Here is what I have: 1. A file that acts has the database interface code:
public class AppDatabaseInterface { // Directory names private string m_cstrDefaultdbDir = "C:\\"; // File names private const string m_cstrDBFileName = "DefaultDatabase.mdb"; private System.Data.OleDb.OleDbConnection m_dbConn; public AppDatabaseInterface() { // Determine the App Root directory when this object is constructed System.Reflection.Assembly assm = System.Reflection.Assembly.GetAssembly(System.Type.GetType("AppDatabase.AppDatabaseInterface")); m_cstrDefaultdbDir = System.IO.Path.GetDirectoryName(assm.CodeBase.ToString()); m_cstrDefaultdbDir = m_cstrDefaultdbDir.Remove(0, 6); // Removes the leading file:\ header from the path. OpenDatabase(); } private void OpenDatabase() { lock (this) { // If the database connection hasn't been initialized, create it. // If it exists and is currently connected, close the connection // as we are about to establish a new connection. if (m_dbConn == null) { m_dbConn = new OleDbConnection(); } else { if (m_dbConn.State != ConnectionState.Closed) { m_dbConn.Close(); } } // Create the new connection string and try and attach to the new database. try { m_dbConn.ConnectionString = string.Format( @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}\{1};", m_cstrDefaultdbDir, m_cstrDBFileName); } catch { Console.WriteLine("Could not open database"); } m_dbConn.Open(); } }2. The "Makes" Method Here's the "Makes" method in the AppDatabaseInterface.cs filepublic string[] Makes { get { OleDbDataReader dbr = null; ArrayList Makes = new ArrayList(); OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM {0};", "Speakers"), m_dbConn); dbr = cmd.ExecuteReader(); // Read each record one at a time ... while (dbr.Read()) { // Load our arrays with the Field names // and values. NOTE: Reloading the field // names over and over again is probably // redundant, but I'm not sure that the order // column names are returned is fixed. for (int it = 0;Are you getting a run-time error or are you just not seeing anything displayed when you are trying to populate your combobox? I haven't analyzed your code thoroughly yet, but try putting a breakpoint in the for loop that populates "Makes" from the db table to see if you're actually getting data out of it. If you ARE getting the correct data out of the table, follow the debugger down until you lose it. Reply back with what you find. (I don't have .NET available right now so I can't plug your code into a project to debug it yourself. ....So help me help you......help me......help you lol)
-
Are you getting a run-time error or are you just not seeing anything displayed when you are trying to populate your combobox? I haven't analyzed your code thoroughly yet, but try putting a breakpoint in the for loop that populates "Makes" from the db table to see if you're actually getting data out of it. If you ARE getting the correct data out of the table, follow the debugger down until you lose it. Reply back with what you find. (I don't have .NET available right now so I can't plug your code into a project to debug it yourself. ....So help me help you......help me......help you lol)
Thanks for your reply! There is no run-time error. The code compiles and runs just fine. The form comes up but the combo box has nothing in it. I've stepped through the code to make sure that the database connection sees the Access file.
-
Thanks for your reply! There is no run-time error. The code compiles and runs just fine. The form comes up but the combo box has nothing in it. I've stepped through the code to make sure that the database connection sees the Access file.
...I think your problem is that you are executing a query rather and not storing it in an OleDbReader object. (this is all off the top of my head, mind you) If you are in VS, use Intellisense to see what db.ExecuteReader() returns...I think it's an OleDbReader object. You need to store the results in one and read from it instead of your db connection. I don't believe that "db" as you have defined it will work the way you are wanting to to work. You need a companion class to hold the results of a query and read from it. I can help you out with that, but I don't have the files handy. I'll get them real quick and I'll be back.
-
Thanks for your reply! There is no run-time error. The code compiles and runs just fine. The form comes up but the combo box has nothing in it. I've stepped through the code to make sure that the database connection sees the Access file.
Yup...I was right. You need to store your "db.ExecuteReader" results in an OleDbReader object. I referred to a project that I'm currently working on and I think I might be able to help you out a little bit. I've only included the relevant portions of my code, but I accomplished exactly what you are trying to do. I wanted to populate a combobox with the contents of my "category" table. I'm not finished copying (Having to write it down by hand and bring it over...my development computer is not "plugged in" to my network) I'm going to go back, get more code and place it here in an edit. I'll let you know at the end of this post what I'm going to be bringing back next post ...a little bit of explanation. I have 2 classes (clsDatabase & clsQueryResults) that provide a layer of abstraction away from ADO.NET. This allows me to separate my application logic from database management commands. The form code:
private void form_Load(object sender, System.EventArgs e)
{
ArrayList mCategoriesToDisplay = Manager.getAllCategories();
foreach(clsManager.clsCategory cat in mCategoriesToDisplay)
{
this.cbCategory.Items.Add(cat.Title);
}
}"clsManager" code ...I use "clsManager" as a mediator to perform network and assembly linkage. If I were developing a desktop app, this would not be needed...just thought it might be helpful to explain why this layer of abstraction is even present
clsCategoryList mCategoryList;
.
.
.
//in the constructor, mCategoryList = new clsCategoryList();
public ArrayList getAllCategories()
{
return mCategoryList.getAllCategories();
}"clsCategoryList" is basically my own way of holding my category objects. getAllCategories returns an ArrayList of clsCategory objects
public ArrayList getAllCategories()
{
string sql = "Select * From Category";
clsQueryResults resultSet = new clsQueryResults();
resultSet.Open(mDatabase, sql);
while(!resultSet.EOF)
{
clsCategory category = new clsCategory();
category.RestoreStateFromQuery(resultSet);
Categories.Add(category);
resultSet.NextRecord(); //(Move Next)
}
return Categories
}The code I will bring you in my next post will be what happens when we move closer to ADO.NET as well as what happens inside the actual "clsCategory" object. ...clsCategory is the only place along the "data chain" that can actually interface with a clsDatabase object and a clsDatabase object is the only thing that can actually communicate with Access. inside "cl
-
Yup...I was right. You need to store your "db.ExecuteReader" results in an OleDbReader object. I referred to a project that I'm currently working on and I think I might be able to help you out a little bit. I've only included the relevant portions of my code, but I accomplished exactly what you are trying to do. I wanted to populate a combobox with the contents of my "category" table. I'm not finished copying (Having to write it down by hand and bring it over...my development computer is not "plugged in" to my network) I'm going to go back, get more code and place it here in an edit. I'll let you know at the end of this post what I'm going to be bringing back next post ...a little bit of explanation. I have 2 classes (clsDatabase & clsQueryResults) that provide a layer of abstraction away from ADO.NET. This allows me to separate my application logic from database management commands. The form code:
private void form_Load(object sender, System.EventArgs e)
{
ArrayList mCategoriesToDisplay = Manager.getAllCategories();
foreach(clsManager.clsCategory cat in mCategoriesToDisplay)
{
this.cbCategory.Items.Add(cat.Title);
}
}"clsManager" code ...I use "clsManager" as a mediator to perform network and assembly linkage. If I were developing a desktop app, this would not be needed...just thought it might be helpful to explain why this layer of abstraction is even present
clsCategoryList mCategoryList;
.
.
.
//in the constructor, mCategoryList = new clsCategoryList();
public ArrayList getAllCategories()
{
return mCategoryList.getAllCategories();
}"clsCategoryList" is basically my own way of holding my category objects. getAllCategories returns an ArrayList of clsCategory objects
public ArrayList getAllCategories()
{
string sql = "Select * From Category";
clsQueryResults resultSet = new clsQueryResults();
resultSet.Open(mDatabase, sql);
while(!resultSet.EOF)
{
clsCategory category = new clsCategory();
category.RestoreStateFromQuery(resultSet);
Categories.Add(category);
resultSet.NextRecord(); //(Move Next)
}
return Categories
}The code I will bring you in my next post will be what happens when we move closer to ADO.NET as well as what happens inside the actual "clsCategory" object. ...clsCategory is the only place along the "data chain" that can actually interface with a clsDatabase object and a clsDatabase object is the only thing that can actually communicate with Access. inside "cl
Wow... thanks, mate! You're a huge help... I really appreciate this! I just figured out that making the following change in my AppDatabaseInterface.cs file did the trick for me:
if (!found) alMakes.Add(make);but your approach on the whole seems better and I'm going to look into it. Thank you very much! N. -
Wow... thanks, mate! You're a huge help... I really appreciate this! I just figured out that making the following change in my AppDatabaseInterface.cs file did the trick for me:
if (!found) alMakes.Add(make);but your approach on the whole seems better and I'm going to look into it. Thank you very much! N.You're not using an object oriented design are you? I assume from your variable names that you're doing something with sound equipment?? Typically, you would do something like the following
public class clsSpeaker
{
private string mMake;
private string mModel;//your object constructor
public Speaker()
{
mMake = "";
mModel = "";
}public void RestoreStateFromQuery(clsResultSet rs)
{
this.mMake = rs.GetColumnValue("SpeakerMake");
this.mModel = rs.GetColumnValue("SpeakerModel");
//Where SpeakerMake and SpeakerModel are tables in your database
}
public string Make
{
get
{
return mMake;
}
}public string Model
{
get
{
return mModel;
}
}
}...so now, you can just instantiate your clsSpeaker class inside your UI, Deserialize it from your database query, and then do the command that I included in my previous post labeled as "form code." It makes the code much more legible and bug tracing is a lot easier because your code is properly compartmentalized. btw, put your code in "pre" tags instead of "code" tags...It's a lot easier to read. (Pre tags are what I used to post my code in this thread)