none
C# 3.0 - Retrieve data from MS Access 12.0 and populate in TreeView. Select column with data indicating column name RRS feed

  • Question

  • Since I am a begginer, I believe the code below is not the smartest one. I'll be glad to improve it if you teach me how.

     

    What I intend to do, is retrieve data quering an MS Access 12.0 database and populate the results in a TreeView control.

    The MS Access file, named "CRMPass.accdb" contains six tables.

    The data I need to retrieve is in table "ThemeDetails", column No 4 (named "SpecialName").

     

    So, my solution has:

    namespace "nPermits",                with a WindowsForm          class cPermitsFrm : Form

    namespace "nQueryMSAccess"   with a Class Library            class cQueryMSAccess

    namespace "nTreeViewProj"         with a Class Library            class cTreeView

     

    I need to deal with two cases:

    In the first one, I select the column (with the data) referencing the number of the column in the table. In this case 4.

    In the second case, I need to reference the column by the column name. In this case "SpecialName".

     

    The code for the first case runs okay, but when modifying in order to reference by Column Name instead of Column Number, I get the following exception: An unhandled exception of type 'System.NullReferenceException' occurred in QueryMSAccess.dll    Additional information: Object reference not set to an instance of an object

    The Exception points to the sentence highligted in yellow

     

    The changes in the code are highligted in green

     

    Thanks in advance for any help,

    Aldo.

     

    Code Snippet

    namespace nPermits

    {

        public partial class cPermitsFrm : Form

        {

           public cPermitsFrm()

           {

               InitializeComponent();

              // Retrieve data from selected Column (by "action" configuration) and show data into Tree View

     

              // First case: 

              IList<string> themes = cQueryMSAccess.QueryColumnDataToList("GetCRMPassThemeDetails", 4);

     

              // Second case:

    IList<string> themes = cQueryMSAccess.QueryColumnDataToList("GetCRMPassThemeDetails", "SpecialName");

     

              foreach (string item in themes) { cTreeView.InitializeTreeView(trVwChooseTheme, "ChooseTheme", 0, item); }

            }

        } // Class

    }//NameSpace

     

    namespace nQueryMSAccess

    {

      public static class cQueryMSAccess

      {

        public static string ConnString(string action)

        {

           // Set the connection string

           string conn;

           switch (action)

           {

              case "GetCRMPassThemeDetails":

                 conn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=E:\Visual Studio 2008\Projects\ReadWriteMSAccessFromGrid\Permits\Data\CRMPass.accdb";

                 break;

              default: conn = null; break;

            }

            return conn;

        }

     

        public static string SelectString(string action)

        {

            // Set the values for several SQL strings I am going to use

            string selectString;

            switch (action)

            {

                case "GetCRMPassThemeDetails":

                selectString = @" SELECT" + @" * " + @" FROM ThemeDetails";

                   break;

                default: selectString = ""; break;

            }

            return selectString;

         }

     

         public static string SourceTable(string action)

         {

            // Set SourceTable value

            string sourceTable;

            switch (action)

            {

                case "GetCRMPassThemeDetails":

                   sourceTable = "ThemeDetails";

                   break;

                default: sourceTable = ""; break;

             }

             return sourceTable;

         }

     

         public static IList<string> QueryColumnDataToList(string action, int colNo) ==> string colName for the second case

         {

             IList<string> myList = new List<string>();

             DataSet myDataSet = new DataSet();

             try

             {

                using (OleDbConnection myConnection = new OleDbConnection(ConnString(action)))

                {

                   using (OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(SelectString(action), myConnection))

                    {

                         myDataAdapter.Fill(myDataSet, SourceTable(action));

                    }

                 }

              }

             catch (Exception ex)

             {

                MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK,  MessageBoxIcon.Error);

                Application.Exit(); // finish the program

              }

     

             // For the first case:

             DataTable dt = myDataSet.Tables[0];

             foreach (DataRow dr in dt.Rows) { myList.Add(dr[colNo].ToString()); } // Load elements of selected column to list

     

             //For the second case:

             DataTable dt = myDataSet.Tables[colName];

             foreach (DataRow dr in dt.Rows) { myList.Add(dr[colName].ToString()); }

     

             return myList;

          }

      } // Class

    } // namespace

     

    namespace nTreeViewProj

    {

    public class cTreeView

    {

    public static void InitializeTreeView(TreeView TreeName, string nodeName,int nodeNo, string text)

    {

    TreeName.BeginUpdate();

    switch (nodeName)

    {

    case "ChooseTheme":

    TreeName.Nodes.Add(text);

    break;

    case "CompanyName":

    TreeName.Nodes[0].Nodes.Add(text);

    break;

    default:

    break;

    }

    TreeName.EndUpdate();

    }

    } // Class

    } // namespace

     

     

    Wednesday, March 19, 2008 7:37 PM

Answers

  • I changed the Method this way, and now is running okay.

    Code Snippet

    public static IList<string> QueryColumnDataToList(string action, string colName)

    {

      IList<string> myList = new List<string>();

      DataSet myDataSet = new DataSet();

      try

      {

        using (OleDbConnection myConnection = new OleDbConnection(ConnString(action)))

        {

          using (OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(SelectString(action), myConnection))

          {

              myDataAdapter.Fill(myDataSet, SourceTable(action));

              DataTable dt = myDataSet.Tables[0];

              foreach (DataRow dr in dt.Rows) { myList.Add(dr[colName].ToString()); } // Load elements of selected column to list

          }

        }

       }

       catch (Exception ex)

       {

          MessageBox.Show("Error: " + ex.Message, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

          Application.Exit(); // finish the program

       }

       return myList;

    }

     

     

    Thursday, March 20, 2008 5:56 AM