How to populate combobox from sql in C# - and more...

[Neofriend]

Alright, I have the answer to the first question. Helpful for many of those who are having a real problem dealing with this.

Code: Select all

 // Connection and SQL strings
            string SQL = "SELECT lab_name FROM lab";

            // create a connection object
            string ConnectionString = "Data Source=xyz;Initial Catalog=xyz;User ID=xyz;Password=xyz";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create command object
            SqlCommand cmd = new SqlCommand(SQL, conn);

            // open connection
            conn.Open();

            // call command's ExcuteReader
            SqlDataReader reader = cmd.ExecuteReader();

            try
            {

                while (reader.Read())
                {
                    comboBox1.Items.Add(reader["lab_name"].ToString());

                }
            }

            finally
            {
                // close reader and connection
                reader.Close();
                conn.Close();
            }

That perfectly works to populate the combobox - but I have this combobox in a form, where I not only want to populate it from the database but the user selects an item from the combobox and on submit, it should edit some place in database.

So the question is how to get the selecteditem from combobox. I tried a couple of things, for example
comboBox1.SelectedItem.ToString();

but it doesn't seem to work..... I am sure am missing something. Thank you.

[Neo]

comboBox1.Text is a simple way and comboBox1.SelectedItem.ToString() is the standard C# way. Both works in the following example.

Code: Select all

        public Form1()
        {
            InitializeComponent();

            comboBox1.Items.Add ("abcd1");
            comboBox1.Items.Add ("abcd2");
            comboBox1.Items.Add ("abcd3");
            comboBox1.Items.Add ("abcd4");
            comboBox1.Items.Add ("abcd5");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show(comboBox1.Text);
            MessageBox.Show(comboBox1.SelectedItem.ToString());
        }

[Neofriend]

You're right with that and everything works fine.

However, am now fighting with a pinching situation of combobox.

The situation: I have 2 combobox, on load of form, one of the combobox is filled with data from a table in database. Now I'd like to detect whenever the user selects an item from the combobox1 (whenever the selection is changed too), it should get the SelectedItem.ToString() saved in a variable and trigger a database call for filling another combobox on basis of this value.

Its been 4 hours am trying to do this; and a few other things which are working fine now though this is just killing me.

Hope you have an answer.

[Neo]

This is a usual situation when you develop a database application.

Write a function based on the following pseudo code.

function fillCombo(){

clear combobox
fetch data from database (ex: select field1 from table1 where field2 = comboBox1.SelectedItem.ToString() )
load field1 data to combo2 as you did for combo1
}

Call this function from click, selection, etc...

[Neofriend]

Hey, thanks for the response. The internet wasn't working but the good news is I ended up using the same technique. And so far, it feels like working nicely.

But am having another irritating issue. Its pretty detailed but I'll try to explain the main scenario.

One form, with 4 combo's and one text box. Two combo boxes are populated from database. When the form is filled, we can click on submit. As soon as submit is pressed. Two methods are called.

First method, inserts the data in to one table, that table records the transaction.
Second method, gets the last inserted ticketid from the first method and selected labno from form and inserts the status in a table.

The first method table namely transaction table has one primary key, ticketid.
The second table status has two foriegn keys.

The issue is, when I press submit, the transaction is recorded nicely. But it shows error in inserting the data into status table, says conflicted with the foreign key constraint. Queries and code is fine, I checked it many times.

Another interesting thing, as soon as you click submit button next time. It inserts in to status table then.

Any idea, why?

am calling both the methods, on submit button. Even checked the values and many things using messagebox. Its so irritating when you work for 10 hours on the same one problem.

Everything was going quite smooth since last couple of days.

[Neo]

My friend, don't worry. These are the situation we enjoy the most.

From you explanation, it seems it is an issue related to key constraints (referential integrity).
Check following things.

1. Begin transation (BeginTrans)
2. Insert data to the table with primary key first
3. Commit the table insertion (CommitTrans)

Now we know the entry is completely inserted to database and ready to cater referential integrity.

4. Begin transation (BeginTrans)
5. Insert data to the 2nd table with foreign keys to first table
6. Commit the table insertion (CommitTrans)

If this is not working, let me know.

[Neofriend]

I am glad you know, how much am I enjoying this. I feel sooooooo thrilled! lol

I am just doing that, as I've made the methods, on the submit button am calling the first method then am calling the second method.

Apart from this, what I don't understand in your last reply is, where to begin transaction and how exactly? As in application, under the query? - A little confused....

Guess you just lose the power when you are working on same kind of troubles and stick to one form.

[Neofriend]

by the way, one of my friends said, every table should have a primary key. Really?

As I've been working with primary keys as and when required so far. She says it is because of primary key. The first table has the primary key, second table has two foriegn keys, one from the first table and another from second table and an extra column. That's it, no primary key.

[Neofriend]

I deleted the data from both the tables and edited somethings as I thought might be helpful to do the last try. And it didn't work nicely.

But what I see now is, It perfectly adds data to both the tables but it gives an error too. Huh? that the insert statement is conflicted with the foreign constraint.... why?

[Neo]

It is due to violation of referential integrity. Most of the database engines provide a tree view (Graphical view of the database) which shows primary/foreign keys. If you could post a screenshot of that we can reduce typing

Answer to your friend: Primary key is not possible always. Whenever we add a primary key we must understand the constraints of it like only unique entries, etc... Whenever we can't maintain that, we do not use a primary key.
But we must try to normalise the database as much as possible so we have primary key for most of the tables. The main reason is that the tables with primary key are indexed automatically so that those are faster to access than the other ones. Please invite her to join with us. We need lots of people here for a good discussion.