I am trying to code the login page, here is my code:
'login form code
Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
Dim username As String = txtUsername.Text.Trim
Dim pwd As String = txtPassword.Text.Trim
Dim insertQry As String = "select 1 from UserInfo where username = '" & username & "' and userpassword = '" & pwd & "'"
Dim res As Boolean = executeReader(insertQry)
End Sub
database module
Imports System.Data.SqlClient
Module DBconn
Public conn_ As New SqlConnection("")
Public Function executeReader(ByVal query As String)
Try
Dim cmd As New SqlCommand(query, conn_)
conn_.Open()
Dim r2 As SqlDataReader = cmd.ExecuteReader()
Return True
Catch ex As Exception
Return False
End Try
End Function
End Module
My question is how to do validation of username and password check with select 1 query?
Take a look at this example, it does the following:
Private Function ValidateLogin(ByVal username As String, ByVal password As String) As Boolean
Dim count As Integer = 0
'Declare the connection object
Using con As SqlConnection = New SqlConnection
'Wrap code in Try/Catch
Try
'Set the connection string
con.ConnectionString = "" 'TODO: set this value
'Create a new instance of the command object
Using cmd As SqlCommand = New SqlCommand("SELECT Count(UserInfoId) FROM UserInfo WHERE username=@username AND userpassword=@password", con)
'Parameterize the query
With cmd.Parameters
.Add("@username", SqlDbType.VarChar).Value = username
.Add("@password", SqlDbType.VarChar).Value = password
End With
'Open the connection
con.Open()
'Use ExecuteScalar to return a single value
count = Convert.ToInt32(cmd.ExecuteScalar())
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
End If
End Try
End Using
'Return row count is greater than 0
Return count > 0
End Function