Original thread - To see why this was Updated
Reason for new thread is to start with a clean slate and I will also be providing a tutorial for SQL and possibly Oracle DB connections to show how easy this code is to implement regardless of the DB you are using.:p

As well, if time permits I may take the time to code this in a few other .Net languages (C#, C++, and J#).

This is sample code for a ASP.Net Login page (using Visual Basic.Net code behind) with OleDB connection to an Access Database using ADO.Net.

Pre-Code Setup::-|
1. Create a New ASP.NET Web Application. I called mine NorthLogin2
2. Rename the default aspx Webform to Login or Index or something more descriptive. I called mine Login2.aspx
3. Assuming you are in Visual Studio.Net right click on this form in the Solution Explorer and Select Set as Start Page.
4. Find your Northwind.mdb file (The classic Northwind Database in Access)
Under Tools, Options, Tables/Queries Tab select Run Permissions as Owner's
5. Create a new table for login requirements. I called mine tblUser
Columns U_id (autonumber), U_Name (text), and U_Password (text with Input Mask of Password)
6. Create Query (i.e. Stored Procedure) to validate login(s). I called mine sp_ValidateUser
In the SQL Designview the code for that query is

SELECT COUNT(*) AS Num_of_User
         FROM tblUser
         WHERE (((tblUser.U_Name)=[@UserName]) AND ((tblUser.U_Password)=[@Password]));

This query counts the number of users that it retrieves which matches the where clause. The @UserName and @Password are the values passed in to the query from the Webform.
7. Close database, because you can not access it while it is open.

Let the coding begin::cheesy:
1. Create the API for your Login Webform. You want to have two textboxes for the user to enter their login information. You will also want to have some kind of visual feedback to the user regarding the status of the login; i.e. Is the login successful? Usually that is done by redirecting to the page they are trying to login to. If it is not successful, and whether they provided the information in the two textboxes.

Login2.aspx

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Login2.aspx.vb" Inherits="NorthLogin2.WebForm1"%>
        <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
        <html>
        	<head>
        	    <title>Northwind Database Login</title>
        		<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
        		<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
        		<meta content="JavaScript" name="vs_defaultClientScript">
        		<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
        		<!-- <summary>
        			|||||	Style Sheet |||||
        			</summary>
        	    --><link title="standard" href="Styles.css" type="text/css" rel="stylesheet">
        	</head>
        	<body>
        		<!-- |||||	Login Form	||||| -->
        		<form id="frmlogin" method="post" runat="server">
        		    <table id="mainTable">
        		        <tr>
        		    	    <td>
        		    		    <table class="t_border" id="loginTable" cellspacing="15" cellpadding="0">
        		    		        <tr>
 					 <td><b>Login: </b>
		 		 	 </td>
 					 <td><asp:textbox id="txtUserName" runat="server" width="160px"></asp:textbox><asp:requiredfieldvalidator id="rvUserValidator" runat="server" display="None" errormessage="You must supply a Username!"
		 		 		 controltovalidate="txtUserName"></asp:requiredfieldvalidator></td>
        		    		        </tr>
        		    		        <tr>
 					 <td><b>Password: </b>
		 		 	 </td>
 					 <td><asp:textbox id="txtPassword" runat="server" width="160px" textmode="Password"></asp:textbox><asp:requiredfieldvalidator id="rvPasswordValidator" runat="server" display="None" errormessage="Empty Passwords not accepted"
		 		 		 controltovalidate="txtPassword"></asp:requiredfieldvalidator></td>
        		    		        </tr>
        		    		        <tr>
		 		 		<td align="center" colspan="2"><asp:button id="cmdSubmit" runat="server" borderstyle="Solid" text="Submit"></asp:button></td>
        		    		        </tr>
        		    		    </table>
        		    	    </td>
        		        </tr>
        		        <tr>
        		    	    <td>
        		    		    <table id="messageDisplay">
        		    		        <tr>
 					 <td><asp:validationsummary id="Validationsummary1" runat="server" width="472px" displaymode="BulletList"></asp:validationsummary></td>
        		    		        </tr>
        		    		    </table>
        		    	    </td>
        		        </tr>
        			</table>
        		</form>
        	    <asp:label id="lblMessage" runat="server" width="288px" forecolor="#C00000" font-size="Medium"
        		    font-italic="True" font-bold="True"></asp:label>
        	    <!--	|||||    End of Form	|||||    -->
        	</body>
        </html>

As you can see by the above code I require my users to supply a Username and Password, the requirefieldvalidator object does just that.

2.Code for the communication to the database:
a.Add the following line of code to the Web.Config file
- start right after
<?xml version="1.0" encoding="utf-8"?>
<configuration>

<!--    |||||	Application Settings    |||||	-->
          <appSettings>
        	<add key="strConn" value="Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Northwind.mdb;User ID=Admin;Password=;"/>
          </appSettings>
          <system.web>

These lines of code do several things. 1. Eliminates the need for creation of a connection string (strConn) each time you want to connect to the Database(DB). Who wants to write that out each time. Now you have "pseudo-global" variable for use in your entire application. 2. Makes it secure, so no user can see where the DB is actually located. 3. And most importantly tells the application the location of the said DB.

b.Code the "meat" of the Login in page.
- Mine is in the code behind rather than a script block, but the principles are the same.
- Add the following Imports to your code behind, just above the class declaration.

Imports System.Web.Security '   |||||   Required Class for Authentication
        Imports System.Data '   |||||   DB Accessing Import
        Imports System.Data.OleDb   '   ||||||  Access Database Required Import!
        Imports System.Configuration    '   ||||||  Required for Web.Config appSettings |||||

This will provide the library imports you need for Authentication, accessing an OleDB (i.e. Access), and accessing the web.config file (contains your connection string)

c. Create a Function to connect to the DB return result(s)
- This is the main function of this entire webform.

Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean
        		'<sumamry>
        		'   |||||   Declare Required Variables
    		' ||||| Access appSettings of Web.Config for Connection String (Constant)
        		'</summary>
 		' ||||| First is the Connection Object for an Access DB
        		Dim MyConn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
        		'   |||||   This is the Connections Object for an SQL DB
        		SqlConnection(ConfigurationSettings.AppSettings("strConn"))
        
        		'<sumamry>
        		'   |||||   Create a OleDb Command Object
        		'   |||||   Pass in Stored procedure
        		'   |||||   Set CommandType to Stored Procedure
        		'</summary>
        
     		' ||||| To Access a Stored Procedure in Access - Requires a Command Object
        		Dim MyCmd As New OleDbCommand("sp_ValidateUser", MyConn)
        		'   |||||   To Access a Stored Procedure in SQL Server - Requires a Command Object
        	   
        		MyCmd.CommandType = CommandType.StoredProcedure
        		'   |||||   Create Parameter Objects for values passed in
        		Dim objParam1, objParam2 As OleDbParameter
        		'<sumamry>
        		'   |||||   Add the parameters to the parameters collection of the
   		' ||||| command object, and set their datatypes (OleDbType in this case)
        		'</summary> 
        		objParam1 = MyCmd.Parameters.Add("@UserName", OleDbType.Char)
        		objParam2 = MyCmd.Parameters.Add("@Password", OleDbType.Char)
        
        		''   |||||   Set the direction of the parameters...input, output, etc
        		objParam1.Direction = ParameterDirection.Input
        		objParam2.Direction = ParameterDirection.Input
        		''   |||||   Set the value(s) of the parameters to the passed in values
        		objParam1.Value = strUserName
        		objParam2.Value = strPassword
        
        		'   |||||   Try, catch block!
        		Try
        		    '   |||||   Check if Connection to DB is already open, if not, then open a connection
        		    If MyConn.State = ConnectionState.Closed Then
        			    '   |||||   DB not already Open...so open it
        				MyConn.Open()
        			End If
        
        		    '   |||||   Create OleDb Data Reader
        		    Dim objReader As OleDbDataReader
        		    objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
        		    '   |||||   Close the Reader and the Connection Closes with it
        
        			While objReader.Read()
        			    If CStr(objReader.GetValue(0)) <> "1" Then
        				    lblMessage.Text = "Invalid Login!"
        				Else
        				    objReader.Close()   '   |||||   Close the Connections & Reader
        				    Return True
        				End If
        			End While
        		Catch ex As Exception
			lblMessage.Text = "Error Connecting to Database!"
        		End Try
        
        
        	End Function

d. Code the button click event for submitting login to DB
- The code for the onClick event of the Submit button is as follows, but this is very basic and simple. I will be expanding on this further with things like have a maximum number of attempts, and what happens when a user tries to access a page in the application without having logged in; it should redirect the user to the login page/form, otherwise what is the purpose of the login form.

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
        		If Page.IsValid Then    '   ||||| Meaning the Control Validation was successful!
        		    '   |||||   Connect to Database for User Validation |||||
        	 If DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()) Then
        	 FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False)  '   |||||   default.aspx Page!
        			Else
        	 Else
					 '   |||||   Credentials are Invalid
					 lblMessage.Text = "Invalid Login!"
					 '   |||||   Increment the LoginCount (attempts)
					 'Session("LoginCount") = CInt(Session("LoginCount")) + 1
					 ' ||||| Determine the Number of Tries
					 'If Session("LoginCount").Equals(intMaxLoginAttempts) Then
					 '	Response.Redirect("Denied.aspx")
					 'End If
        	 
					 'If CInt(Session("Num_of_Tries")) > 2 Then ' ||||| If Exceeds then Deny!
					 '	Response.Redirect("Denied.aspx")
					 'End If
        	 
        				 End If
        			 End If
        	End Sub

