Sorry, let me give you a clearer picture first. I'm using Access Database and I named my Database as User.mdb. My table is called tblUser. Inside the table got U_id, U_Name and U_Password. I haven't create any Query because the SQL statement you wrote on the first page will always prompt me the value of the parameter. So in the end I deleted it. Is it purpose to be this way?
I also create a Login Page named Login.aspx. My form have:
- txtUsername
- txtPassword
- cmdSubmit
- lblMessage
Thus, I would like to learn from you what should I put for my query and how can I connect to the Access Database so that my login page is workable? I really appreciate your help. =) Thank you in advance!
Have something to say? Contribute New Article Reply to this Article
Sorry, I'm kinda noob in this... I do not know how to find out my version of ASP.NET. I insert Imports.System.Data.SqlClient on top, although the KeyName is no longer showing error but when I used breakpoint, this statement show error >>> Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Is the information enough?
Ok be sure you have all of the following imports:
When keywords are not recognized, that is the first thing to check.
**Copy and Paste doesn't always solve this** :) 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 |||||
And be sure you have this line in your Web.Config file. <appSettings>
<add key="strConn" value="Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=;Password=;"/>
</appSettings>
Let me know how it goes..:cool:
Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
Sorry, let me give you a clearer picture first. I'm using Access Database and I named my Database as User.mdb. My table is called tblUser. Inside the table got U_id, U_Name and U_Password. I haven't create any Query because the SQL statement you wrote on the first page will always prompt me the value of the parameter. So in the end I deleted it. Is it purpose to be this way? I also create a Login Page named Login.aspx. My form have: - txtUsername - txtPassword - cmdSubmit - lblMessage Thus, I would like to learn from you what should I put for my query and how can I connect to the Access Database so that my login page is workable? I really appreciate your help. =) Thank you in advance!
Ok, I can definitely help.
First off I highly recommend youREAD through the tutorial again. I am being serious, and not in a bad way.
Why? Your statement:
I haven't create any Query because the SQL statement you wrote on the first page will always prompt me the value of the parameter. So in the end I deleted it. Is it purpose to be this way?
This demonstrates that you may not understanding what the purpose each line of code I have provided is doing. So let me see if I can better clarify what is happening.
To begin this SQL statement used in Access is correct:
SELECT COUNT(*) AS Num_of_User
FROM tblUser
WHERE (((tblUser.U_Name)=[@UserName]) AND ((tblUser.U_Password)=[@Password]));
The query is suppose to prompt you; it will in Access as the @UserName & @Password are parameters the query is expecting to do the processing against the database on. What you are doing wanting to do is pass in the values entered into the ASP.NET control (i.e. username & password textboxes) into these paramters. Otherwise how would you provide the query with the values entered? And how else would you qualify the query? Meaning; how would you find out if the password entered is correct for the username entered?
Here is another important point. Your initial error was with the SQLConnection object not being recognized, but you are now saying you are using an Access Database.
Which are you using? An SQL database or Access?
If it is truly Access you are using then the imports you need are those directed at the start of this tutorial:
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 |||||
Link to the start of this tutorial - using an Access Database
Hope this helps.:cool:
Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
I'm Sorry... The login page is still not working. I do not know what went wrong. I'm using Access DB.
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 |||||
I've changed the above code from Imports.System.Data.SqlClient to Imports.System.Data.OleDBConnection. Because I'm not using SQL Server. Am I right to do so? I've also create a new databse which I had followed the codes on the first page. So could you paste the rest of the codes which I need for me? The first page is kinda confusing for me. I'm really new at this. This is the first time I'm doing this. Please...
Greetings all. I am very green at .net, learning as I go at this point. Is there a way to "adapt" the code here to be usable on the 2.0 Framework? I am building a basic webform for a project at work that pulls a dropdown list from an SQL DB, and I have an editing page to manage it, but of course it needs to be protected so anyone can't just go in and make changes. I build the entire login script based on the notes here, with the usual couple tweaks, but get the error "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" when I run in debug mode after entering the name/password. The form always returns the "invalid login!" message regardless as well. I am writing in vb.net 2k5 at this time, but haven't learned all the features as of yet either. -- Thanks for any assistance anyone can provide! --
Greetings all. I am very green at .net, learning as I go at this point. Is there a way to "adapt" the code here to be usable on the 2.0 Framework? I am building a basic webform for a project at work that pulls a dropdown list from an SQL DB, and I have an editing page to manage it, but of course it needs to be protected so anyone can't just go in and make changes. I build the entire login script based on the notes here, with the usual couple tweaks, but get the error "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" when I run in debug mode after entering the name/password. The form always returns the "invalid login!" message regardless as well. I am writing in vb.net 2k5 at this time, but haven't learned all the features as of yet either. -- Thanks for any assistance anyone can provide! --
HiE_Hobbie_Coder,
You say you are using VB.Net 2K5? Well this code only works with ASP.NET 1.0 or 1.1. It will not function (do to several Framework changes) in ASP.NET 2.0, or VB.NET 2k5 (which would require major code changes to from this tutorial).
Provide the code you are having issues with and I can see what I can do to help.
Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
Hi,
I'm new to ASP.net and SQl server,
I'm using VS 2003 and SQL 2005 express edition,
I followed thru out ur tutorial , but still ve problem with building my first login page..
After I click the submit button..lblmessage2.= become visible which is
"Error Connecting to Database!"
I tried playing around with my connection string but its still the same..
my current connection string
<appSettings>
<addkey="strConn"value="Data Source=(local);database=fypDB;User id=;Password=;"/>
</appSettings>
I'm using window authentication...tried several connection string...but i still cannot connect to the db..other then the connection string..others codes are the same as ur tutorial..
try
<add name="strConn" connectionString="Data Source=P4C800E\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=True"
providerName="System.Data.SqlClient" /> Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
try
<add name="strConn" connectionString="Data Source=P4C800E\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=True" providerName="System.Data.SqlClient" />
I tried the above but error message came out bout missing key, so i replace name with key,..and then another error bout value came out so i replace it,...and now its this error..all this are after the on submit
<strong>Parser Error Message: </strong>Unrecognized attribute 'providerName'
<strong>Source Error:</strong>
Line 2: <configuration>
Line 3: <appSettings>
Line 4: <add key="strConn" value="Data Source=SHINGREY2\SQLEXPRESS;database=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
Line 5: </appSettings>Line 6: <system.web>So your datasource is SQLEXPRESS and not some other name?
Also, you have left left the Database as Northwind, and your previous post showed that your database is fypDB So make sure you taylor the string for you specific instance.
Also, the providerName is not required, that was out of ASP.NET 2.0. My apologies, bu the error message tells you what is wrong, so remove the portion that is unrecognized.
The error will occur on the Submit, because that is when the application makes a call to the Database.
Should be: (at least try this)
Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
Acutally here is a major better explaination.
When using SQL Express you need to also consider the new security. I recommend that you download and install the Microsoft SQL Server Management Studio Express CTP which will give you a GUI control app for SQL Express. Then go to secuirty --> Logins folder in the Object Explorer in this app and set the following up for you ASP.NET login (not the login for the asp.net app you are building).
Set the login up like have shown here for my SQL server and Northwind database:
[IMG]http://www3.telus.net/public/tmlohnes/Images/SQLSecurity.jpg[/IMG]
Once that is setup. (note: not all steps are shown in the above image).
Then the SQL Connection string (using my example) would look like this:
"Server=P4C800E;Database=Northwind;Trusted_Connection=True"Hope this helps
Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
Finally I got it to work...
The problem is I'm not familiar with Sql server,
never used it, prior to this tutorial,
anyway, I got it to work, with the Sql Management Express,
I set up the ASP.Net Login..followed ur steps..tried and tried..
but it only works when I played with the db_owner checkbox on your shown page and also the security page for users Under my northwind DB.....
Many Thanks for your time...now I can move on to session
Very good guide! The only problem I have is that I'm using MYSQL, and I can not use store procedures with it. So my question is:
How can I make the Function DBConnection to work without store procedures? Is this possible?
Thanks!
Sure, use the SQL statements in the Store Procedures in the code_behind, just like you would any other SQL statement.
Dim sqlstring as String = "SELECT Count(*) FROM tblUsers WHERE...."
Hope this helps.
Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
Thanks for the answer, I made this query, but it keep saying "Invalid user"
[php]
Public storecons As String
Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean
storecons = _
"SELECT COUNT(*) AS Num_of_User " & _
"FROM tblUser " & _
"WHERE U_Name = @UserName AND U_Password = @Password " & _
"RETURN @Num_of_User "
Dim MyConn As MySqlConnection = New MySqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn"))
Dim MyCmd As New MySqlCommand(storecons, MyConn)
MyCmd.CommandType = Data.CommandType.Text
Dim objParam1, objParam2 As MySqlParameter
Dim objReturnParam As MySqlParameter
objParam1 = MyCmd.Parameters.Add("@UserName", MySqlDbType.VarChar)
objParam2 = MyCmd.Parameters.Add("@Password", MySqlDbType.VarChar)
objReturnParam = MyCmd.Parameters.Add("@Num_of_User", Data.SqlDbType.Int)
objParam1.Direction = Data.ParameterDirection.Input
objParam2.Direction = Data.ParameterDirection.Input
objReturnParam.Direction = Data.ParameterDirection.ReturnValue
objParam1.Value = txtUserName.Text
objParam2.Value = txtPassword.Text
Try
If MyConn.State = Data.ConnectionState.Closed Then
MyConn.Open()
MyCmd.ExecuteNonQuery()
End If
If objReturnParam.Value < 1 Then
lblMessage.Text = "Invalid Login!"
Else
Return True
End If
MyConn.Close()
Catch ex As Exception
lblMessage.Text = "Error Connecting to Database!"
End Try
End Function
[/php]
I hope you can give me a hand.
Thanks!
Hi there
You dont need this: RETURN @Num_of_User a select statement by default returns a value already.
And you don't require this:
objReturnParam = MyCmd.Parameters.Add("@Num_of_User", Data.SqlDbType.Int)
I can't stress this enough. You can not just cut and paste and hope it works Go through the logic, and understand what each line of code is doing.
Here is a bit of a hint (actually more than that):
objParam2.Value = txtPassword.Text
Dim objReader As OleDbDataReader
' ||||| 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
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 Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
Hi Paladine. I m new in dis forum and also a VB.NET beginner. Thanks alot for dat post for the connection to SQL server 2000 database, it was exactly wat i was looking for. Anyway, i hav a question here. Can't i juz match the value of password to the user ID? I mean, without going through the stored procedure and all? Is using stored procedure a more efficient way? Thanks. I used parameters for the password and it worked so far. Hope you can give some advice. Thank you.
Hi there glad I could help.
At the start of this thread, page 1, there is the steps to using a store procedure (preferred methodology) for this.
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_UserLet me know if you need something more specific.
Paladine
Master Poster
Team Colleague
824 posts since Feb 2003
Reputation Points: 211
Solved Threads: 27
uhm...I have been checking all of your tutorials Paladine...they are great, I've tried the C# example and it worked quite well...nonetheless, as I am a newbie in asp.net :( I've been tryng to do the login page using mysql and hell I've been having lots and lots of trouble... wonder if you could guide me pls hehe
btw..my problems start with the stored procedure, which I think I kinda adapted in mysql 5, but, I don't think it's working.....also may have problems with the connection... so bad...
as I said, I tried to adapt the C# with sql server example you posted before....here are fragments of the code I think are wrong:
[in the code behind]
.......
private bool DBConnection(string tUser, string tPass)
{
//I couldn't make it work in the web.configure :(
OdbcConnection myConn = new OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=database;UID=uid;PASSWORD=password;OPTION=3;");
OdbcCommand myCmd = new OdbcCommand("sp_ValidateUser", myConn);
myCmd.CommandType = CommandType.StoredProcedure;
.......
returnParam = myCmd.Parameters.Add("@Num_of_User", OdbcType.Int);
objParam1.Direction = ParameterDirection.Input;
objParam2.Direction = ParameterDirection.Input;
returnParam.Direction = ParameterDirection.Output;
......
now, this is the stored procedure I made (got a table named "users"):
CREATE PROCEDURE `sp_ValidateUser`(IN UserName Varchar(50), IN Wachtwoord Varchar(50), OUT Num_of_User INT)
BEGIN
SELECT COUNT(*)
INTO Num_of_User
FROM users
WHERE user = UserName
AND password = Wachtwoord;
END
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
and finally the error I got:
System.Data.Odbc.OdbcException: ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.24-community-nt]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sp_ValidateUser' at line 1 at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteNonQuery() at _Default.DBConnection(String tUser, String tPass)
I think I really need help hehe :P..... thanx in advance :)
doesn't matter anymore, I managed to do the page using the login control, and, using the mysql connector instead of odbc... sorry for the inconvenience... anyways, if some other newbie or not want some help just let me know... bye then
P.S. I forgot, I haven't done it with the stored procedure though, so, as soon as I am able to do it, I'll be telling you pals
This article has been dead for over three months
You
© 2012 DaniWeb® LLC
![http://www3.telus.net/public/tmlohnes/Images/SQLSecurity.jpg[/IMG]](http://www3.telus.net/public/tmlohnes/Images/SQLSecurity.jpg%5B/IMG%5D){kind=link}