AND statement in mySQL

Question

I'm trying to run a query in MySQL database with C#. I want the column prioritySettings returned when the 3 previous columns are within certain values. This query works, but only with one variable at a time, so at the moment I can only specify the value of one of the columns. Are my AND statements correct?

string query = "SELECT prioritySetting FROM {DATABASE} WHERE handling ='" + handling + "'" + "AND corner ='" + corner + "'" + "AND power  ='" + power + "'";

some more code;

                 MySqlDataReader sqlReader;                                       
                string handling = overOrUnderInput;
                string corner = cornerPartInput;
                string power = onOrOffPowerInput;
                string query = "SELECT prioritySetting FROM {DATABASE} WHERE handling ='" +             handling + "'" + " AND corner ='" + corner + "'" + " AND power  ='" + power + "'"; 
                MySqlCommand getRecords = new MySqlCommand(query, connection);
                connection.Open();
                sqlReader = getRecords.ExecuteReader();

                    while (sqlReader.Read())
                    {

                        try
                        {

                            try
                            {

                                suggestions[i] = (sqlReader.GetString(0));
                            }
                            catch
                            {
                            }
                            i++;

                        }
                        catch (MySqlException ex)
                        {
                            MessageBox.Show(ex.ToString());
                        }

and the error;

System.NullReferenceException: Object reference was not set to an instance of an object.

thanks, i used {database} to represent my database, i do actaully have the database.Table in my code — EPicLURcher, Oct 13 '14 at 08:22
the error is that i have an array that gets data out of the database and there is a null exception — EPicLURcher, Oct 13 '14 at 08:23
I suggest to post the whole code around this call and the exact error message because this should not fail unless one of your input contains a single quote. I am pretty sure that the error is not here — Steve, Oct 13 '14 at 08:25
Please read this [What is a null reference exception and how to fix it](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Steve, Oct 13 '14 at 08:36
ok thank you very much. Ill report back, that link should help! — EPicLURcher, Oct 13 '14 at 08:50

score 4 · Answer 1 · edited Oct 13 '14 at 08:21

4

Seem like a space is required before each AND.

edited Oct 13 '14 at 08:21

İsmet Alkan

5,141
3
38
64

answered Oct 13 '14 at 08:16

nightcrawler

56
3

I would suggest to test this answer against a real query – Steve Oct 13 '14 at 08:22
thanks, i tried this but i am still getting the same error, a null exception is thrown when it tries to save data into an array – EPicLURcher Oct 13 '14 at 08:25

score 0 · Answer 2 · answered Oct 13 '14 at 08:18

0

Add a space before each AND

string query = "SELECT prioritySetting FROM {DATABASE} WHERE handling ='" + handling + "'" + " AND corner ='" + corner + "'" + " AND power  ='" + power + "'";

answered Oct 13 '14 at 08:18

Tushar

11,306
1
21
41

score 0 · Answer 3 · answered Oct 13 '14 at 08:20

0

Let's see how your query looks if the parameters are inserted. I assume parameters have values equal to their names.

SELECT prioritySetting 
FROM {DATABASE} 
WHERE handling ='handling'AND corner ='corner'AND power  ='power'

If it's really DATABASE after the FROM you should change it to TABLE. I'm not very sure if it causes any problems, but I strongly recommend putting spaces before ANDs. So your code line will be like:

string query = "SELECT prioritySetting FROM {DATABASE.TABLE} WHERE handling ='" + handling + "'" + " AND corner ='" + corner + "'" + " AND power  ='" + power + "'";

answered Oct 13 '14 at 08:20

İsmet Alkan

5,141
3
38
64

tried inserting them "manually" and i got the same error (null exception when tried to save into array) as when they were being inserted by a variable – EPicLURcher Oct 13 '14 at 08:30
can you please provide your table schema and some more code. (especially where it throws the error) – İsmet Alkan Oct 13 '14 at 08:32

score 0 · Answer 4 · answered Oct 13 '14 at 08:25

0

string query = "SELECT prioritySetting FROM {TABLE}
                    WHERE handling ='" + handling + "'" + " 
                    AND corner ='" + corner + "'" + "
                    AND power  ='" + power + "'";

answered Oct 13 '14 at 08:25

sqluser

5,145
7
32
48

AND statement in mySQL

4 Answers4