3. Create the page to send the user to once login is successful
- In Visual Studio.Net go to File -> Add New Item -> Webform
- Name it default.aspx
- For ease at this time, just put a text message saying something like "Successful Login".
- It is this page that ASP.Net will automatically look for once login is successful, so if you do not create it you will get an application error.

4. Compile and run your code!

That is the end of this basic outline of a Login Page using ASP.Net. There were some moderate level of difficulty coding, but I believe my comments inline with the code should clarify any questions you may have. If by chance you have some questions, please post them here.

Please DO NOT POST replies to this thread that say things like - "Mine doesn't work!" without providing details of what errors you got, what does happen, etc.

I can't help you if you do not provide details! Let me say that again I can't help you if you do not provide details! And preferably any code alerations you may have done to the above code for your specific
application.

Happy Coding.:cool:

SQL Variation on the above Simple Login Page in ASP.Net (w/ VB.Net)

** I am using Visual Studio.Net and SQL Server 2000, but the principles are still the same regardless of your IDE tools ***

1. Follow the above steps for creation of the Application and Page
a. Created a WebForm called the ASP.Net Page Login.aspx
b. Coded the WebForm in HTML

2. Modify the Web.Config file and add the connection string to SQL Server(as was above..BUT DIFFERENT!)

- This assumes your SQL Server is located locally on your system, and not remotely.
- Add these lines of code the Web.Config file just after <configuration>.

