Search Filtering Mysql / Gridview in VB.Net

Question

I have 2 texts boxes "TextBox1" and "TextBox2" and a submit button called "Button1" Below that i have a Label "Label1"

What i need to do is search between dates in "Input1 & Input2" (yyyy-mm-dd) and display a value (It is just counting the records in the tabels)

This is my current query

Dim c As New MySqlConnection("Server=localhost;Database=test;UID=test;PWD=test;")
c.Open()
Dim com As New MySqlCommand("SELECT COUNT(*) as c FROM toutcome WHERE AffID = '0001' AND CompletedDate >= CURDATE();", c)
Dim myReader As MySqlDataReader = com.ExecuteReader(CommandBehavior.CloseConnection)
myReader.Read()
Label1.Text = myReader.Item(0).ToString()

myReader.Close()

And here is the query i am attempting to use to complete the above task, it is not working at all.

Dim c As New MySqlConnection("Server=localhost;Database=mdxmain;UID=MerryWoodTest;PWD=H0r$hamTest;")
c.Open()
Dim com As New MySqlCommand("SELECT COUNT(*) as c FROM toutcome WHERE AffID = '0001' AND WHERE CompletedDate >= '" & TextBox1.Text & "' AND CompletedDate <= '" & TextBox2.Text & "'", c)
Dim myReader As MySqlDataReader = com.ExecuteReader(CommandBehavior.CloseConnection)
myReader.Read()
Label11.Text = myReader.Item(0).ToString()

myReader.Close()

The top Query works and i get a value.

The bottom query error is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE CompletedDate >= '' AND CompletedDate <= ''' at line 1]

_it is not working at all_ Care to explain? Did you get error messages? The count is zero? — Steve, Feb 27 '15 at 13:06

Steve · Accepted Answer · 2015-02-27T13:43:11.187

Too many WHEREs in that line

... FROM toutcome WHERE AffID = '0001' AND WHERE ....
                                           ^^^^^

Of course a better approach is to use a parameterized query to have a command text more readable and prevent Sql Injection and add the Using statement around disposable object to release their resource when you have finished to work with them

Dim commandText= "SELECT COUNT(*) as c FROM toutcome " & _
                 "WHERE AffID = '0001' AND CompletedDate >= @init " & _
                 " AND CompletedDate <= @ending"
Using c = New MySqlConnection(".........")
Using com = new MySqlCommand(commandText, c)
    c.Open()
    com.Parameters.Add("@init", MySqlDbType.Date).Value = Convert.ToDateTime(TextBox1.Text)
    com.Parameters.Add("@ending", MySqlDbType.Date).Value = Convert.ToDateTime(TextBox2.Text)
    Using myReader = com.ExecuteReader(CommandBehavior.CloseConnection)
       if myReader.Read() Then
           Label11.Text = myReader.Item(0).ToString()
       End If
    End Using
End Using
End Using

score 0 · Answer 2 · answered Feb 27 '15 at 16:12

I also Fixed my previous faulty query and it is working.

   Dim x1 As New MySqlConnection("Server=test;Database=mdxmain;UID=test;PWD=testt;")
            x1.Open()
            Dim comx1 As New MySqlCommand("SELECT COUNT(*) as c FROM toutcome WHERE AffID = '" & CType(Session.Item("affID"), String) & "' AND CompletedDate >= '" & TextBox1.Text & "' AND CompletedDate <= '" & TextBox22.Text & "' AND Status = 'A'", x1)
            Dim myReaderx1 As MySqlDataReader = comx1.ExecuteReader(CommandBehavior.CloseConnection)
            myReaderx1.Read()
            Label12.Text = myReaderx1.Item(0).ToString()

            myReaderx1.Close()

I think you have really made at least a couple of errors here. No Using, string concatenation. Did you read this [Sql Injection Explained](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — Steve, May 20 '15 at 16:49

Search Filtering Mysql / Gridview in VB.Net

2 Answers2