C# Discussion Boards - CodeProject

21-Sep-06 13:17

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:

<br />
 public class AppDatabaseInterface<br />
    {<br />
<br />
        // Directory names<br />
        private string m_cstrDefaultdbDir = "C:\\";<br />
<br />
        // File names<br />
        private const string m_cstrDBFileName = "DefaultDatabase.mdb";<br />
<br />
        private System.Data.OleDb.OleDbConnection m_dbConn;<br />
<br />
        public AppDatabaseInterface()<br />
        {<br />
            // Determine the App Root directory when this object is constructed<br />
            System.Reflection.Assembly assm =<br />
                System.Reflection.Assembly.GetAssembly(System.Type.GetType("AppDatabase.AppDatabaseInterface"));<br />
            m_cstrDefaultdbDir = System.IO.Path.GetDirectoryName(assm.CodeBase.ToString());<br />
            m_cstrDefaultdbDir = m_cstrDefaultdbDir.Remove(0, 6);	// Removes the leading file:\ header from the path.<br />
<br />
            OpenDatabase();<br />
        }<br />
<br />
<br />
        private void OpenDatabase()<br />
        {<br />
            lock (this)<br />
            {<br />
                // If the database connection hasn't been initialized, create it.  <br />
                // If it exists and is currently connected, close the connection<br />
                // as we are about to establish a new connection.<br />
                if (m_dbConn == null)<br />
                {<br />
                    m_dbConn = new OleDbConnection();<br />
                }<br />
                else<br />
                {<br />
                    if (m_dbConn.State != ConnectionState.Closed)<br />
                    {<br />
                        m_dbConn.Close();<br />
                    }<br />
                }<br />
<br />
                // Create the new connection string and try and attach to the new database.<br />
                try<br />
                {<br />
                    m_dbConn.ConnectionString = string.Format(<br />
                         @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}\{1};",<br />
                         m_cstrDefaultdbDir, m_cstrDBFileName);<br />
                }<br />
                catch<br />
                {<br />
                    Console.WriteLine("Could not open database");<br />
                }<br />
                m_dbConn.Open();<br />
<br />
            }<br />
        }<br />

2. The actual UI form

<br />
public partial class frmAppDatabase: Form<br />
    {<br />
        AppDatabaseInterface m_database;<br />
<br />
        public frmSpeakerDatabase()<br />
        {<br />
            InitializeComponent();<br />
<br />
            m_database = new AppDatabaseInterface();<br />
<br />
            string[] strSpeakerMakes = m_database.Makes;<br />
<br />
            for (int i = 0; i < strSpeakerMakes.Length; i++)<br />
            {<br />
                cbSpeakerMakes.Items.Add(strSpeakerMakes[i]);<br />
            }<br />
      }<br />

What I want this portion of the code to do is to fill in the combobox with the various items from the specified field in the database. However, I see nothing.

The Access file is in the \bin\debug directory of the project.

Could you please advice me as to what is wrong?

Thanks!

Guffa21-Sep-06 20:53

21-Sep-06 20:53

TheBlindWatchmaker wrote:
However, I see nothing.

You see nothing? Not even the error message?

You are trying to use the property "Makes", but you have not created any property with that name. What do you expect to happen?

---
b { font-weight: normal; }

TheBlindWatchmaker22-Sep-06 5:15

22-Sep-06 5:15

Oops! My mistake!

Here's the "Makes" method in the AppDatabaseInterface.cs file

<br />
        public string[] Models(string make)<br />
        {<br />
            {<br />
                OleDbDataReader dbr = null;<br />
                ArrayList Models = new ArrayList();<br />
<br />
                OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM {0};", "Speakers"), m_dbConn);<br />
                //OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM {0} WHERE {1} = {2};", "Speakers", "MAKE", make), m_dbConn);<br />
                dbr = cmd.ExecuteReader();<br />
<br />
                // Read each record one at a time ...<br />
                while (dbr.Read())<br />
                {<br />
                    // Load our arrays with the Field names<br />
                    // and values.<br />
<br />
                    string dbMake = "";<br />
                    string model= "";<br />
                    for (int it = 0; it < dbr.FieldCount; it++)<br />
                    {<br />
                        string strField = dbr.GetName(it);<br />
                        if (strField == "MAKE")<br />
                        {<br />
                            dbMake = (string)dbr.GetValue(it);<br />
                        }<br />
                        if (strField == "MODEL")<br />
                        {<br />
                            model = (string)dbr.GetValue(it);<br />
                        }<br />
                    }<br />
                    if (dbMake == make)<br />
                        Models.Add(model);<br />
<br />
                }<br />
                string[] strModels = new string[Models.Count];<br />
                int i = 0;<br />
                foreach (object o in Models)<br />
                {<br />
                    strModels[i++] = (string)o;<br />
                }<br />
                return strModels;<br />
            }<br />
        }<br />

Thanks!

Guffa22-Sep-06 7:29

22-Sep-06 7:29

TheBlindWatchmaker wrote:
Here's the "Makes" method in the AppDatabaseInterface.cs file

No, it isn't.

---
b { font-weight: normal; }

TheBlindWatchmaker22-Sep-06 7:53

22-Sep-06 7:53

Damnit! I'm really inept today! Apologies, apologies!

This might be it! Smile | :)

<br />
 public string[] Makes<br />
        {<br />
            get<br />
            {<br />
                OleDbDataReader dbr = null;<br />
                ArrayList Makes = new ArrayList();<br />
<br />
                OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM {0};", "Speakers"), m_dbConn);<br />
                dbr = cmd.ExecuteReader();<br />
<br />
                // Read each record one at a time ...<br />
                while (dbr.Read())<br />
                {<br />
                    // Load our arrays with the Field names<br />
                    // and values.  NOTE: Reloading the field<br />
                    // names over and over again is probably<br />
                    // redundant, but I'm not sure that the order<br />
                    // column names are returned is fixed.<br />
                    for (int it = 0; it < dbr.FieldCount; it++)<br />
                    {<br />
                        string strField = dbr.GetName(it);<br />
                        if (strField == "MAKE")<br />
                        {<br />
                            string make = (string)dbr.GetValue(it);<br />
                            bool found = false;<br />
                            foreach (object o in Makes)<br />
                            {<br />
                                if ((string)o == make)<br />
                                {<br />
                                    found = true;<br />
                                    break;<br />
                                }<br />
                            }<br />
                            if (found)<br />
                                Makes.Add(make);<br />
                            break;<br />
                        }<br />
<br />
                    }<br />
<br />
                }<br />
                string[] strMakes = new string[Makes.Count];<br />
                int i= 0;<br />
                foreach (object o in Makes)<br />
                {<br />
                    strMakes[i++] = (string)o;<br />
                }<br />
                return strMakes;<br />
            }<br />
        }<br />

Guffa22-Sep-06 9:39

22-Sep-06 9:39

I see. The method does exactly what it should, and returns an empty array.

You only add to the ArrayList duplicates of values that already exist in the list, but as you start with an empty list, there are never any duplicates, so it will always remain empty.

I guess that your intention was to add the values that aren't duplicates?

In that case, why don't you just get the unique values from the database in the first place? And if you specify what field you want instead of using "select *", you don't have to loop through the fields looking for the one you want.

---
b { font-weight: normal; }

TheBlindWatchmaker22-Sep-06 9:53

22-Sep-06 9:53

Hi Guffa,

The Makes field has, say, 10 speakers from Mfr1, 10 from Mfr2 etc. I want the combo box to show Mfr1, Mfr2 and so on.

How would you suggest I do this?

Thanks for your time!

Guffa22-Sep-06 10:37