Web.Config:

<!--    |||||	Application Settings    |||||	-->
           <appSettings>
         	<add key="strConn" value="Data Source=(local);database=Northwind;User id=Paladine;Password=;"/>
           </appSettings>
           <system.web>

- If the server is located remotely, then there is a slight modification to the connection string to be made

Remote Connection:

<appSettings>
         	<add key="strConn" value="Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=Paladine;Password=;"/>
           </appSettings>

- Data Source is the IP Address of the Server hosting the SQL Server DB
- 1433 is the standard port for SQL Server

3. Declare the Imports required

- These give you access to the libraries and data access methods you need to connect the Login Page to the data base.

Imports:

Imports System.Web.Security	 '   ||||||   Required Class for Authentication
         Imports System.Data			 '   ||||||   DB Accessing Import
         Imports System.Data.SqlClient   '   ||||||   SQL Server Import
         Imports System.Configuration    '   ||||||   Required for Web.Config appSettings |||||

4. As was done in the above example for access, create the OnClick Event code for the Submit Button

- I am using the codebind, rather than the script method of coding the Visual Basic Portion of the Application.
- Once this button is pressed, the ASP.Net Page will validate the data provided by the user before it transmits anything to the server.
- If the date meets the validation criteria, then the event will call the DBConnection Function.
- But before we get to the DBConnection Function, here is the code for the OnClick Event

OnClick_Event:

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
         		If Page.IsValid Then    '   ||||| Meaning the Control Validation was successful!
         		    '   |||||   Connect to Database for User Validation |||||
         		    If DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()) Then
         			    FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False)  '   |||||   default.aspx Page!
         			Else
         			    '   |||||   Credentials are Invalid
         			    lblMessage.Text = "Invalid Login!"
         			End If
         		End If
         	End Sub

5. Create the table that stores the User Login Credentials

- This can be easily done in Visual Studio.NET or vial query analyzer.
- Use which ever method you want, I will show the code as you would enter it in query analyzer

Table Script:

CREATE TABLE NorthWindUsers 
         	(UserID INT IDENTITY(1,1) NOT NULL,
         	 UserName VARCHAR(50) NOT NULL,
         	 Password VARCHAR(50) NOT NULL)

6. Insert some data into the newly created table.
- This is just for teaching purposes, but you could wrap this code up into a Store Procedure and use it in a Add New User page in your application.

Insert Script:

INSERT INTO NorthWindUsers
         	(UserName, Password)
         	VALUES ('Admin', 'Admin')

7. As in the AccessDB example you must create the stored procedure which takes the values pass to it and validates them against the database.

- The values being passed to the store procedure are the User Name and Password.
- The store procedure can be created by two methods with my setup :

a. In Visual Studio
- Create a server connection to your SQL Server in the Server Explorer.
- Once you have this connection select the Northwind Database
- In the Visual Studio.Net IDE select the Database Menu->New Stored Procedure
- Follow the code sample below to create the stored procedure. **DO NOT Forget to Rename the default stored procedure name i.e.StoreProcedure1**

b. In Query Analyzer
- Create the stored procedure as by following the sample code below

Things to note:

/* [b]JUST TO SHOW YOU A DIFFERENCE BETWEEN[/b] Visual Studio.Net and Query Analyzer */
         CREATE PROCEDURE dbo.sp_ValidateUser /* How it would appear in Visual Studio.Net */
         CREATE PROCEDURE sp_ValidateUser /* How it would appear in QUERY ANALYZER */

