Click here to Skip to main content
15,749,072 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an Application in To manage subscribers
Each subscriber has a subscription expiration date

how I can when the Application start make check for all subscribers
and If you find a subscriber whose subscription endstoday, you'll see
a message telling you that subscribers are subscribed today

What I have tried:

Dim readers As SqlDataReader

    Dim query As String
    query = "Select * from table name where expdate='" & DateTime.Now & "'"
    command = New SqlCommand(query, con)
    readers = command.ExecuteReader

    Dim count As Integer
    count = 0
    While readers.Read
       count = count + 1
    End While


        If count = 0 Then

           MsgBox("لا يوجد اشتركات منتهية")
            MsgBox("هناك اشتراكات تنتهي اليوم")
        End If
Updated 16-Oct-17 0:35am
CHill60 15-Oct-17 12:45pm    
What's wrong with what you have tried?
Your code is vulnerable to sql injection. Use sql parameters instead of string concatenation.
Why not use COUNT (*) in your query instead of counting the records in a loop?
Lockwood 16-Oct-17 6:28am    
This is one of the few examples where that is not going to be a problem - Date.Now is not user-provided information.

Edit: That sounded a lot meaner than I meant it to.
You are correct, parameters are important things to use.
Richard Deeming 17-Oct-17 14:07pm    
DateTime.Now isn't. But if the user has any influence over the current culture settings, the string representation of it might be.
Lockwood 18-Oct-17 7:02am    
That's a fair point - I'd not thought of the possibility of using culture settings as an attack vector.

1 solution

Dim i As Integer
Using oConn As SqlConnection(connectionString), oCmd As SqlCommand("SELECT COUNT(expdate) FROM table WHERE expdate <= @ExpDate",oConn)
    i = oCmd.ExecuteScalar
End Using
If i = 0 Then
    'No rows found
    'Rows found
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.
Share this answer
Richard Deeming 17-Oct-17 14:09pm    
Assuming the data is stored correctly, you don't want to convert the date to a string before you pass it in. Just set the parameter value to Date.Now; the value will be passed as the correct data-type, with no i18n issues to worry about.
Lockwood 18-Oct-17 7:04am    
I think I had my mind still working in part on the string concatenation from the original questions, which took me down that road.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900