login code

[jnash]

hi there i havent coded in ages im looking for a tutorial for a form that takes two textbox fields user/pass and searchs through the username and password fields in a access database and either allows in or disallows

i did this for a college project a long time ago but i just cant find it, i have googled before you ask but its more reading and showing not for what i want thanks in advance

Jonathan

 

[arjmand]

Hey Buddy try this

say the login check is made on click event of a button named day btnOK
here is code:

'Make connection to database named say abc.mdb having two columns userID and Password

dim conn as new OLEDB.OLEDBConnection(" Provider= Microsoft.JET.OLEDB.4.0; Data Source=C:\abc.mdb")

Private Sub btnOK_Click(byVal Sender as Object, ............)

conn.open 'Open the connection

dim a,b as string

a=textbox1.text 'say this textbox has ID
b=textbox2.text 'and this for PAssword

dim findRec as new OLEDB.OLEDBCommand("Select count(*) from tableName where userID='" & a & "and Password='" & b & "'")

'The above query searches for the values in variables a and b from the database table.

'now execute the above query using the command below

findRec.ExecuteScalar 'This query returns a number of records found on the basis of query written

'next line
if findRec.ExecuteScalar>0 then
MessageBox.show("Login Succeded")
else
MessageBox.show("Reenter ID and Password")
End Sub

If this helps I ll be glad, if not do reply I ll try to help more, coz I had these problems myself earlier and had to bang my head around for these simple things.

 

[jnash]

thank you very much the only problem is the findrec.exescalar bit that isnt initialising do i need to add a libary class or something

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: ExecuteScalar: Connection property has not been initialized.

thanks

 

[jmcilhinney]

Pass the OleDbConnection object that you created earlier to the second argument of the OleDbCommand constructor:

Dim myConnection As New OleDbConnection("connection string here")
Dim myCommand As New OleDbCommand("SQL code here", myConnection)

Try
    myConnection.Open()

    'Execute query here.
Finally
    myConnection.Close()
End Try

 

[jnash]

thanks however

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

any ideas??

heres my code


Public Class frmSplash
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
'Make connection to database named say abc.mdb having two columns userID and Password
Dim conn As New OleDb.OleDbConnection(" Provider= Microsoft.JET.OLEDB.4.0; Data Source=db.mdb")
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
conn.Open() 'Open the connection
MessageBox.Show("connection open badboy")
Dim a, b As String
a = txtUsername.Text 'say this textbox has ID
b = txtPassword.Text 'and this for PAssword
Dim findRec As New OleDb.OleDbCommand("Select count(*) from tblUsers where UserName='" & a & "and Password='" & b & "'", conn)

'The above query searches for the values in variables a and b from the database table.
'now execute the above query using the command below
findRec.ExecuteScalar() 'This query returns a number of records found on the basis of query written
'next line
If findRec.ExecuteScalar > 0 Then
MessageBox.Show("Login Succeded")
Else
MessageBox.Show("Reenter ID and Password")
End If
conn.Close()
End Sub
End Class

it breaks at the executescalar command!

 

[jmcilhinney]

This is a perfect example of why you should NEVER use string concatenation to build SQL statements and also why a simple debug will save you all sorts of time. You have neglected to add a single quote to the end of the UserName value. Had you used parameters instead this would not have been an issue, and had you simply displayed the value of the command's CommandText property you would have seen it. If the code breaks when you execute an SQL statement then the most likely culprit is the SQL code, so that that's the first thing to look at If you had placed a breakpoint and tested the actual string you'd built, or even just used a messageBox, you have seen something like:

[SIZE=2]Select count(*) from tblUsers where UserName='ABCD and Password='EFGH'[/SIZE]

To avoid the potential for issues like this you should always use parameters:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] findRec [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT COUNT(*) FROM tblUsers WHERE UserName = @UserName AND Password = @Password", conn)
[/SIZE][SIZE=2]
findRec.Parameters.Add("@UserName", txtUsername.Text)[/SIZE][SIZE=2]
findRec.Parameters.Add("@Password", txtPassword.Text)[/SIZE]

Note that I've also done away with the 'a' and 'b' variables. They add no value whatsoever and they actually make the code harder to read because the names are meaningless.

 

[jnash]

thank you so much it finally works every one has been so helpfulthanks again jon