The Store Procedure Script:

CREATE PROCEDURE sp_ValidateUser /* How it would appear in QUERY ANALYZER */
         	(
         		@UserName VARCHAR(50) = NULL,
         		@Password VARCHAR(50) = NULL,
         		@Num_of_User INT = 0
         	)
         AS
         	SET @Num_of_User = (SELECT COUNT(*) AS Num_of_User
         	FROM NorthWindUsers
         	WHERE UserName = @UserName AND Password = @Password)
         RETURN  @Num_of_User

8. As in the AccessDB Example of this login page you need to create the DBConnection Function
- You will have to modify the code from the AccessDB example to work for SQL server.
- You will declare a new parameter, add it to the parameter colleciton, and have it set to receive the return value from the stored procedure.
- Determine if the return value is greater than 0 (meaning the SQL DB found the user based on the creditenials provided).

DBConnection Function:

Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean
         		'<sumamry>
         		'   |||||   Declare Required Variables
     		' ||||| Access appSettings of Web.Config for Connection String (Constant)
         		'</summary>
         		'   |||||   This is the Connections Object for an SQL DB
         		Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
         
         		'<sumamry>
         		'   |||||   Create a OleDb Command Object
         		'   |||||   Pass in Stored procedure
         		'   |||||   Set CommandType to Stored Procedure
         		'</summary>
         
  		' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object
         		Dim MyCmd As New SqlCommand("sp_ValidateUser", MyConn)
         
         		MyCmd.CommandType = CommandType.StoredProcedure
         		'   |||||   Create Parameter Objects for values passed in
         		Dim objParam1, objParam2 As SqlParameter
         		'   |||||   Create a parameter to store your Return Value from the Stored Procedure
         		Dim objReturnParam As SqlParameter
         		'<sumamry>
         		'   |||||   Add the parameters to the parameters collection of the
    		' ||||| command object, and set their datatypes (OleDbType in this case)
         		'</summary> 
         		objParam1 = MyCmd.Parameters.Add("@UserName", SqlDbType.VarChar)
         		objParam2 = MyCmd.Parameters.Add("@Password", SqlDbType.VarChar)
         		objReturnParam = MyCmd.Parameters.Add("@Num_of_User", SqlDbType.Int)
         
         		'   |||||   Set the direction of the parameters...input, output, etc
         		objParam1.Direction = ParameterDirection.Input
         		objParam2.Direction = ParameterDirection.Input
         		objReturnParam.Direction = ParameterDirection.ReturnValue   '   Note RETURNVALUE
		'' ||||| Set the value(s) of the parameters to the respective source controls
         		objParam1.Value = txtUserName.Text
         		objParam2.Value = txtPassword.Text
         
         		'   |||||   Try, catch block!
         		Try
         		    '   |||||   Check if Connection to DB is already open, if not, then open a connection
         		    If MyConn.State = ConnectionState.Closed Then
         			    '   |||||   DB not already Open...so open it
         				MyConn.Open()
         				MyCmd.ExecuteNonQuery()
         			End If
         		    '   |||||   Was the return value greater than 0 ???
         		    If objReturnParam.Value < 1 Then
         			    lblMessage.Text = "Invalid Login!"
         			Else
         				Return True
         			End If
         
         		    '   |||||   Close the Connection Closes with it
         			MyConn.Close()
         
         
         		Catch ex As Exception
  			lblMessage2.Text = "Error Connecting to Database!"
         		End Try
         
         
         	End Function

9. Create the default.aspx page (as in the above AccessDB example)
- This is the default page the application will go to following a successful login.

Your SIMPLE (kind of) SQL Login Page is COMPLETE! Compile and Run it, and if you followed my steps it will work!

Hope this helps everyone, and happy coding.:cool:

Disclaimer:
There is an even simplier version of this login page, down to the bare minimum of code. But I went the route I did for reasons of expanding, and eventually a tutorial on making this login code into a User Control that you can implement in a number of applications. Thus not having to recode this everytime you do a new application requiring a login.

Thanks a bunch Paladine. Almost there.

I ran the login page in debug mode and found this:
After MyCmd.ExecuteNonQuery(), it took me to the Catch ex As Exception

I double checked the strConn & Stored Procedure and they both work. Did it work on your DB?

-kendel

It's my bad. I didn't know that the params in the SP must match the params in .net page

Your example works perfect. Thanks again.

Thanks a bunch Paladine. Almost there.

I ran the login page in debug mode and found this:
After MyCmd.ExecuteNonQuery(), it took me to the Catch ex As Exception

I double checked the strConn & Stored Procedure and they both work. Did it work on your DB?

-kendel

hi Paladine,
i just tried ur code with this change in the webconfig:

