So I have an sql database, that contains One Column known as 'users' - and underneath the 'users' table are the following rows; member_id ( randomly generated ), login ( the username ), passwd ( the password ), expiry ( when the service expires, such as service expires on August 18, 2016. This expiry is in Unix Time Stamp :\ ), type ( the type of account, e.g; admin or user ), and the key they purchased to register with -- all of which is under the database 'vpam_xxxxxtest', I developed a visual basic login system that selects from the users table, the username and password, and checks the user input, then logs in; if the data matches what's in the database, it works perfectly, but at the same time, I was wondering how would I code it to check the users' expiry data, and if the account is expired, then show another form?
Dim Myadapter As New MySqlDataAdapter
Dim Sqlquery = "SELECT * From users WHERE login='" & UsernameTextBox.Text & "'AND passwd ='" & StringtoMD5(PasswordTextBox.Text) & "';"
Dim Command As New MySqlCommand
Command.Connection = MysqlConnection
Command.CommandText = Sqlquery
Myadapter.SelectCommand = Command
Dim Mydata As MySqlDataReader
Mydata = Command.ExecuteReader
If Mydata.HasRows = 0 Then
How would I integrate the below SQL solution into the code?
This is simple, assuming that you know how to perform SQL queries. As you said on your comment you check if the user credentials are correct (username and password) in this process you can fetch the users' expiry date with this code
String usr = "the username"
String pw = "the password"
You make the connection to your database (MSSQL, MySQL, or other) and perform this query
SELECT expiry FROM users WHERE login = usr AND passwd = pw
This way you will make 2 things at the same time, check if the login is correct and get the expiry date (you can obtain more data if you want by altering the query).
Why this method? It will only perform one query to the database making the program faster.
Then you check if the query returned any rows, if yes you run the current unix date against the one that you've obtained on the query If you want to specify the errors like (invalid username, invalid password) and so on you must run 2 queries making the program slower
SELECT member_id FROM users WHERE login = usr
SELECT expiry FROM users WHERE passwd = pw AND member_id = uid
First query to check if the user exists, if it does it returns the member id
Second query to check if the password matches the member_id's password and returns the expiry date.
If it worked just tag as answer, if you have any doubts just ask