Help for code in VB.NET

Question

0.00/5 (No votes)

See more:

I have an Application in vb.net 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

    con.Open()
    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

        con.Close()

        If count = 0 Then

           MsgBox("لا يوجد اشتركات منتهية")
        Else
            MsgBox("هناك اشتراكات تنتهي اليوم")
        End If

Posted 15-Oct-17 6:25am

Member 13339518

Updated 16-Oct-17 0:35am

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Lockwood · Answer 1 · 2017-10-16T00:35:00

VB

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
    'No rows found
Else
    '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.