|
|
Sorry ignore my previous post, I realize after reading it that its essentially useless. Let me be more specific, I have a staff table with variables username and password, and loginstatus, which I set to 1 or 0 depending on whether they're logged in or not. So in the login button procedure I want to put some code to check if the user name and password entered in the textboxes match those in my table. As I am new to this whole thing, I am unsure how to go about doing this (ie moving through from one user to the next through the table to compare with the textboxes in my form).. any help would be appreciated. Thanks, and sorry if this is a real newbie question.
|
|
|
|
Well, I replied to your previous post before reading this one telling me to ignore it... oh well. You should read the reply anyway, because it introduces the authentication object.
Again, I don't know what level of detail you are seeking.
Why would you query the entire table, and then loop through the records to see if you have a match? Just query the table with the username and password as parameters and see if you get a result or not.
Do you know how to query a database using ADO.NET in an ASP.NET application?
Thomas D. Greer
www.tgreer.com
|
|
|
|
|
|
Thomas, thanks for your help, I'm not sure if I'll be able to use what you've suggested, because I have my username and passwords stored in a 'staff' table in my sql database. I have another page in which staff members are added to this database, so I can't really have an existing set of usernames to compare it against. Let me show you what I've done so far. I know its not a very efficient way of doing it, but I'm new to this so its the only way I could figure it out. What I did was pas a query string to the staff table, and any matches that are found are brought into a datagrid. Because I can't have duplicate records in my table, I just search the number of records in the data grid, so technically, if there is one record, its found a match and if its zero, it hasn't. Where I'm kind of stuck now is this. I have another variable in the table called loginstatus, which has a value of 0 for not logged in and 1 for logged in. Once i've found this match with the query string, how do I locate that corresponding 'loginstatus' in that row of the table to change it 1. Can you help me out with this? here's a sample of the code in my submit button.
Private Sub stfLoginBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles stfLoginBtn.Click
staffUser = stfUsertxtbox.Text
staffPass = stfPasstxtbox.Text
Finder = ("SELECT * FROM pStaffbl WHERE userName = '" & staffUser & "' and password = '" & staffPass & "'")
Dim objDataSet As New DataSet
Try
Dim objDataAdapter As New SqlDataAdapter(Finder, dbConn)
objDataAdapter.Fill(objDataSet, "pStaffbl")
Catch objError As Exception
DBprob.Text = "<b>* Error in updating the database</b>.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub
End Try
Dim objdataview As New DataView(objDataSet.Tables("pstaffbl"))
DataGrid1.DataSource = objdataview
DataGrid1.DataBind()
Dim count As New Integer
count = objdataview.Count()
If count = 0 Then
DBprob.Text = "Wrong username or password, please try again"
Else
DBprob.Text = "Congratulations, you have been logged in"
'this is where I want to enter the code which I asked you about!
'Sending parameters to the next page for confirmation
Session("username") = stfUsertxtbox.Text
Session("password") = stfPasstxtbox.Text
Response.Redirect("/peterblair/staffhome.html")
End If
End Sub
|
|
|
|
|
|
My previous post in the prior thread was meant to introduce you to the .NET Authentication class. The simplest way to use it would be with the web.config file. However, you can also use it conjunction with a database.
Once a user has been authenticated, ASP.NET handles the rest through, in most cases, cookies, or if cookies aren't supported by the client, through the session state object.
What I would do in your case is write a stored procedure that
1) checks the user credentials against the database
2) changes the login status to "logged-in" and
3) returns the status code to your app
(or of course, returns an error code if the user can't be validated).
That way you have to execute a single stored procedure, rather than making several round-trips to the server.
When you say "I'm new to this", you need define what you mean by "this".
You need to structure your posts to contain a general overview of the task, followed by a highly specific question. I'm still not sure what you know and don't know how to do!
Thomas D. Greer
www.tgreer.com
|
|
|
|
|
|
By "this" I mean the entire .net framework, I have a basic understanding of html, vb, and asp.net but when it comes to many specific and advanced (to me anyways) procedures I really don't know what I'm doing. I realize the authentication class you mentioned would be the best way of doing it, I will change it to that in time, but for now, all I am having trouble with is locating the 'login_status' variable in my database that corresponds with the username and password in my textboxes and changing the value from 0 to 1. I assume I have to write an UPDATE query string? I just don't know how to execute it to the specific table in the database. I know this may seem like a very basic thing, but I'm struggling with it.
Peter
|
|
|
|
My recommendation is the same: use a stored procedure for this task. Make your database do the database work.
Here is a sample stored procedure:
CREATE PROCEDURE spValidateUser
( @USERNAME varchar(50),
@PASSWORD varchar(50)
)
AS
update tblStaff set loginstatus = 1 where
(username=@USERNAME and password=@PASSWORD)
|
|
That stored procedure will udpate loginstatus ONLY IF the username and password are correct.
Here's how you would call/use that stored procedure in your ASP.NET page. My examples are in C#;
string username = ... // get username from user
string password = ... // get password from user
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
// database connection string from web.config.
SqlCommand command = new SqlCommand("spValidateUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = password;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();
|
|
Then, you check the value of "rows". If it is "0", then the udpate affected zero rows, meaning the username and/or password weren't valid. If the value is "1", then you know the update worked, so you know that the username/password were valid, and that loginstatus was updated.
If the value of rows is 2 or more, you have duplicate user accounts!
I'm trying to show you several best practices here:
1) store your database connection strings in the web.config file.
2) do all SQL queries, updates, inserts, deletes, etc. in stored procedures.
3) use ADO.NET in ASP.NET to call the stored procedures and process results
4) do NOT bring over entire tables into ASP.NET (datagrid, etc.), as that forces IIS to use a LOT of memory to maintain those tables.
Thomas D. Greer
www.tgreer.com
P.S. You'll need to grant whatever SQL user you're using in your connection string "execute" rights to the stored procedure.
If you want to do queries that return mulitple records, use a DataReader, then do a while.read() to process the records one at a time. Or, you can bind the datareader itself to a web control. DataReaders are forward-only, read-only objects, so require very little IIS memory compared to data adapters, data tables, etc.
|
|
|
|
|
|
I tried the stored procedure you suggested. I got it to work! thank you for your help, sorry if I was a bother to you, I'm sure this stuff all seems very simple to you lol..
Peter
|
|
|
|
You weren't a bother. If questions bothered me, I wouldn't be participating in a forum!
Thomas D. Greer
http://www.tgreer.com
|
|
|
|
|
|
|
|
|
|
Stryker
tgreer
