C# | MySql query working in DB not working in application

Question

I have this query:

string query = "SELECT afspraak_locatie FROM Afspraak WHERE date(datum) = '" + datum +"'";

The final query will look like this:

SELECT afspraak_locatie FROM Afspraak WHERE date(datum) = '2016-06-16'

When i execute the query in my PHPMYADMIN it returns the row. But when i do it in C# it says my MySqldatareader is empty

Here is the code i use for that:

MySqlCommand cmd1 = new MySqlCommand(query1, connection);
cmd1.CommandType = CommandType.Text;
using (MySqlDataReader reader1 = cmd1.ExecuteReader())
{
       while (reader1.Read())
       {
            result1.Add(reader1.GetString(0));
       }
       reader1.Close();
}
cmd1.Cancel();

When this gets executed it will give a System.NullreferenceException on the while(reader1.read) part. Any solutions?

Use sql parameters instead of string concatenation. If you get a `NullReferenceException` `result1` is null which seems to be a `List`, so use `var result1=new List();` — Tim Schmelter, Jun 20 '16 at 14:20
Well, the first thing to do is stop building your query like that. Use parameterized SQL. Note that you don't need to call `Close` on your reader due to the `using` statement. — Jon Skeet, Jun 20 '16 at 14:20
These variables are created with the program itself, There is no user interaction. — Gertjan Brouwer, Jun 20 '16 at 14:21
@GertjanBrouwer Irrelevant. It only takes someone reusing the code for something that is provided externally to open up for SQL Injection. Also by parametrising you avoid needing to know how to format values for the database. — Richard, Jun 20 '16 at 14:24
What does debugging show? If you are getting a `NullReferenceException` then start by working out what is null. — Richard, Jun 20 '16 at 14:24
Did you try to debug the code? It might be that your query doesn't have the date value in the desired format.. — Sujeet Sinha, Jun 20 '16 at 14:29
My query has the value 2016-06-16 without Hours,Minutes and seconds. But the database can still find the value datetime because i use date(). Also the nullreferenceexeption is from the reader1 so that means my MySqldatareader is empty. Which means my query did not return anything. However the query works when i use it in my PHPMYADMIN — Gertjan Brouwer, Jun 20 '16 at 14:35

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Schema and data loaded:

create table Afspraak
(
    id int auto_increment primary key,
    afspraak_locatie varchar(100) not null, -- just an example (we don't know your datatype)
    datum datetime not null -- you said it was a datetime in a comment under your question
);

insert Afspraak (afspraak_locatie,datum) values
('Rome','2016-06-14 13:55:55'),
('London','2016-06-15 15:12:12'),
('Cairo','2016-06-16 07:00:33'),
('Boston','2016-06-17 01:30:00');

select * from afspraak; 
+----+------------------+---------------------+
| id | afspraak_locatie | datum               |
+----+------------------+---------------------+
|  1 | Rome             | 2016-06-14 13:55:55 |
|  2 | London           | 2016-06-15 15:12:12 |
|  3 | Cairo            | 2016-06-16 07:00:33 |
|  4 | Boston           | 2016-06-17 01:30:00 |
+----+------------------+---------------------+

GUI Layer:

private void button1_Click(object sender, EventArgs e)
{
    myDB.FindThatRow("2016-06-16"); // get data
}

DB Layer:

public void FindThatRow(string theDate)
{   // or all those rows
    // 

    using (MySqlConnection lconn = new MySqlConnection(connString))
    {
        lconn.Open();
        using (MySqlCommand cmd = new MySqlCommand())
        {   // 
            cmd.Connection = lconn;
            cmd.CommandText = @"select id,afspraak_locatie FROM Afspraak WHERE date(datum) = @pTheDate";
            cmd.Prepare();
            cmd.Parameters.AddWithValue("@pTheDate", theDate);
            using (MySqlDataReader rs = cmd.ExecuteReader())
            {   //
                while (rs.Read())
                {
                    int qId = (int)rs.GetInt32("id");
                    string sViewIt = rs.GetString("afspraak_locatie");
                }
            }
        }
    }
}

It found the data:

Use the using blocks as recommended by everyone. Bind your parameters.

The reasons why one should steer toward data bindings, versus string concatenation as seen in your attempt, include losing the functionality of what binding offers as seen in Configuring Parameters and Parameter Data Types and other links near or off that topic. And, it turns querying into the mess seen in PHP with concatenation which steered their modern usage toward parameter data bindings too.

Imagine how difficult and debug-intensive the following query would be without bindings:

Sql Injection Attacks:

Parameter binding protects you from such attacks, unlike your method of concat. See the following question including this answer for stored procedure usage.

Hi, Great post. I might have found the problem, I currently open the connection and then i use cmd1 and cmd2 for 2 different query's the first one always works but the second always fails apparantly. Do you have any idea why that would be the case??? Someone also suggested MySql stored procedure, might that be a solution — Gertjan Brouwer, Jun 20 '16 at 16:57
It throws errors if there is a result set midstream and not completed. I use blocks of connections (very small blocks) that have segmented usage. Or, for one-off queries, I wrap the connect, cmd, resultset in `using` and flee the routine knowing the connection object and everything is disposed of. So, have different connection objects. It does not mean you are going to run out of connections on the server if you do it right. It means all those errors go away. — Drew, Jun 20 '16 at 17:01
I currently have 1 MySql connection which i open via if(connection.open). Should i create a new one for each query in the same function/method or should i as suggested use stored procedures? — Gertjan Brouwer, Jun 20 '16 at 17:03
Yeh i only need them for 2 seconds each to get some data and they can be closed then. But I am not sure if that is the problem i am currently facing. — Gertjan Brouwer, Jun 20 '16 at 17:07
Well i now have 2 objects of the DBconnect class: dbconnect1 and dbconnect2. I first do dbconnect1.getAfspraakId after that i do dbconnect2.getAfspraakInfo. This won't work it will give me a system.nullreferenceexception on the mysqldatareader. But when i dont use the method dbconnect1.getAfspraakId and just do dbconnect2.getAfspraakInfo it works. I dont know why do you have any idea?? — Gertjan Brouwer, Jun 20 '16 at 18:35
I would need to see your source code. Feel free to find me in the [Campaigns](http://chat.stackoverflow.com/rooms/95290/campaigns) room. As such, I am deleting irrelevant comments under this posting. — Drew, Jun 20 '16 at 18:40
NVM i fixed it, In my method before this one i used cmd.close() which apprantly causes the error, Probably because the connection stays open. — Gertjan Brouwer, Jun 20 '16 at 18:45

C# | MySql query working in DB not working in application

1 Answers1