MySql is returning column name, not value

Question

myCommand.CommandText = "SELECT 'USED' FROM `KEYS` WHERE `KEYS`.`KEY` = '" + TextBox1.Text + "'"

Dim myReader As MySqlDataReader
            myReader = myCommand.ExecuteReader
            While (myReader.Read())
                MessageBox.Show(myReader.GetString(0))
            End While

The returning string is "USED". But that is wrong: it should be returning integer 0 instead. Why is that?

Edit: I changed the MessageBox line to MessageBox.Show(myReader.GetInt16(0)) but now it sends me an error telling me that the input string is not in the right format..

@SLaks: Ah, you are very right. I am quite new to this.. I will take a look about that soon. Thank you. — Saturn
– Saturn, Commented Dec 30, 2011 at 17:44

Eric · Accepted Answer · 2011-12-30 17:39:35Z

4

You need backtick, not apostrophe ` not '

You're actually selecting the string "USED" rather than the column. You could just remove the apostrophes all together and say

myCommand.CommandText = "SELECT USED FROM `KEYS` WHERE `KEYS`.`KEY` = '" + TextBox1.Text + "'"

Also as a note, don't use dynamic SQL--used prepared queries:

myCommand.CommandText = "SELECT USED FROM KEYS WHERE KEYS.KEY = @Key";
myCommand.Parameters.AddWithValue("@Key", TextBox1.Text);

Otherwise you're very susceptible to SQL injection (which is a very bad thing).

answered Dec 30, 2011 at 17:39

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Saturn Over a year ago

Thanks a lot! What does the Pameters.AddWithValue method do exactly though?

Eric Over a year ago

It adds a parameter of a given name, in this case @Key, with the value passed, in this case TextBox1.Text to the query. When the query executes, it safely inserts that value where you've put the name of that parameter in the command text, so that you don't have to worry about somebody executing SQL code on your app without your permission.

Icarus · Accepted Answer · 2011-12-30 17:39:41Z

1

You are using the wrong quotes; you need to use ` as so:

myCommand.CommandText = "SELECT `USED` FROM `KEYS` WHERE `KEYS`.`KEY` = '" + TextBox1.Text + "'"

answered Dec 30, 2011 at 17:39

Icarus

64k14 gold badges102 silver badges116 bronze badges

Comments

AlfredoVR · Accepted Answer · 2011-12-30 17:40:19Z

1

Remove the 'USED' and write used instead.

answered Dec 30, 2011 at 17:40

AlfredoVR

4,3673 gold badges27 silver badges33 bronze badges

Comments

Christopher Mahan · Accepted Answer · 2011-12-30 17:40:38Z

1

single quoted around the name 'USED' tell it to return the word 'USED' in an unnamed column.

answered Dec 30, 2011 at 17:40

Christopher Mahan

7,6299 gold badges55 silver badges66 bronze badges

Comments

Lion · Accepted Answer · 2011-12-30 17:40:48Z

0

Change here

"SELECT `USED` FROM `KEYS` WHERE `KEYS`.`KEY` = '" + TextBox1.Text + "'"

You need to insert backticks and not single quotes.

answered Dec 30, 2011 at 17:40

Lion

19.1k22 gold badges83 silver badges111 bronze badges

Collectives™ on Stack Overflow

MySql is returning column name, not value

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related