<appSettings>
<add key="strConn" value="Provider = SQLOLEDB; Data Source=(local); Initial Catalog=users; Integrated Security=SSPI;"/>
</appSettings>

I get this error:

An analytical error message: Composition section appSettings cannot be
recognized.
Source error:
Line 12:<compilation defaultLanguage="vb" debug="true" />
Line 13:
Line 14:<appSettings>
Line 15:<add key="strConn" value="Provider = SQLOLEDB;Data Source=(local); Initial Catalog=users; Integrated Security=SSPI;"/>
Line 16:</appSettings>

I have a database called users and have a table called tbl_users in users database.

Im stuck using a Jap version of SQL server and visual studio.net, i havent used sql server b4, and dont know how if i saved the query correctly. could you give more details, sorri 2 b such a bother.
Cheers for any help!

hi hi!
i didnt see the 2nd posting u did and i changed my web.config to:
<appSettings>
<add key="strConn" value="Data Source=(local); database=users; user id=sa; password=chaos;"/>
</appSettings>

And i get this error:
An analytical error message: Type 'Final2.Global' was not able to be read.
Source error:
Line 1:<%@ Application Codebehind="Global.asax.vb" Inherits="Final2.Global" %>
im like huh? wats global.asax file do?

I started from scratch and its working now! ur a chamP!
cheers

Hmm.. Glad I could.... help! Hehe.

Happy Coding

Hi Paladine,
i just wanted to know, how can i insert more fields into the exisiting table on sql server?

In short. With difficulty. It depends, is there data (more than a few lines that ) in the table? When you modifiy an existing table with data in it, there can be issues with data integrity, as well as how is the new field going to affect the existing rows.

In the case of a few lines data, you can extract that data with Enterprise manager, and then Drop the table, recreate it with the new fields, and then modify the insert statements you exported with Enterprise manager to include data for the new fields...and voila. But it is a little more intensive than I made it sound. Unless you have the original insert statments that first populated the table. Like my example above.

thnks a bunch 4 all ur good work paladine!!

it really works!

but since im a novice in asp.net, ive few question 4 u..

in ur application, evry succesfull login will be directed to default.aspx page..is there another way to direct to other page? the simple way is by renaming the file, but sure there is other way rite??

2ndly, i just wanted to clarify, by having form authorization, user cannot have access to any page in the web application rite?? but, unfortunately, it can happen in my application..i can still go directly to any page i desired by typing the path in the browser..whut is wrong?do i need to include some portion of code in every page in the application to indicate user must first log on?? im really newbie in asp.net..hope u cud help thnks!

Thanks for the compliments!


To answer you second question first, I will say yes. Here is a synopsis of what you would have to do.

FormsAuthentication.RedirectFromLoginPage(username,createPersistentCookie)

when you pass FormsAuthentication.RedirectFromLoginPage a second parameter that equals false, like this: FormsAuthentication.RedirectFromLoginPage (UserName.Text, false)

RedirectFromLoginPage issues a temporary cookie, or session cookie, that expires when the browser is closed. If you pass true instead, RedirectFromLoginPage issues a persistent cookie thats good for 50 years.

You will want to modify this cookie, and you can check the cookie at the beginning of each page within the application and if it does not match you redirect to the login page. Hope that makes sense?

As for your second question, this is a setting under IIS, so simply go to the IIS managment console and modify the default page to whatever you like.

Hope this helps!

Take care

thnks a bunch 4 all ur good work paladine!!

it really works!

but since im a novice in asp.net, ive few question 4 u..

in ur application, evry succesfull login will be directed to default.aspx page..is there another way to direct to other page? the simple way is by renaming the file, but sure there is other way rite??

2ndly, i just wanted to clarify, by having form authorization, user cannot have access to any page in the web application rite?? but, unfortunately, it can happen in my application..i can still go directly to any page i desired by typing the path in the browser..whut is wrong?do i need to include some portion of code in every page in the application to indicate user must first log on?? im really newbie in asp.net..hope u cud help thnks!

Thanks to Paladine - This is exactly what I am looking for and trying to do. I have a question - when I try to create procedure...it gives me an error - Server: Msg 170, Level 15, State 1, Procedure sp_ValidateUser, Line 8
Line 8: Incorrect syntax near 'Num_of_User'.
What does it mean? How to fix it. Thanks for a million.

Hi there,

Thanks to Paladine, I've learned a lot from his .NET login example as well.

About the error, it's just a careless typo. To fix it, replace (.) with a commas(,) after NULL in the sencond line.

@UserName VARCHAR(50) = NULL,
@Password VARCHAR(50) = NULL,
@Num_of_User INT = 0

Thanks to Paladine - This is exactly what I am looking for and trying to do. I have a question - when I try to create procedure...it gives me an error - Server: Msg 170, Level 15, State 1, Procedure sp_ValidateUser, Line 8
Line 8: Incorrect syntax near 'Num_of_User'.
What does it mean? How to fix it. Thanks for a million.

