use current date as a search parameter through vb.net in mysql

Question

I'm trying to query my database based on the current user and the current date. I currently am getting no results from my query and I think the problem is with my formatting of the current date in vb.net. The query as is

query = "SELECT movie_name from movie2 Where movie_id=(SELECT movie_id from rental where client_username='" & currentUser & "' AND start_date<='" & Format(Now, "yyyy-MM-dd") & "' AND return_date>='" & Format(Now, "yyyy-MM-dd") & "')"

I know the rest of the query works (except calling the currentUser) as I have tested it on the mySql server. I have called currentUser before, which is saved through a variable in a module, without any hassle so I feel the problem has to be with my dates. I don't know how to format the current date so mySql will receive it properly? I have also tried the code

query = "SELECT movie_name from movie2 Where movie_id=(SELECT movie_id from rental where client_username='" & currentUser & "' AND start_date<='" & DateTime.Now.ToString("yyyy-MM-dd") & "' AND return_date>='" & DateTime.Now.ToString("yyyy-MM-dd") & "')"

with no success. Any help would be greatly appreciated! Thanks, Alan.

Yes, but the problem shoul be fixed using a parameterzied query — Steve, Apr 04 '16 at 09:49

score 3 · Accepted Answer · edited May 23 '17 at 12:08

The problem is the usual bad practice to use string concatenation when building sql command.
And the remedy is always the same. A parameterized query.

 query = "SELECT movie_name from movie2 Where movie_id=" & _
         "(SELECT movie_id from rental where client_username=@name"  &_
         " AND start_date<=@date AND return_date>=@date"
 Using cmd = new MySqlCommand(query, connection)
    cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = currentUser
    cmd.Parameters.Add("@date", MySqlDbType.DateTime).Value = DateTime.Now
    Using reader = cmd.ExecuteReader()
       .... now read your data ....
    End Using
End Using

With string concatenation you are vulnerable to Sql Injection and when forcing the conversion of decimals or date to a string you are an easy victim of wrong conversions. Parameters avoid all of that

EDIT: With an MySqlDataAdapter

 query = "SELECT movie_name from movie2 Where movie_id=" & _
         "(SELECT movie_id from rental where client_username=@name"  &_
         " AND start_date<=@date AND return_date>=@date"
 Using cmd = new MySqlCommand(query, connection)
    cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = currentUser
    cmd.Parameters.Add("@date", MySqlDbType.DateTime).Value = DateTime.Now
    Using adapter = new MySqlDataAdapter(cmd)
       Dim dt = new DataTable()
       adapter.Fill(dt)
    End Using
End Using

I want to show the data through a data frame so i was using a MySqlDataAdapter rather than a reader, how would I change the code to do this? — , Apr 04 '16 at 10:09
In that case, you could use the MySqlDataAdapter constructor that takes an MySqlCommand and pass the command prepared above instead of creating/using an MySqlDataReader — Steve, Apr 04 '16 at 10:27

use current date as a search parameter through vb.net in mysql

1 Answers1