login with ms access table

[plusplus]

I have a login control, when user presses login I want to check if he exists in the table.
I made a connection to database, and now how do I check if he exists?

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
Dim sqlstring As String
sqlstring = "SELECT id FROM tbl_users WHERE name = " & Login1.UserName & "AND password= " & Login1.Password
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\Dokumente und Einstellungen\USER\Eigene Dateien\Login.mdb;User Id=admin;Password=;")
Dim dbcomm As New OleDbCommand(sqlstring, cn)

cn.Dispose()
End Sub

[bala24]

Call Executescalar with your command object. It returns a single value like id in your case if the query is successful.

If it is nothing then the login fails, otherwise, allow the user.

[plusplus]

I need some more help, I don't know how to go about it
In web.config I have
<authorization>
<deny users="?"/>
<allow users="*"/>
</authorization>
<authentication mode="Forms"/>

In my login form I have a login control with this code

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
Dim sqlstring As String
sqlstring = "SELECT id FROM tbl_users WHERE name = " & Login1.UserName & "AND password= " & Login1.Password
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\Dokumente und Einstellungen\USER\Eigene Dateien\Login.mdb;User Id=admin;Password=;")
Dim dbcomm As New OleDbCommand(sqlstring, cn)

cn.Dispose()
End Sub

Now if I add to the above code
if dbcomm.executescalar <> nothing then ??????? what do I do to tell my web.config login was successfull?

[bala24]

Try this article.
It will clarify your doubts about form authentication.

http://www.eggheadcafe.com/articles/20020906.asp

If you think that's difficult to digest, go through this one..

http://www.csse.monash.edu.au/courseware/cse2030/2004/login.html
Hope this helps.

[plusplus]

I'll take a look at it, but maybe you know a place with vb code not c?

[plusplus]

Try this article.
It will clarify your doubts about form authentication.

http://www.eggheadcafe.com/articles/20020906.asp

If you think that's difficult to digest, go through this one..

http://www.csse.monash.edu.au/courseware/cse2030/2004/login.html
Hope this helps.

I'll try it out and get back if I need some more help.

[bala24]

Well the second url I sent is in vb and its quite easy to understand as well.

Try that one.

[plusplus]

Here is what I have so far, the bold line gives me an error, besides does this code look right?

Dim myid As Long
Dim sqlstring As String
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\Dokumente und Einstellungen\USER\Eigene Dateien\Login.mdb;User Id=admin;Password=;")
Dim dr As New OleDbDataReader
sqlstring = "SELECT id FROM tbl_users WHERE name = ? AND password= ?"
Dim dbcomm As New OleDbCommand(sqlstring, cn)

dbcomm.Parameters.AddWithValue("name", Login1.UserName)
dbcomm.Parameters.AddWithValue("password", Login1.Password)

If dbcomm.ExecuteScalar <> Nothing Then
dr = dbcomm.ExecuteReader()
myid = dr(1)
System.Web.Security.FormsAuthentication.RedirectFromLoginPage(myid, False)
End If

cn.Dispose()

[bala24]

Well you don't need that line at all.
The ExecuteScalar method returns a single value eg the number of records affected in an update query.
In your case it holds the id of the user which is an authenticated user.

If he is not, the ExecuteScalar will return nothing.

So what you could do id this.

Dim strid as string 'Assuming your id is a string value

strid=dbcomm.ExecuteScalar()

If not strid is nothing Then
	System.Web.Security.FormsAuthentication.RedirectFromLoginPage(strid, False)
End If

Hope its clear.