You will want to modify this cookie, and you can check the cookie at the beginning of each page within the application and if it does not match you redirect to the login page. Hope that makes sense?

im so sorry 4 my low knowldge on this area, but cud u be more specific on this?? perhaps portion of coding to implement this?? thnks

hi hi! thanks for the reply Paladine about the table issue. The next thing i want to do is compare a username to a username in the database.
From this line: FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False)
If i set to say: RedirectFromLoginPage(txtUserName.Text, True)
what does that do? and how can I compare this username in another page?
What I am trying to do is:
if the username = <temp username> then
load an empty form
else if username =<member's username> Then
Load form with member's details already there

im trying to create an update details page.
no idea how to access username thats being passed from the RedirectFromLoginPage...
cheers

Kevin

Kendel, I saw that and I did change it with a commas(,) ...and got that error:
Server: Msg 170, Level 15, State 1, Procedure sp_ValidateUser, Line 8
Line 8: Incorrect syntax near 'Num_of_User'.

which is at this line: SET @ Num_of_User = (SELECT COUNT(*) AS Num_of_User

any ideas? Thanks

Ok, I will have to once again make this clear. *COPY & PASTE* only works well if you also understand the code. When I copy and paste the code I convert it with a vbColorCoder to have it look nice here. So sometimes extra spaces get added.

So in this case SET @ Num_of_User .... has a space between the @ and Num which it shouldn't, so please remove that and it should work.

So as I stated before, understanding the code and syntax is important!

:cool:
Hope this helps!

Kendel, I saw that and I did change it with a commas(,) ...and got that error:
Server: Msg 170, Level 15, State 1, Procedure sp_ValidateUser, Line 8
Line 8: Incorrect syntax near 'Num_of_User'.

which is at this line: SET @ Num_of_User = (SELECT COUNT(*) AS Num_of_User

any ideas? Thanks

Thanks Paladine. I saw the extra space and fixed it. Thanks for the advice.

No worries,

You can do something like this

Creating a Cookie with a custom Expiry Date
Alternatively you can create a cookie that's issued and has a custom lifetime instead of 50 years. The key is to replace the call to RedirectFromLoginPage method with the your own implementation, like so:

Dim cookie As HttpCookie = FormsAuthentication.GetAuthCookie (UserName.Text, chkPersistCookie.Checked)
 ' Expires in 30 days, 12 hours and 30 minutes from today.
 cookie.Expires = DateTime.Now.Add(New TimeSpan(30, 12, 30, 0))
 Response.Cookies.Add (cookie)
 Response.Redirect (FormsAuthentication.GetRedirectUrl (UserName.Text,chkPersistCookie.Checked))

Here we have created a new authentication cookie, explicitly set its expiration date, added the cookie to the Cookies collection of the current HttpResponse instance, and finally we redirect the user to the page that they had requested.

Hope this helps

im so sorry 4 my low knowldge on this area, but cud u be more specific on this?? perhaps portion of coding to implement this?? thnks

Paladine: I am not sure...has anyone had asked you this question or not. ...I did not all the posts.
As the way it code, if I know what the next page is such as Default.aspx ..I don't have to login...I can type the URL and it goes direct to that page. How can we prevent that? Thanks so much for your response.
(sorry, I am so new with this stuff).

Ok, I have had a number of people ask me how to prevent access to say the default.aspx page via the direct url, and you can prevent this in a number of ways. I have mentioned the use of cookies, but in the following example I will use another method of Session variables.

Continue from the existing code in this tutorial and add the following.

Open the Global.asax file and view the code. This file contains many elements, and the one we are going to focus on is the Session_Start subroutine.

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
		' Fires when the session is started
	End Sub

Add the follow line of code to this subroutine.

'   <summary>
		 '	   Track whether they're logged in or not
		 '   </summary>
		Session("Logged_IN") = "No"

This will setup a Session level variable to determine if the user has successfully logged in or not.

Now to the default.aspx page or your equivalent page to this (and any other pages within your application) add the following lines of code in the Page_Load subroutine.

Response.Cache.SetCacheability(HttpCacheability.NoCache)
		 If Session("Logged_IN").Equals("No") Then
			 Response.Redirect("Login.aspx")
		End If

These lines do several things. The first line sets the page to not be cacheable. Meaning it will not be stored in the cache of the users computer. Why? Well then the user could still reach the page and appear to be logged in, but would actually be, and would not be able to access any of the functionality the page may have. So lets just avoid this by not making it cacheable.

The next few lines do the testing to see if the user is logged in or not. If not, then the user is directed to the Login.aspx page.

Few things to note:
1. All objects inherit the method Equals() for comparing two objects.
2. All objects inherit the ToString method
These methods are inherited from the Object Parent Class.


So my Page_Load event would look something like this:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
			 'Put user code to initialize the page here
			 '   <summary>
			 ' ||||| Authenicate user for accces to pages within application
			 ' ||||| Enusre the page can't be navigated to without
			 '   |||||   user's being online and logged in.
			 ' ||||| **Note: Logged_IN session object is created in Session_Start 
			 '   |||||   of the Global.asax file **
			 ' ||||| Rem: All Objects inherit Equals() // compare two objects // ToString()
			 '   |||||   methods from the Object Parent Class
			 '   </summary>
			 '   |Do not allow caching of page
			 Response.Cache.SetCacheability(HttpCacheability.NoCache)
			 If Session("Logged_IN").Equals("No") Then
				 Response.Redirect("Login.aspx")
			 End If
	End Sub

Now there is only one thing left to do. You have to add a line of code to your Login.aspx to set the Session variable to "Yes" when the user has successfully logged in.

So this modification is made to the cmdSubmit_Click subroutine where the condition statement If DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()) Then exists. So my updated subroutine would look like this:

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
		 If Page.IsValid Then    '   ||||| Meaning the Control Validation was successful!
			 ' ||||| Connect to Database for User Validation |||||
			 If DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()) Then
				 Session("Logged_IN") = "Yes"    '   |||||   Use to Validate on other pages in the application
				 FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False)  '   |||||   default.aspx Page!
			 Else
				 '   |||||   Credentials are Invalid
				 lblMessage.Text = "Invalid Login!"
			 End If
		 End If
	End Sub

