Inorder for the Store Procedure (a prebuilt function that you call to execute a series of SQL statements. This function can accept a number of parameters, and return a value based on those parameters it is given) to work, in the example I have provided, the number of parameters built in the stored procedure must be the equal number you pass in.
Hope this helps
I'm having the same problem can you explain further I am a newbie
Actually the problem you are having is very simple. If you read the error message it actually tells you what the problem is.
System.Data.OleDb.OleDbException: Too few parameters. Expected 4
It is expecting 4 parameters, and you are supplying only 2!!
Ok, I know what you are going to say, but that is all my Store Procedure is built with. The answer to that is NO! Run you stored procedure, and see what happens. It will request 4 parameters, because the table.column name method you used has too many brackets, which confuses access. Your Store Procedure should be like this (and I have used your code with this and it works).
SELECT COUNT(*) AS Num_of_User
FROM myUsers
WHERE myUsers.U_Name = @UserName AND myUsers.U_Password = @password;Hope this helps you out.
But just to save you some frustration in the future, be sure to test your code as you go. Build your stored procedures, and test them. Build one function (onSubmit_Click for example) and test it. Then build DBConnection, and test it.
Good luck, and if you have any more problems, feel free to ask.
I created a second lable box and sent ex to string and this was the result.
I am also building the SQL version to see how that goes.
Thanks for the help, am learning lots on this one...
System.Data.OleDb.OleDbException: Too few parameters. Expected 4. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at WebListSite.WebForm1.DBConnection(String strUserName, String strPassword) in C:\Documents and Settings\Jason Apel\My Documents\My Webs\WebListSite\AdminLogin.aspx.vb:line 89
Thanks for your time it works great...
Will now look further at my SQL version and see if I have done a similar thing.
Glad you go it working.
The SQL version would be the exact same code format, and layout, with two exceptions: 1. Store Procedures is written differently in SQL and 2. Change the OleDB to SqlClient for imports and modify the subsequent code for the use of this import and voila.
Should take 10 mins to modify and run on SQL.
Have been working with the code and having lots of luck...
But one question, stopping access to pages directly and re-directing them to the login page is fine for me in the .aspx world but I have 2 pages that are htm. Can you please give me some pointers on how this is done...
Attached is the html page i want to stop access to unless they go through the login page...
This page was created out of access 2003 to update a table as I don't have the skills yet to write it in asp. Yes I know it is the easy way out but the goal out-ways the journy in this caes.
;)
Hi
I am new member in your site i used your codes to fit in my project i did the query in sql and connect that to my web but it doesn't work can any one help me in that
this is my codes
Private Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
SqlConnection1.Open()
Dim user As String = nameF.Text
Dim pass As String = Password1.Text
Dim oCM2 As SqlClient.SqlCommand
oCM2 = New SqlClient.SqlCommand("login", SqlConnection1)
oCM2.CommandType = CommandType.StoredProcedure
Dim StID As SqlClient.SqlParameter
StID = New SqlClient.SqlParameter("@SID", SqlDbType.Int, 4)
StID.Value = user
StID.Direction = ParameterDirection.Input
oCM2.Parameters.Add(StID)
Dim Spassword As SqlClient.SqlParameter
Spassword = New SqlClient.SqlParameter("@Password", SqlDbType.NVarChar, 50)
Spassword.Value = pass
Spassword.Direction = ParameterDirection.Input
oCM2.Parameters.Add(StID)
Dim objReader As SqlDataReader
objReader = oCM2.ExecuteReader(CommandBehavior.CloseConnection)
While objReader.Read()
If CStr(objReader.GetValue(0)) <> "1" Then
Response.Redirect("start.aspx")
lblMessage.Text = "Invalid Login!"
Else
Response.Redirect("test.aspx")
objReader.Close()
End If
End While
End Sub
End Classthis my query
ALTER PROCEDURE dbo.login
(@SID int ,
@Password nvarchar (50)
)
AS
select count(*) as Num_Use
from Sinfo
where SID=@SID and Password=@Password
RETURNthanks for help and give this chance
Nice Clean Code! Good job.
Here is the reason it doesn't work: You have repeated this line twice; oCM2.Parameters.Add(StID)
The second time it should have been oCM2.Parameters.Add(Spassword)
Hope that helps
Hi
I am new member in your site i used your codes to fit in my project i did the query in sql and connect that to my web but it doesn't work can any one help me in that
this is my codesPrivate Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) SqlConnection1.Open() Dim user As String = nameF.Text Dim pass As String = Password1.Text Dim oCM2 As SqlClient.SqlCommand oCM2 = New SqlClient.SqlCommand("login", SqlConnection1) oCM2.CommandType = CommandType.StoredProcedure Dim StID As SqlClient.SqlParameter StID = New SqlClient.SqlParameter("@SID", SqlDbType.Int, 4) StID.Value = user StID.Direction = ParameterDirection.Input oCM2.Parameters.Add(StID) Dim Spassword As SqlClient.SqlParameter Spassword = New SqlClient.SqlParameter("@Password", SqlDbType.NVarChar, 50) Spassword.Value = pass Spassword.Direction = ParameterDirection.Input oCM2.Parameters.Add(StID) Dim objReader As SqlDataReader objReader = oCM2.ExecuteReader(CommandBehavior.CloseConnection) While objReader.Read() If CStr(objReader.GetValue(0)) <> "1" Then Response.Redirect("start.aspx") lblMessage.Text = "Invalid Login!" Else Response.Redirect("test.aspx") objReader.Close() End If End While End Sub End Classthis my query
ALTER PROCEDURE dbo.login (@SID int , @Password nvarchar (50) ) AS select count(*) as Num_Use from Sinfo where SID=@SID and Password=@Password RETURNthanks for help and give this chance
Hi thanks a lot for quick help
I did the change but the same problem. It give me the same even the password is wrong
Hello, I too am new to ASP.Net (bet you here that alot! :lol: ) Anyhow, I've gone through the tutorial (Login and registration) using visual studio.net 2003 (academic version). I was able to get the Login to work, but unable to get the registration piece to work. The error that I am getting when attempting to register a user is "Error Connecting to Database". I'm not sure where to look to correct this error, I've debugged, changed the params in the web.config with no success. I've attached a copy of the code. Any help that you can provide is greatly appreciated.
Ok I will look at it when I get home.
i am totally new to vb.net and was attempting to make you login page work. I followed the directions and it builds wiht no errors. However, every time i try to sign in it says invalid password no matter what i put in. I filled the data with some user names and passwords but none seem to work. Any help would be greatly appreciated.
I don't really understand how to export the project yet so i took the folder from wwwroot and the one from document and settings. Do you need the database file too? Let me know i can send it. It works and is the exact one you had in your example.
I would love to help!
All you need to provide me with is the methods you took so far to determine why it is not working. Did you go line by line with breakpoints? Do you know what breakpoints are and how to use them?
I need a know the DB structure.
How did you build the Stored Procedure? Have you tried running the stored procedure in Access to make sure it works and returns what you expect it should?
Saying that, as long as the stored procedure works, and you copied my code "exactly" and changed on the line in the web.config that points to the DB (and did so syntax correct) you should have no errors.
As for supplying the application. I can look at it, but you need to provide me the entire application directory as is. i.e. vbproj file and directories intact.
The zip file you provided has two different directories in it, and the project file doesn't load properly.
Hope that helps.
i am totally new to vb.net and was attempting to make you login page work. I followed the directions and it builds wiht no errors. However, every time i try to sign in it says invalid password no matter what i put in. I filled the data with some user names and passwords but none seem to work. Any help would be greatly appreciated.
I don't really understand how to export the project yet so i took the folder from wwwroot and the one from document and settings. Do you need the database file too? Let me know i can send it. It works and is the exact one you had in your example.
Attached is the database file and the project. The password for the database is 1212. Yeah i figured out what break points are last night. i go through and see if i can fix it. BUt any help is greatly appreciated...thanks
dominic
I will have to look at this when I get home. Nothing stands out at the moment.
Hehe... ok, you had me stumped for 5 minutes, but I know what the problem is now. You code is fine, but not your implementation of part of it. No where in this tutorial did I discuss what to do in the case of password protecting the DB holding the information.
But now that you have presented the problem, this is a good learning opportunity for everyone interested.
You have to modify the Web.Config connection string to be this:
<add key="strConn" value="Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb;Jet OLEDB:Database Password=1212;"/>This portion "Jet OLEDB: Database Password=******;" is required, and not the login UserID and Password. Ok, why would it not work with User ID as Admin and 1212 as the password. Because this method assumes sharing of the DB, where setting the password on the DB makes the AccessDB file "Exclusive" to the owner (i.e. Admin in this case). Meaning, when you try to connect to the db it will request only the Password, not the UserID, because it is set to excusively the owner.
Make sense?
Hope so, if not, let me know.
Sorry I took so long getting back to you, but work is crazy, and I am getting pushed into taking over for my boss that quit (and I am a 6 month Noob!).
Hi!
i just finished creating SQL login page as was shown on page1.
I don't get any errors but i simply cannot login. Invalid User!
I checked the new table - Admin user is there. I use a remote SQL server and tried app settings with IP and without. What could be a problem? integrated security = true or false?
Hmmm, alright lets try to find out what the problem is!
1. Have you tested the Stored Procedure in SQL to be sure it returns what you expect when you use the login credentials you have tried on the Login Page?
2. Have put in break points in the application and followed through logic to verify the values that are populating the variables? (Assumes you have Visual Studio)
3. Have you commented out the error messages in the code behind to get he asp.net error messages to display...sometimes codebehind errors messages provide too little information to a larger problem ????
the code and Stored Procedure were fine. It was my sql connection. I used <add key="strConn" value="Data Source=DENVER; database=pubs;User id=myID;Password=;Integrated Security=true; "/> It works now, at least from Visual studio testing! even it's a romote server.
Thanks Paladine!!!!
Hey no problem. Glad you go it worked out.
Happy coding!
Hi, when login successfully, how do I get the data from SQL Server based on the user's identity? (eg. User.Identity.Name or username) I need help in getting the user's data from SQL Server and display it on the webpage. What procedures to include?
Hi I am in very harry can you attach your code when you use the sql in login page and can you attach your store producer
in harry please i have a final project
Awaw re-read the tutorial everything you asked for is in the tutorial - Page 1 infact: Here is the Stored Procedure and my code
**note** This is ASP.Net 1.0 & 1.1 compatible code.
Hi I am in very harry can you attach your code when you use the sql in login page and can you attach your store producer
in harry please i have a final project
I modified the code in c# to login with Oracle DB. But I cannot open connection when I pass in user name and password through Stored procedure. The connection open fails. I don't know enough about it to fix it. Please help.
The oracle stored procedure works fine. Here is Stored procedure:
create or replace procedure sp_ValidateUser(
uname IN varchar,
pword IN varchar,
Num_of_User IN OUT integer
)
as
pass varchar(20);
BEGIN
select pwd into pass from testrpt where username=uname;
if (pass = pword) then
Num_of_User:=1;
else
Num_of_User:=0;
end if;
END;Here is the connection string specified in DB Connection .xml
<?xml version="1.0" encoding="utf-8" ?>
<appSettings>
<add key="oraClientConnStr" value="Data Source=prod_wh1;User Id=;Password=;" />
</appSettings>
Here is the C# code:
private void cmdSubmit_Click(object sender, System.EventArgs e)
{
if (Page.IsValid)
{
// pass in username and password provided by user
if (DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim()))
{
FormsAuthentication.RedirectFromLoginPage (txtUserName.Text, false);
}
else
{
lblMessage.Text = "Invalid Login, please try again!";
}
}
}
private bool DBConnection(string txtUser, string txtPass)
{
myConn = new OracleConnection(ConfigurationSettings.AppSettings["oraClientConnStr"]);
OracleCommand myCmd = new OracleCommand("sp_ValidateUser", myConn);
myCmd.CommandType = CommandType.StoredProcedure;
OracleParameter objParam1 = myCmd.Parameters.Add("uname", OracleType.VarChar);
OracleParameter objParam2 = myCmd.Parameters.Add ("pword", OracleType.VarChar);
OracleParameter returnParam = myCmd.Parameters.Add ("Num_of_User", OracleType.Int32);
objParam1.Direction = ParameterDirection.Input;
objParam2.Direction = ParameterDirection.Input;
returnParam.Direction = ParameterDirection.InputOutput;
objParam1.Value = txtUser;
objParam2.Value = txtPass;
try
{
if (myConn.State.Equals(ConnectionState.Closed))
{
myConn.Open();
myCmd.ExecuteNonQuery();
}
else if (myConn.State.Equals(ConnectionState.Open))
{
myCmd.ExecuteNonQuery();
}
if ((int)returnParam.Value < 1)
{
lblMessage.Text = "Invalid Login!";
return false;
}
else
{
lblMessage.Text = "Successful Login!";
myConn.Close();
//Response.Redirect(ResolveUrl("~/Report.aspx"));
Response.Redirect("Report.aspx",false);
return true;
}
}
catch (Exception ex)
{
lblMessage2.Text = ex + "Error Connecting to the database";
return false;
}
}So you have tested the Stored Procedure and it produces the results you expect in Oracle?
What is the exact error message you are getting?
I modified the code in c# to login with Oracle DB. But I cannot open connection when I pass in user name and password through Stored procedure. The connection open fails. I don't know enough about it to fix it. Please help.
The oracle stored procedure works fine. Here is Stored procedure:
create or replace procedure sp_ValidateUser( uname IN varchar, pword IN varchar, Num_of_User IN OUT integer ) as pass varchar(20); BEGIN select pwd into pass from testrpt where username=uname; if (pass = pword) then Num_of_User:=1; else Num_of_User:=0; end if; END;Here is the connection string specified in DB Connection .xml
<?xml version="1.0" encoding="utf-8" ?> <appSettings> <add key="oraClientConnStr" value="Data Source=prod_wh1;User Id=;Password=;" /> </appSettings> Here is the C# code: private void cmdSubmit_Click(object sender, System.EventArgs e) { if (Page.IsValid) { // pass in username and password provided by user if (DBConnection(txtUserName.Text.Trim(), txtPassword.Text.Trim())) { FormsAuthentication.RedirectFromLoginPage (txtUserName.Text, false); } else { lblMessage.Text = "Invalid Login, please try again!"; } } } private bool DBConnection(string txtUser, string txtPass) { myConn = new OracleConnection(ConfigurationSettings.AppSettings["oraClientConnStr"]); OracleCommand myCmd = new OracleCommand("sp_ValidateUser", myConn); myCmd.CommandType = CommandType.StoredProcedure; OracleParameter objParam1 = myCmd.Parameters.Add("uname", OracleType.VarChar); OracleParameter objParam2 = myCmd.Parameters.Add ("pword", OracleType.VarChar); OracleParameter returnParam = myCmd.Parameters.Add ("Num_of_User", OracleType.Int32); objParam1.Direction = ParameterDirection.Input; objParam2.Direction = ParameterDirection.Input; returnParam.Direction = ParameterDirection.InputOutput; objParam1.Value = txtUser; objParam2.Value = txtPass; try { if (myConn.State.Equals(ConnectionState.Closed)) { myConn.Open(); myCmd.ExecuteNonQuery(); } else if (myConn.State.Equals(ConnectionState.Open)) { myCmd.ExecuteNonQuery(); } if ((int)returnParam.Value < 1) { lblMessage.Text = "Invalid Login!"; return false; } else { lblMessage.Text = "Successful Login!"; myConn.Close(); //Response.Redirect(ResolveUrl("~/Report.aspx")); Response.Redirect("Report.aspx",false); return true; } } catch (Exception ex) { lblMessage2.Text = ex + "Error Connecting to the database"; return false; } }
Using Session Variables.
Global.asax Code:
...
Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
'<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
...Use of these variables in Login.aspx code behind (or script):
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 |||||
Dim intMaxLoginAttempts = CInt(Session("Num_of_Tries")) ' ||| Set in Global.asax
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!"
' ||||| 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 SubSo you have tested the Stored Procedure and it produces the results you expect in Oracle?
What is the exact error message you are getting?
Here is the error message:
System.Data.OracleClient.OracleException: ORA-01004: default username feature not supported; logon denied at System.Data.OracleClient.DBObjectPool.GetObject(Boolean& isInTransaction) at System.Data.OracleClient.OracleConnectionPoolManager.GetPooledConnection(String encryptedConnectionString, OracleConnectionString options, Boolean& isInTransaction) at System.Data.OracleClient.OracleConnection.OpenInternal(OracleConnectionString parsedConnectionString, Object transact) at System.Data.OracleClient.OracleConnection.Open() at ReportTool.LoginReportTool.DBConnection(String txtUser, String txtPass) in c:\inetpub\wwwroot\reporttool\login.aspx.cs:line 108Error Connecting to the database
Please let me know what is wrong?
Paladine....
First of all thanks for the great post, It was easy to follow and worked the first time, even with the tweaks I made. Anyway I have just a general question about security and one small issue I have with the
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False) ' ||||| default.aspx Page!.
In my site I have just one folder that I currently want to be secure. The site is laid out something like this.
Site Root (Allow anonymous)
Links (Allow anonymous)
About Us (Allow anonymous)
Proofs (secure)
etc....
My issue is, is that I want to have my login page in the root directory, just in case in the future I would want other folders to be secure. Within my proofs.aspx page in the Proofs folder I have put to code to redirect to the Login.aspx page if Session("Logged_IN").Equals("No"). The problem is once the user is validated, instead of returning back to the proofs.aspx page in the proofs folder, I go to the default page in the root folder. Any ideas why? I've set up the config file as like this. I have authorization set to (Allow anonymous)
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="strConn" value="Data Source=(local);database=MNP;User id=?;Password=?;"/>
</appSettings>
<system.web>
<!-- DYNAMIC DEBUG COMPILATION
Set compilation debug="true" to insert debugging symbols (.pdb information)
into the compiled page. Because this creates a larger file that executes
more slowly, you should set this value to true only when debugging and to
false at all other times. For more information, refer to the documentation about
debugging ASP.NET files.
-->
<compilation defaultLanguage="vb" debug="true" />
<!-- CUSTOM ERROR MESSAGES
Set customErrors mode="On" or "RemoteOnly" to enable custom error messages, "Off" to disable.
Add <error> tags for each of the errors you want to handle.
"On" Always display custom (friendly) messages.
"Off" Always display detailed ASP.NET error information.
"RemoteOnly" Display custom (friendly) messages only to users not running
on the local Web server. This setting is recommended for security purposes, so
that you do not display application detail information to remote clients.
-->
<customErrors mode="RemoteOnly" />
<!-- AUTHENTICATION
This section sets the authentication policies of the application. Possible modes are "Windows",
"Forms", "Passport" and "None"
"None" No authentication is performed.
"Windows" IIS performs authentication (Basic, Digest, or Integrated Windows) according to
its settings for the application. Anonymous access must be disabled in IIS.
"Forms" You provide a custom form (Web page) for users to enter their credentials, and then
you authenticate them in your application. A user credential token is stored in a cookie.
"Passport" Authentication is performed via a centralized authentication service provided
by Microsoft that offers a single logon and core profile services for member sites.
-->
<authentication mode="Forms">
<forms name="MNPLogin" loginUrl="login.aspx" protection="All" path="/" timeout="30" />
</authentication>
<!-- AUTHORIZATION
This section sets the authorization policies of the application. You can allow or deny access
to application resources by user or role. Wildcards: "*" mean everyone, "?" means anonymous
(unauthenticated) users.
-->
<authorization>
<!--<deny users ="?" />-->
<allow users="*" /> <!-- Allow all users -->
<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>
<!-- APPLICATION-LEVEL TRACE LOGGING
Application-level tracing enables trace log output for every page within an application.
Set trace enabled="true" to enable application trace logging. If pageOutput="true", the
trace information will be displayed at the bottom of each page. Otherwise, you can view the
application trace log by browsing the "trace.axd" page from your web application
root.
-->
<trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true" />
<!-- SESSION STATE SETTINGS
By default ASP.NET uses cookies to identify which requests belong to a particular session.
If cookies are not available, a session can be tracked by adding a session identifier to the URL.
To disable cookies, set sessionState cookieless="true".
-->
<sessionState
mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes"
cookieless="false"
timeout="20"
/>
<!-- GLOBALIZATION
This section sets the globalization settings of the application.
-->
<globalization requestEncoding="utf-8" responseEncoding="utf-8" />
</system.web>
</configuration>One other thing I noticed is that when I go to the login page from the page I want secured there is no return url in the query string. I know that this is most likely the issue, but am not sure how to solve it.
Thanks in advance.
KEG....
I have the similar problem:- After successful login through my login.aspx it always takes me to defaut.aspx. My redirect from Login.aspx to other pages fails - only redirects to defaut.aspx.
I just found the answer to this and was getting ready to post the solution. You basically have to put an addition web config file in the folder you want to secure. The solution can be found at http://www.4guysfromrolla.com/webtech/110701-1.2.shtml . Also I had to remove the
' |Do not allow caching of page
Response.Cache.SetCacheability(HttpCacheability.NoCache)
If Session("Logged_IN").Equals("No") Then
Response.Redirect("Login.aspx")
End IfAll works fine now.
Kegger,
Thanks for the suggestions. It works for me now
Vishwa
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.