Dim i As Integer
Using oConn As SqlConnection(connectionString), oCmd As SqlCommand("SELECT COUNT(expdate) FROM table WHERE expdate <= @ExpDate",oConn)
oCmd.Parameters.AddWithValue("ExpDate",Date.Now)
oConn.Open
i = oCmd.ExecuteScalar
End Using
If i = 0 Then
Else
End If
This uses parameters. While I said above that in this case it is not required to deal with SQL Injection, it is still good to do:
1: Get into the habit of using parameters always, so you don't slip and miss doing it where it does create a vulnerability.
2: More readable code
3: Reusable if you do many commands from the same SqlCommand object
SELECT * is expensive. Here I am selecting the count of the field I am querying on only.
Using the Using block means that the SqlConnection and SqlCommand objects get disposed of automatically when I have finished with them, and if an unhandled exception is thrown.
I am throwing the result into a variable outside of the Using block so that the processing of the result can be done after we have disconnected from the database, freeing up those resources. If you declare i within the Using block then it will only have scope there.
I am using Date.Now.Date.ToString("s") to get the date out as an ISO format date, yyyy-MM-ddTHH:mm:ss, so that internationalisation won't apply.
I did also realise when editing to add that comment in where your problem is in your original code.
You are using DateTime.Now, which would give the time to the millisecond. You are then using an equals comparison on your data. I am assuming expiry date is a date with no time element, so your code would only ever find something if it were run at exactly midnight.
By using DateTime.Now.Date (Or Date.Now.Date) you are only using the date element.
Edit 2: I changed mine to Date.Now.Date, then realised I didn't need to do that since I am checking for dates prior to or equal to now.