Can anyone help me? I dunno how to do login..
Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()
Dim da As New SqlClient.SqlDataAdapter()
Dim ds As New DataSet()
Dim dr As SqlClient.SqlDataReader
Dim alertScript As String
Function sMsgbox(ByVal errors As String)
alertScript = "<script language=javascript>"
alertScript &= "alert('" & errors & "');"
alertScript &= "</script" & ">"
Response.Write(alertScript)
End Function
Function chkuser()
If username.Text = "" Or password1.Text = "" Then
Call sMsgbox("Please Enter email and password")
username.Text = ""
password1.Text = ""
Else
Dim is_sql As String = "data source=CHIONG;initial catalog=library;integrated security=SSPI;persist security info=False;workstation id=CHIONG;packet size=4096"
cn.ConnectionString = is_sql
cn.Open()
cm.CommandText = "SELECT * FROM Member where MemEmail = '" & username.Text & "' and MemPwd = '" & password1.Text & "' and MemRole = 'admin' "
cm.Connection = cn
dr = cm.ExecuteReader()
If Not dr.RecordsAffected Then
Call sMsgbox("Sorry you have no permission to access this")
username.Text = ""
password1.Text = ""
Else
Response.Redirect("Admin1.aspx")
username.Text = ""
password1.Text = ""
End If
dr.Close()
End If
End Function
Private Sub LoginButton_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles LoginButton.Click
chkuser()
End SubSo are you getting an error or something? I can see one thing that may be affecting it since you're using a WHERE clause to find your name and password.
If dr.Read Then
Response.Redirect("Admin1.aspx")
username.Text = ""
password1.Text = ""
Else
Call sMsgbox("Sorry you have no permission to access this")
username.Text = ""
password1.Text =
End If
dr.Close()
End IfI didnt get error, the problem is that it seems like it doesnt execute the "If Not dr.RecordsAffected Then" statement.. but now i've modified it with the codes that you've given, and it is ok already.. many thanks
Exellent? Cheers.to juicemousezero,
I got another problem here....
If for example, i have many buttons in one
WebForm, how come after i do validation for one
particular button, then eventually the other buttons are not
functioning? The other buttons (Login button,
Register button and Forgot Password button) are
the menu at the left side, and the button with the
validation is the located in the middle. Should i use frameset to create the menu at the right side? Or i've done the validaiton wrongly? I didnt write any codes for validation, i use only the validator from the toolbox.. The codes for the buttons are all in one page, named Login.aspx.vbDo the buttons stop working when you click any of the buttons or just a particular one?
If it happens on any of them I'd say it has something to do with a logical error when the page posts back. Is this the same code you postsed above or is it different? That'd be helpful to see.Oopss.. i've set the "CausesValidation" for all the other buttons to True, so whenever i click the otehr buttons, they will perform the validation for the "Submit" button. Thanks for reminding me about the logical error...Thanks!
Back to the login function, can we read data from 2 tables at one time? Because at Function chkuser(), it gives me error saying that "There is already an open DataReader associated with this Connection which must be closed first."
The codes are below:
Function sMsgbox(ByVal errors As String)
alertScript = "<script language=javascript>"
alertScript &= "alert('" & errors & "');"
alertScript &= "</script" & ">"
Response.Write(alertScript)
End Function
Function chkuser()
If username.Text = "" Or password1.Text = "" Then
Call sMsgbox("Please Enter email and password")
username.Text = ""
password1.Text = ""
Else
Dim cn As New SqlClient.SqlConnection("data source=CHIONG;initial catalog=library;integrated security=SSPI;persist security info=False;workstation id=CHIONG;packet size=4096")
Dim SqlAdmin As String = "SELECT * FROM Administrator where AdminID = '" & username.Text & "' and AdminPwd = '" & password1.Text & "' "
Dim SqlMem As String = "SELECT * FROM Member where MemEmail = '" & username.Text & "' and MemPwd = '" & password1.Text & "' "
Dim cmAdmin As New SqlClient.SqlCommand(SqlAdmin, cn)
Dim cmMem As New SqlClient.SqlCommand(SqlMem, cn)
Dim drAdmin As SqlClient.SqlDataReader
Dim drMem As SqlClient.SqlDataReader
cn.Open()
drAdmin = cmAdmin.ExecuteReader()
drMem = cmMem.ExecuteReader()
If drAdmin.Read Then
Response.Redirect("Admin1.aspx")
username.Text = ""
password1.Text = ""
drAdmin.Close()
ElseIf drMem.Read Then
Response.Redirect("HomePage.aspx")
username.Text = ""
password1.Text = ""
drMem.Close()
Else
Call sMsgbox("Sorry you have no permission to access this")
username.Text = ""
password1.Text = ""
End If
End If
cn.Close()
End Function
Private Sub LoginButton_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles LoginButton.Click
chkuser()
End SubYeah I see what you mean. It's best to avoid multiple connections and objects cause of that, plus it just takes more overhead to run the program if you've got those objects floating around.
You're right about needing to read from two tables because in general it's prolly be better to do what you're doing more on the SQL side of things as opposed to the logic side. Assuming you've got numeric ID columns in both the Admin and Member tables it'd be something like...
SELECT a.AdminID, a.AdminPassword, m.MemEmail, m.MemPwd, m.MemID
FROM Administrator a inner join Member m
on a.AdminID = m.MemID
where a.AdminID = '" & username.Text & "' AND a.AdminPwd = '" & password1.Text & "' "
OR
m.MemEmail = '" & username.Text & "' AND m.MemPwd = '" & password1.Text & "' "
Then use a single query, datareader and connection to get your stuff from the database. You know what I mean?
The query.. is it the one u've shown above? So i have to use join table?Why is it a.AdminID = m.MemID?
DO u mean AdminID and MemID have the same value?
For TABLE Member, the promary key is MemEmail. I didnt create another ID for it.. so can i still apply ur codes there?Hmm.. gotcha. Well it should still work. I guess you can key off anything as long as it's unique and you can join any two tables by columns as long as the columns contain the same datatype.
SELECT a.AdminID, a.AdminPassword, m.MemEmail, m.MemPwd
FROM Administrator a inner join Member m
on a.AdminID = m.MemEmail
where a.AdminID = '" & username.Text & "' AND a.AdminPwd = '" & password1.Text & "' "
OR
m.MemEmail = '" & username.Text & "' AND m.MemPwd = '" & password1.Text & "' "
You just need to make sure that whatever columns you join by are of the same datatype and it should run.Dear expert,
I still couldnt figure out how should i redirect the admin and user to their own page respectively? It seems like it doesnt execute this statement "If dr.Read Then".. What should i do?Nah I'm no expert. I actually learn that again every day I come into work and hang out with real experts.
Anyway, you'll just need to build some sort of conditional statement that basically says if the name and password are taken from the admin table, Response.Redirect them to the admin page. If they're taken from the members table, Response.Redirect them to the members page.
It'd probably also be a good to do that with your SQL code (not the Response.Redirect, of course, but the conditional statements) so that you'll only be taking from the database exactly what you need and thus optimizing performance a little bit.Ooohhh.. that's the things i dunno about.. how can i know if the name and password are taken from admin table or member table? Since the SQL statement is joining table?Well... yeah... it's about to get complicated...
First of all I'd recemmend moving this SQL query into a stored procedure cause it'll be way easier to deal with. It'll also run faster.
Have you worked with stored procedures before?Stored procedures? never work with this before.. So what can it do? Maybe i do some research about stored procedures first.. Is it one of the topic of asp.net?Here are some articles on them. You can google something like "using stored procedures in vb.net" and find more, if need be.
<!-- m --><a class="postlink" href="http://www.411asp.net/home/tutorial/howto/database/storedpr">http://www.411asp.net/home/tutorial/how ... e/storedpr</a><!-- m -->
It's totally a SQL thing, but when it comes to ASP.NET you have to know quite a bit of a lot of languages. Making use of them here would be wise, I think.
Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()
Dim da As New SqlClient.SqlDataAdapter()
Dim ds As New DataSet()
Dim dr As SqlClient.SqlDataReader
Dim alertScript As String
Function sMsgbox(ByVal errors As String)
alertScript = "<script language=javascript>"
alertScript &= "alert('" & errors & "');"
alertScript &= "</script" & ">"
Response.Write(alertScript)
End Function
Function chkuser()
If username.Text = "" Or password1.Text = "" Then
Call sMsgbox("Please Enter email and password")
username.Text = ""
password1.Text = ""
Else
Dim is_sql As String = "data source=CHIONG;initial catalog=library;integrated security=SSPI;persist security info=False;workstation id=CHIONG;packet size=4096"
cn.ConnectionString = is_sql
cn.Open()
cm.CommandText = "SELECT * FROM Member where MemEmail = '" & username.Text & "' and MemPwd = '" & password1.Text & "' and MemRole = 'admin' "
cm.Connection = cn
dr = cm.ExecuteReader()
If Not dr.RecordsAffected Then
Call sMsgbox("Sorry you have no permission to access this")
username.Text = ""
password1.Text = ""
Else
Response.Redirect("Admin1.aspx")
username.Text = ""
password1.Text = ""
End If
dr.Close()
End If
End Function
Private Sub LoginButton_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles LoginButton.Click
chkuser()
End SubSo are you getting an error or something? I can see one thing that may be affecting it since you're using a WHERE clause to find your name and password.
If dr.Read Then
Response.Redirect("Admin1.aspx")
username.Text = ""
password1.Text = ""
Else
Call sMsgbox("Sorry you have no permission to access this")
username.Text = ""
password1.Text =
End If
dr.Close()
End IfI didnt get error, the problem is that it seems like it doesnt execute the "If Not dr.RecordsAffected Then" statement.. but now i've modified it with the codes that you've given, and it is ok already.. many thanks
Exellent? Cheers.to juicemousezero,I got another problem here....
If for example, i have many buttons in one
WebForm, how come after i do validation for one
particular button, then eventually the other buttons are not
functioning? The other buttons (Login button,
Register button and Forgot Password button) are
the menu at the left side, and the button with the
validation is the located in the middle. Should i use frameset to create the menu at the right side? Or i've done the validaiton wrongly? I didnt write any codes for validation, i use only the validator from the toolbox.. The codes for the buttons are all in one page, named Login.aspx.vbDo the buttons stop working when you click any of the buttons or just a particular one?
If it happens on any of them I'd say it has something to do with a logical error when the page posts back. Is this the same code you postsed above or is it different? That'd be helpful to see.Oopss.. i've set the "CausesValidation" for all the other buttons to True, so whenever i click the otehr buttons, they will perform the validation for the "Submit" button. Thanks for reminding me about the logical error...Thanks!
Back to the login function, can we read data from 2 tables at one time? Because at Function chkuser(), it gives me error saying that "There is already an open DataReader associated with this Connection which must be closed first."
The codes are below:
Function sMsgbox(ByVal errors As String)
alertScript = "<script language=javascript>"
alertScript &= "alert('" & errors & "');"
alertScript &= "</script" & ">"
Response.Write(alertScript)
End Function
Function chkuser()
If username.Text = "" Or password1.Text = "" Then
Call sMsgbox("Please Enter email and password")
username.Text = ""
password1.Text = ""
Else
Dim cn As New SqlClient.SqlConnection("data source=CHIONG;initial catalog=library;integrated security=SSPI;persist security info=False;workstation id=CHIONG;packet size=4096")
Dim SqlAdmin As String = "SELECT * FROM Administrator where AdminID = '" & username.Text & "' and AdminPwd = '" & password1.Text & "' "
Dim SqlMem As String = "SELECT * FROM Member where MemEmail = '" & username.Text & "' and MemPwd = '" & password1.Text & "' "
Dim cmAdmin As New SqlClient.SqlCommand(SqlAdmin, cn)
Dim cmMem As New SqlClient.SqlCommand(SqlMem, cn)
Dim drAdmin As SqlClient.SqlDataReader
Dim drMem As SqlClient.SqlDataReader
cn.Open()
drAdmin = cmAdmin.ExecuteReader()
drMem = cmMem.ExecuteReader()
If drAdmin.Read Then
Response.Redirect("Admin1.aspx")
username.Text = ""
password1.Text = ""
drAdmin.Close()
ElseIf drMem.Read Then
Response.Redirect("HomePage.aspx")
username.Text = ""
password1.Text = ""
drMem.Close()
Else
Call sMsgbox("Sorry you have no permission to access this")
username.Text = ""
password1.Text = ""
End If
End If
cn.Close()
End Function
Private Sub LoginButton_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles LoginButton.Click
chkuser()
End SubYeah I see what you mean. It's best to avoid multiple connections and objects cause of that, plus it just takes more overhead to run the program if you've got those objects floating around.
You're right about needing to read from two tables because in general it's prolly be better to do what you're doing more on the SQL side of things as opposed to the logic side. Assuming you've got numeric ID columns in both the Admin and Member tables it'd be something like...
SELECT a.AdminID, a.AdminPassword, m.MemEmail, m.MemPwd, m.MemID
FROM Administrator a inner join Member m
on a.AdminID = m.MemID
where a.AdminID = '" & username.Text & "' AND a.AdminPwd = '" & password1.Text & "' "
OR
m.MemEmail = '" & username.Text & "' AND m.MemPwd = '" & password1.Text & "' "
Then use a single query, datareader and connection to get your stuff from the database. You know what I mean?
The query.. is it the one u've shown above? So i have to use join table?Why is it a.AdminID = m.MemID? DO u mean AdminID and MemID have the same value?
For TABLE Member, the promary key is MemEmail. I didnt create another ID for it.. so can i still apply ur codes there?Hmm.. gotcha. Well it should still work. I guess you can key off anything as long as it's unique and you can join any two tables by columns as long as the columns contain the same datatype.
SELECT a.AdminID, a.AdminPassword, m.MemEmail, m.MemPwd
FROM Administrator a inner join Member m
on a.AdminID = m.MemEmail
where a.AdminID = '" & username.Text & "' AND a.AdminPwd = '" & password1.Text & "' "
OR
m.MemEmail = '" & username.Text & "' AND m.MemPwd = '" & password1.Text & "' "
You just need to make sure that whatever columns you join by are of the same datatype and it should run.Dear expert,
I still couldnt figure out how should i redirect the admin and user to their own page respectively? It seems like it doesnt execute this statement "If dr.Read Then".. What should i do?Nah I'm no expert. I actually learn that again every day I come into work and hang out with real experts.
Anyway, you'll just need to build some sort of conditional statement that basically says if the name and password are taken from the admin table, Response.Redirect them to the admin page. If they're taken from the members table, Response.Redirect them to the members page.
It'd probably also be a good to do that with your SQL code (not the Response.Redirect, of course, but the conditional statements) so that you'll only be taking from the database exactly what you need and thus optimizing performance a little bit.Ooohhh.. that's the things i dunno about.. how can i know if the name and password are taken from admin table or member table? Since the SQL statement is joining table?Well... yeah... it's about to get complicated...
First of all I'd recemmend moving this SQL query into a stored procedure cause it'll be way easier to deal with. It'll also run faster.
Have you worked with stored procedures before?Stored procedures? never work with this before.. So what can it do? Maybe i do some research about stored procedures first.. Is it one of the topic of asp.net?Here are some articles on them. You can google something like "using stored procedures in vb.net" and find more, if need be.
<!-- m --><a class="postlink" href="http://www.411asp.net/home/tutorial/howto/database/storedpr">http://www.411asp.net/home/tutorial/how ... e/storedpr</a><!-- m -->
It's totally a SQL thing, but when it comes to ASP.NET you have to know quite a bit of a lot of languages. Making use of them here would be wise, I think.