** Please Note it is very important that you place the line of code Session("Logged_IN") = "Yes" right before the RedirectFromLoginPage method call, because your session variable must be set before you redirect the user...or else they will never register as being logged in or your default.aspx or other pages.

Hope this helps everyone.

Happy Coding!

:cool

Paladine: Thanks so much for your generous help. It works great ;)

Now, we can login and view the default page. What about LogOut? Have you tried that Paladine? it would be great if you can give us a hint ....thanks again.

Hey no problem. Glad I could help!

So, for logout what do you want that is causing you issues?

I would just have a button or hyperlink that the user would click to logout, and in the event of On_Click set the Session variable to "No" and redirect the user to the login page again.

Hope this helps.

Paladine: Thanks so much for your generous help. It works great ;)

Now, we can login and view the default page. What about LogOut? Have you tried that Paladine? it would be great if you can give us a hint ....thanks again.

Ok guys I have been working on this for hours and I still cant figure out what the problem is. So here is my setup

web.config

<appSettings>
		<add key="strConn" value="Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\tke\bin\Database.mdb;User ID=Admin;Password=;" />
	</appSettings>
<authentication mode="Forms">
			<forms name="DBLogin" loginUrl="login.aspx" /> 
		</authentication>

Each user has a unique "Scroll #" so my access procedure looks like this. If I run this procedure within access it does return the appropiate value.

SELECT scroll_numb
FROM tbl_alumni_login
WHERE (((username)=[@UserName]) AND ((Password)=[@Password]));

and the table "tbl_alumni_login" has 3 columns 1.scroll_numb 2. username 3. password


login.aspx Database connection "Pretty much just like you have yours"

Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean

Dim MyConn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
Dim MyCmd As New OleDbCommand("sp_alumni_login", MyConn)
        MyCmd.CommandType = CommandType.StoredProcedure

Dim objParam1, objParam2 As OleDbParameter

objParam1 = MyCmd.Parameters.Add("@UserName", OleDbType.Char)
objParam2 = MyCmd.Parameters.Add("@Password", OleDbType.Char)

objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
        
objParam1.Value = txtUserName.Text
objParam2.Value = txtPassword.Text


Try
            If MyConn.State = ConnectionState.Closed Then
                   MyConn.Open()
            End If

            Dim objReader As OleDbDataReader
            objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)

            While objReader.Read()

                If CStr(objReader.GetValue(0)) <> "1" Then
                    lblMessage.Text = "Invalid Login!"
                Else
                    objReader.Close()  
                    Return True
                End If
            End While
        Catch ex As Exception
            lblMessage.Text = "Error Connecting to Database!"
        End Try


    End Function

login.aspx submit button "Same as yours as well"

If Page.IsValid Then   

            Dim intMaxLoginAttempts = CInt(Session("Num_of_Tries"))

            If DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()) Then
                Session("Logged_IN") = "Yes"    
                FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False) 
            Else
                 lblMessage.Text = "Invalid Login!" 
                Session("LoginCount") = CInt(Session("LoginCount")) + 1
              
                If Session("LoginCount").Equals(intMaxLoginAttempts) Then
                    Response.Redirect("Denied.aspx")
                End If

                If CInt(Session("Num_of_Tries")) > 2 Then   
                    Response.Redirect("Denied.aspx")
                End If

            End If
        End If

default.aspx load event as well for all the other forms I have

