6 Replies - 18122 Views - Last Post: 03 January 2011 - 05:35 PM Rate Topic: -----

#1 aaronhaul   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-December 09

FillingComboBox with Data from Database MS SQL

Posted 01 January 2011 - 08:26 PM

Hello everyone,

I have been trying to display two combined columns from database table on comboBox with DropDownList Style, but it is not working and giving me bugs. I followed the examples on http://www.dreaminco...n-the-combobox/ but still getting errors. I am seriously stuck as I am new to C# programming. This is my code:

void FillcomboBox()
        {
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database\\HCore.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            con = new System.Data.SqlClient.SqlConnection();
            con.Open();
            string sql = "SELECT currency_name + ' --- ' + currency_symbol AS BaseCurrency FROM Currency";
            HCoreDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sql, con);

            DataSet HCoreDataSet = new DataSet();
            HCoreDataAdapter.Fill(HCoreDataTable);
            comboBox1.DataSource = HCoreDataTable.tables[0];
            comboBox1.DisplayMember = "BaseCurrency";
        }



Any ideas? Please help me.

Thanks for your efforts

Is This A Good Question/Topic? 0
  • +

Replies To: FillingComboBox with Data from Database MS SQL

#2 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6537
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: FillingComboBox with Data from Database MS SQL

Posted 01 January 2011 - 08:54 PM

Quote

but still getting errors


Less than helpful. What errors on which lines?
Was This Post Helpful? 0
  • +
  • -

#3 aaronhaul   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-December 09

Re: FillingComboBox with Data from Database MS SQL

Posted 01 January 2011 - 10:13 PM

Hi,

Thank you for your effort. I'm having an error on line 11: comboBox1.DataSource = HCoreDataTable.tables[0];

And the error is:

System.Data.DataTable' does not contain a definition for 'tables' and no extension method 'tables' accepting a first argument of type 'System.Data.DataTable' could be found (are you missing a using directive or an assembly reference?)

If the errors cannot be corrected for my problems, can you please give me a better suggestions on the best way I can do it?

Thank you a lot.
Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6259
  • View blog
  • Posts: 24,028
  • Joined: 23-August 08

Re: FillingComboBox with Data from Database MS SQL

Posted 02 January 2011 - 06:16 AM

Learning to read and fix your own errors. Your problematic code:
comboBox1.DataSource = HCoreDataTable.tables[0];


The error:
System.Data.DataTable' does not contain a definition for 'tables'


So, HCoreDataTable appears to be of type System.Data.DataTable, a class which does not contain a property called tables.

DataSet HCoreDataSet = new DataSet();
HCoreDataAdapter.Fill(HCoreDataTable);

I don't use DataAdapters and such, but did you perhaps mean for the argument to fill to be a DataSet, like maybe HCoreDataSet?
Was This Post Helpful? 0
  • +
  • -

#5 aaronhaul   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-December 09

Re: FillingComboBox with Data from Database MS SQL

Posted 03 January 2011 - 04:25 PM

Thank you very much for your assistance. It really do helped me. As I said the other day, am new to C# Programming LOL. Well, from the info you gave me, I had to rewrite the code and was able to arrive at a solution without errors. The comboBox do display the datas from the Database as I wanted when I jumped the exception that was unhandled in Line 27, and there were no errors.

It couldn't have a straight run because a NullReferenceException was unhandled in Line 27. The code and the exception below:

        private void populateCurrencyCombo()
        {
            // Initialize new connection string
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database\\HCore.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            try
            {

                // Get values from Currency table for combo box
                String sQueryCurrency = "SELECT currency_name + ' --- ' + currency_symbol AS [Base Currency] FROM Currency";

                HCoreDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sQueryCurrency, con);

                DataTable dTableCurrency = new DataTable();

                HCoreDataAdapter.Fill(dTableCurrency);
                if (dTableCurrency.Rows.Count > 0)
                {

                    // Attach combo box to data table
                    cboCurrency.DataSource = dTableCurrency;

                    // Set data to display
                    cboCurrency.DisplayMember = dTableCurrency.Columns["Base Currency"].ToString();

                    // Set data to bve used as value when item is selected
                    cboCurrency.ValueMember = dTableCurrency.Columns["currency_ID"].ToString();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string msg = "Fetch Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }



This is the exception:

NullReferenceException was unhandled - Line 27

Object reference not set to an instance of an object.



I don't know how to remove an exception from the code. I would greatly appreciate if it can be removed.

Thanks a million.
Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6259
  • View blog
  • Posts: 24,028
  • Joined: 23-August 08

Re: FillingComboBox with Data from Database MS SQL

Posted 03 January 2011 - 04:38 PM

cboCurrency.ValueMember = dTableCurrency.Columns["currency_ID"].ToString();


I don't see currency_ID in your SQL statement here:

String sQueryCurrency = "SELECT currency_name + ' --- ' + currency_symbol AS [Base Currency] FROM Currency";


Which means that dTableCurrency.Columns["currency_ID"] is null, and therefore you're trying to call ToString() on a null object.
Was This Post Helpful? 0
  • +
  • -

#7 aaronhaul   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 10-December 09

Re: FillingComboBox with Data from Database MS SQL

Posted 03 January 2011 - 05:35 PM

Yes you're right about the null. currency_ID is set to null since the column is set to Identity of the Currency table. So I added the currency_ID to the query to select the data from the database, and it worked like magic. :D This is the new updated / corrected working code, hope it helps others with similar problems too:

        private void populateCurrencyCombo()
        {
            // Initialize new connection string
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database\\HCore.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            try
            {

                // Get values from Currency table for combo box
                String sQueryCurrency = "SELECT currency_ID, currency_name + ' --- ' + currency_symbol AS [Base Currency] FROM Currency";

                HCoreDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sQueryCurrency, con);

                DataTable dTableCurrency = new DataTable();

                HCoreDataAdapter.Fill(dTableCurrency);
                if (dTableCurrency.Rows.Count > 0)
                {

                    // Attach combo box to data table
                    cboCurrency.DataSource = dTableCurrency;

                    // Set data to display
                    cboCurrency.DisplayMember = dTableCurrency.Columns["Base Currency"].ToString();

                    // Set data to bve used as value when item is selected
                    cboCurrency.ValueMember = dTableCurrency.Columns["currency_ID"].ToString();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                string msg = "Fetch Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }




Thanks a lot for the advice.

You're da bomb!!! :rockon:

AaronHaul.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1