Help needed with coding a login form (Database)

[g99ma]

Hello,

I am kinda new to vb net 2003 and programming in general. I am working on a school project and trying to code my login form to compare the string entered with the data in my password table in a VideoInc database.

This is what i have:

' Must add data namespaces.
' This one for data in general.
Imports System.Data
' This one for OleDB which handles Microsoft Access.
Imports System.Data.OleDb
Public Class frmLogin
Inherits System.Windows.Forms.Form

Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click


Dim connectionString As String = _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = " & Application.StartupPath & "\VideoInc.mdb;" & _
"User ID = Admin;" & _
"Password = "
Dim logInOleDbConnection As New OleDbConnection(connectionString)

' Create a ADO.NET OleDbCommand.
Dim sqlQueryString As String = "SELECT * FROM Password WHERE " & _
"Name = '" & txtName.Text & "' " & _
"AND Password = '" & txtPassword.Text & "'"
Dim sqlCommand As New OleDb.OleDbCommand(sqlQueryString, logInOleDbConnection)
' Open the database connection.
logInOleDbConnection.Open()

' Run the sqlCommand.
Dim MyResult As Object
MyResult = sqlCommand.ExecuteScalar
Dim countFound As Integer = CType(MyResult, Integer)

' Close the database connection.
logInOleDbConnection.Close()
' If countFound = 1 a record with a matching Name and Password was found..
If countFound = 1 Then
Me.Hide()
FM.mnuFile.Enabled = True
FM.mnuSystemAdmin.Enabled = True
FM.mnuOperations.Enabled = True
FM.mnuFrontOffice.Enabled = True
Else
MsgBox("The information is incorrect. Please try again", vbCritical + vbOK, "Incorrect Information")
For Each ctl As Control In Me.Controls
If TypeOf ctl Is TextBox Then
CType(ctl, TextBox).Clear()
End If
Next
Me.txtName.Focus()
End If

However everytime I try to run this it comes up with this OleDbException

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

and highlights this line in my code:
MyResult = sqlCommand.ExecuteScalar


Can anybody please kindly help me have a look at this code at tell me what I may doing wrong? Also is there a way for me to to confirm that my data source path is correct? Thanks for your anticipated help

 

[gopal2006]

Login Form



Use Try Catch block in your code as shown below

Dim logInOleDbConnection AsNew OleDbConnection(connectionString)
' Create a ADO.NET OleDbCommand.
Dim sqlQueryString AsString = "SELECT * FROM Password WHERE " & _
"Name = '" & txtName.Text & "' " & _
"AND Password = '" & txtPassword.Text & "'"
Dim sqlCommand AsNew OleDb.OleDbCommand(sqlQueryString, logInOleDbConnection)
' Open the database connection.
After this line of code
Try
logInOleDbConnection.Open()

' Run the sqlCommand.
Dim MyResult AsObject
MyResult = sqlCommand.ExecuteScalar
Dim countFound AsInteger = CType(MyResult, Integer)

' Close the database connection.
logInOleDbConnection.Close()
' If countFound = 1 a record with a matching Name and Password was found..
If countFound = 1 Then
Me.Hide()
FM.mnuFile.Enabled = True
FM.mnuSystemAdmin.Enabled = True
FM.mnuOperations.Enabled = True
FM.mnuFrontOffice.Enabled = True
Else
MsgBox("The information is incorrect. Please try again", vbCritical + vbOK, "Incorrect Information")
ForEach ctl As Control InMe.Controls
IfTypeOf ctl Is TextBox Then
CType(ctl, TextBox).Clear()
EndIf
Next
Me.txtName.Focus()
EndIf
Catch ex as oledb.oledbexception

Msgbox("exception" & ex.tostring)

end try


I feel this will work out

 

[jmcilhinney]

You're using the wrong query. As it stands your SQL code is getting the all the data for every record that matches your criteria. That is NOT what you want. You want a single number that indicates how many rows match your criteria. To do that you would use "SELECT COUNT(*) ...". Also, using that intermediate Object variable is not necessary. You know that ExecuteScalar is going to return an Integer so just cast it straight off. In fact you don't even have to use an Integer variable as all you are interested in is whether it is greater than zero or not:

If CInt(myCommand.ExecuteScalar()) = 0 Then
    MessageBox.Show("Login failed.")
Else
    MessageBox.Show("Login successful.")
End If