select into database problem

I am doing this.

Code:

---

Query = "select  'Number' from database.students where Course ='" & ComboBox1.SelectedItem & "'"

---

and after that number from my database will be saved into my combobox. But the problem is i cannot retrieve the number based on the course i choose.

What is the contents of Query after that line? Is it what you intended? Have you even checked? How exactly do you then execute that query?

Quote:

---

Originally Posted by jmcilhinney

What is the contents of Query after that line? Is it what you intended? Have you even checked? How exactly do you then execute that query?

---

Here my code after executing the query

Code:

---

'If Form1.ComboBox1.SelectedItem = -1 Then
                '    Query = "select * from database.students "

                'Else
                Query = "select  'Number' from database.students where Course ='" & ComboBox1.SelectedItem & "'"
               
                COMMAND = New MySqlCommand(Query, mydbcon)

                reader = COMMAND.ExecuteReader
                While reader.Read
                    Dim num = reader.GetDouble("Number")
                    '  txtSIM.Text = reader.GetDouble("Number")
                    ComboBox3.Items.Clear()
                    ComboBox3.Items.Add(num)

---

I'm assuming Number is the name of a field. But by selecting it in quotes you won't get the content of that field, you will get the word "Number". If you take the quotes from around Number you will probably get the answer you're after.

Also, move the "ComboBox3.Items.Clear()" outside of your wile loop. At the moment your adding a record, clearing it, then adding the next record, clearing that and so on.

Yeah, that's the code, but there is a good chance that the resulting Query is not what you think it is. In some scenarios, ComboBox1.SelectedItem will return something considerably different from what you are expecting. In fact, it might return a type name.

So, the first thing you need to do is put a breakpoint on this line:

COMMAND = New MySqlCommand(Query, mydbcon)

When execution stops on the breakpoint, take a look at what is in Query. If it looks right, then that's not the issue, but there's a good chance that you'll have concatenated something into that query that will really surprise you.

One other point I would make is about what you are selecting. I don't know MySQL, so you may well have that right. It wouldn't be right for the T-SQL in SQL Server, though. Wrapping something in single quotes would create a string literal in the output, with no field name. If that's the right syntax for MySQL, then that's fine. I know they're different.

Quote:

---

Originally Posted by FunkyDexter

I'm assuming Number is the name of a field. But by selecting it in quotes you won't get the content of that field, you will get the word "Number". If you take the quotes from around Number you will probably get the answer you're after.

Also, move the "ComboBox3.Items.Clear()" outside of your wile loop. At the moment your adding a record, clearing it, then adding the next record, clearing that and so on.

---

I tried removing the quotes but still it doesn't populate my combobox with numbers. But when i use

Code:

---

Query = "select * from database.students "

---

i can populate my combobox.

Quote:

---

Originally Posted by djgaidel

Here my code after executing the query

Code:

---

'If Form1.ComboBox1.SelectedItem = -1 Then
                '    Query = "select * from database.students "

                'Else
                Query = "select  'Number' from database.students where Course ='" & ComboBox1.SelectedItem & "'"
               
                COMMAND = New MySqlCommand(Query, mydbcon)

                reader = COMMAND.ExecuteReader
                While reader.Read
                    Dim num = reader.GetDouble("Number")
                    '  txtSIM.Text = reader.GetDouble("Number")
                    ComboBox3.Items.Clear()
                    ComboBox3.Items.Add(num)

---

---

How about you answer the actual question that I asked? I didn't ask for code. I asked for the contents of a variable. The fact that you haven't provided it suggests that you don't know what it is, which is a big problem. You're executing a query that doesn't do what you expect and you haven't even checked to see what that query is. You obviously have an expectation of what it is but if our code always did what we expected then there'd be very few bugs at all. If your code does do what you expect then one of the very first things you should be doing is checking whether your variables actually contain the values you think they do.

Sigh... the query is a red herring... there may still be issues with it, but it's not the reason the combo box is empty...

Look at what's going on in your loop:

Code:

---

                While reader.Read
                    Dim num = reader.GetDouble("Number")
                    '  txtSIM.Text = reader.GetDouble("Number")
                    ComboBox3.Items.Clear()

---

*koff* *koff* ... see the problem? *koff* you're clearing the cbo on every time through the loop. *koff* *koff*

Also, "Number" should be in back ticks ` or in brackets [] in the SQL... I know ` works with MySQL... not sure if it does with Access or SQL Server or others... the brackets [] work in Access and SQL Server, not sure about the others.

-tg

Personally I'd change the name of the field to something more meaningful instead of just "number" ...

-tg

Yeah, I don't like the idea of a field name of 'Number', but I don't quite agree with the clearing issue. That's certainly a bizarre thing to do, but there IS one item added to the combobox right after that, so the CB should end up with the value from the final row. If that's acceptable, then there is no need for the datareader, since ExecuteScalar would do better, and there's not much point in having a combobox since there's only one value.