Response.Cache.SetCacheability(HttpCacheability.NoCache)
        If Session("Logged_IN").Equals("No") Then
            Response.Redirect("login.aspx")
        End If

global.asax session start "Same as yours again"

Session("Num_of_Tries") = 3
        Session("LoginCount") = 0
        Session("Logged_IN") = "No"

So I think my problem is within the parameter Session("Logged_IN") = "Yes" passed into global.asax. Because everytime I have the code for the default.aspx load event in there it just automatically redirects me straight back to the login page with no "Invalid input" message. However if I enter a completely wrong username and password and get redirected to denied.aspx then go back and put in the correct username and pass I get redirected to the default page, even if the logged in check is in the page load.

If I do get directed to the default page and then start navigating within my application between different pages it will kick me back out to the login page.

I dont know what I have wrong but if you all could help me out it would be greatly appreciated.

Miller

OK, I think I may have the problem figured out. My question is this: What value could scroll_numb be? Would it ever be 0, 2, 3, 4, 5, etc and not 1?? From the code you have provided that it seems the scroll_numb is like my ID column, which will never just be 1!

So you seem to be returning the value of scroll_numb and then in the code

If CStr(objReader.GetValue(0)) <> "1" Then
				    lblMessage.Text = "Invalid Login!"
				Else
...

You are comparing the value in scroll_numb to see if it is NOT "1", which i probably may never be. So the DBConnection returns FALSE and Invalid Login.


Hope that makes sense, and it seems to be the issue you are having.

I think I get what you are saying so this the way I have it now would only work if the scroll number was 1 and would work if the number was like 200?

If CStr(objReader.GetValue(0)) <> "1" Then
                    lblMessage.Text = "Invalid Login!"

I will be using 1 but not anything less than 1. I will be using like 1 - 700. So should this do the trick?

If CStr(objReader.GetValue(0)) < "1" Then
                    lblMessage.Text = "Invalid Login!"

I may be interpreting the code wrong but I dont know I have tried this < "1" and it still doesnt seem to work as I am still getting redirected back to the login page.

I appreciate the help I have been pulling my hair out over this 1. I do like the idea of using access procedures tho, I had no idea u could do that but it does seem easier.

Miller

Ok I setup breakpoints throught my app in the login DB function and within the page load of the default page. It makes it all the way through the login page and into the default page load where then it checks

If Session("Logged_IN").Equals("No") Then
            Response.Redirect("login.aspx")

and it doesnt make it to the

Else
            Session("Logged_IN") = "Yes"

So there for as far as my understanding its not passing that parameter Session("Logged_IN") = "Yes" from the login page to the Global.asax Session start.

Is that right or am I totally off? Also how do I check the value of Session("Logged_IN")?

edit:
Ok I setup a label on the login page and on the default page to display the session("Logged_IN") value. k when the login page loads it says "No" ; after I login I have it display again after setting session("Logged_IN") = "Yes" and the lable does indeed display "Yes". So now its in the processes of redirecting me to default.aspx and I have it display a label again to see what the value is and it is "No". So that value in Session Start is not staying at "Yes" during the transfer from login.aspx to default.aspx. And I am trying to find out why but am unsuccessful.

Miller

Well there maybe an issue with it being a string. So change the line to be

CInt(objReader.GetValue(0)) < 1

And also you would not have an Else part to the if statement in the default.aspx page. Not that it is the issue.

And what does your session_start and session_end look like in the global.asax file?

If the problem isn't there, I am at loss. You could send me a PM with the code sections in it and I could review them. Something is reseting that Session variable for some reason. I am guessing in the Session_Start or End? I can't seem to see where the problem is according to your code. And you have copied my code in line by line to very it works....?

Oh and just to clarify:

So there for as far as my understanding its not passing that parameter Session("Logged_IN") = "Yes" from the login page to the Global.asax Session start.

No value is passed to or from the Global.asax Session_Start. That subroutine only creates the variable and populates it on start of a session. But I understand what you are referring to.

Nice work on the the breakpoints and finding out what value in the Session variable is at different points. Well done.

But saying that.....I am still at a loss here. I will take a clearer look at it when I get home!

The thing is tho is that its getting past the

If Cstr(objReader.GetValue(0)) < "1" Then
Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
        ' Fires when the session is started
        '<summary>
        '   |Fires when the session is started
        '   |Administrator will only be allowed a certain number of login attempts
        '</summary>
        Session("Num_of_Tries") = 3
        Session("LoginCount") = 0

        '   |Track whether they're logged in or not
        Session("Logged_IN") = "No"
    End Sub

And I have nothing in Session End. I have to go meet with my client right now but will be back shortly to try and work this out and let you see my code blocks. I appreciate your help!


edit: Yes it is passing the value "Yes" into Global.asax until it redirects to default.aspx and then it resets it to "No"
Miller

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.