Results 1 to 10 of 10

Thread: select into database problem

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2015
    Posts
    19

    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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: select into database problem

    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?

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2015
    Posts
    19

    Re: select into database problem

    Quote Originally Posted by jmcilhinney View Post
    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)

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: select into database problem

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: select into database problem

    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.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Nov 2015
    Posts
    19

    Re: select into database problem

    Quote Originally Posted by FunkyDexter View Post
    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.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: select into database problem

    Quote Originally Posted by djgaidel View Post
    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.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: select into database problem

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: select into database problem

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: select into database problem

    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.
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width