We help IT Professionals succeed at work.

Logon Control in Visual Studio 2008 in Visual Basic with SQL Server 2005

I have created a table in SQL 2005 that has userLogonId (primary key) and userPassword columns. I have used the Login tool under the Login Category of Toolbox in Visual Studio 2008. I have used a connectionString in the vb page to connect my data source to my drop-downList in order to SELECT items, yet I have no clue what code is needed to search through my table and see 1 if my Logon exists, and 2 if the password is in the same row.
I have googled this to death and serached "help" in Visual Studio yet there are no scripts for this given and I don't know Visual Basic (now if I need to write a script in Java.. but does me no good here).
Since you helped me soooo much on my last question, I would greatly appreciate assistance on this one. If I could award more than 500 points, I woul. This has really stumped me!
Here is what I have so fat http://comweb.uml.edu/elamo57769/project1/userAdmin.aspx
Bette
Comment
Watch Question

Molly FaganApplications Team Supervisor

Commented:
Is this a homework question?  The domain of your web site has me wondering.

Check out this link, http://www.homeandlearn.co.uk/net/nets12p5.html, it will get you started on querying a table and returning data to your app.
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
Yes it is homework and all of us in the class who have made it so far are stumped on this last part. We have all searched the texbook and Googled this. I don't know how familiar you are with on-line courses -- frequently there is a lack of communication with instructors who are elsewhere employed. I don't care about this so much for the assignment as all in the class are lost and in the same boat -- I do web sites for a living and am trying to learn ASP.NET Visual Studio and need some outside help.
I don't need the actual code -- don't expect to be spoon fed -- just where to look to find info about this.
Thanks for the link -- I will check it out and get back to you if it helps.
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
Sorry mjfagan, this is not what I need. I will keep searching. Below is the aspx and aspx.vb code I entered to get a drop-down list to work. In it I connected with my DB with my connectionString and all went well. This was the only example the instructor gave us to work with. The students who have reached this point in the project are all having an impossible time figuring how to modify it to apply to a logon situation.
Any links or words of wisdom that would assist us in figuring this out would be GREATLY appreciated!
Thanks for your effort.

Default.aspx Page sourcecode:
<code>
<%@ Page Language="VB" MasterPageFile="~/Demo.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="header" Runat="Server">Here is the header!
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="main" Runat="Server">
    <asp:DropDownList ID="dlLastName" runat="server" DataSourceID="SqlDataSource1"
        DataTextField="LastName" DataValueField="LastName">
    </asp:DropDownList><asp:Button ID="Button1" runat="server" Text="Go" />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommand="SELECT LastName FROM Employees ORDER BY LastName">
    </asp:SqlDataSource>
    <asp:Button ID="butRedirect" runat="server" Text="Redirect" />
</asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="footer" Runat="Server">Here is footer
</asp:Content>
</code>
Default.aspx.vb  Page sourcecode:
<code>
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myReader As Data.SqlClient.SqlDataReader
        Dim mySqlConnection As Data.SqlClient.SqlConnection
        Dim mySqlCommand As Data.SqlClient.SqlCommand
        'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
        mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
        Dim sql As String = "SELECT FirstName FROM Employees WHERE LastName = '" & Me.dlLastName.Text & "'"
        mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
        Try
            mySqlConnection.Open()
            myReader = mySqlCommand.ExecuteReader()
            If (myReader.HasRows) Then
                'Read in the first row to initialize the DataReader; we will on read the first row.
                myReader.Read()
                Dim content As ContentPlaceHolder
                content = Page.Master.FindControl("main")
                Dim lbl As New Label()
                lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
                content.Controls.Add(lbl)
            End If
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If Not (myReader Is Nothing) Then
                myReader.Close()
            End If
            If (mySqlConnection.State = Data.ConnectionState.Open) Then
                mySqlConnection.Close()
            End If
        End Try
    End Sub
End Class
</code

Bette
Molly FaganApplications Team Supervisor

Commented:
Okay, your original post said, "yet I have no clue what code is needed to search through my table and see 1 if my Logon exists, and 2 if the password is in the same row."  For table, are you talking about a SQL Server table?  That's how I took your original post but since you have code return data back from SQL Server, now I'm not sure what you're exactly having an issue with.
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
If you look at my last post, I was showing you the only example the students were given to connect their web page controls to the database. In this example, we connected our dropDownList to our tables in order to select an employee and display another column from the table. THIS WAS ONLY AN EXAMPLE OF WHAT WE HAVE DONE SO FAR :-)
Now in my CURRENT project, if you followed the link I gave, you will see that I am attempting to validate the logon box. I was trying to modify the ONLY example of connecting to our databases and querying our tables for information that our class was given. I have no clue how to change the vb code to validate the login username/password because 1. I have no background in VB 2. This is the first course I have taken that uses SQL Server2005 (most,if not all, of the class is in the same boat), and 3. this is our first class in Visual Studio 2008 and we are just learning. As you can see, there is a big gap between the example we were given and what we have been asked to do with the Login Box. We all need some help -- either links or explanation.
Have I made myself clearer?
Molly FaganApplications Team Supervisor
Commented:
If you're just wanting to validate a user name and password against a table in the database, all you have to do is change the query you have in your example but you're going to be passing in the contents of the login name textbox and the password textbox.  I don't know what your table name is or the field names but you're going to be doing a SELECT (field list) FROM (table name) WHERE UserID = txtUser.Text AND Password = txtPassword.Text.

If there are no rows returned, then the user name and password combo is not in the table.
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You need to handle the Authenticate event of the login control

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.login.authenticate.aspx

Write your code in this event to check if the user is valid. Do you know how to query a database table?
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
I think I am almost there yet I get these error messages in Default.aspx.vb::
Line 1 "Handles clause requires a WithEvents variable defined in the containing type or one of its base types."
 "Error  'LoginName' is not a member of '_Default'"
"Error 'Password' is not a member of '_Default'."
 and you can see in the Default.asp file that I have them as textbox Id's
<code>
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="header" Runat="Server"><h1>Whispering Oaks Arabians</h1>
<h2>Home of 16.2h Jumper Champion TLA Halynov! </h2>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="main" Runat="Server">
    <h3>Log in </h3>
    Enter User Name <br />
    <input id="LoginName" type="text" />
    <br />
    Enter Password<br />
    <input id="Password" type="password" /><br /><br />
    <input id="butLogon" type="submit"
        value="Logon" />&nbsp;&nbsp;&nbsp;&nbsp;<input id="butReset" type="reset" value="reset" /><br />
    <br />
    </asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="footer" Runat="Server"> <br />
    </asp:Content>
</code>

and here is my Default.aspx.vb

<code>
Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub butLogon_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butLogon.Click
        Dim myReader As Data.SqlClient.SqlDataReader
        Dim mySqlConnection As Data.SqlClient.SqlConnection
        Dim mySqlCommand As Data.SqlClient.SqlCommand
        'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
        mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
        Dim sql As String = "SELECT userLogonId FROM MyUsers WHERE userLogonId = '" & Me.LoginName.text & "' AND userPassword = '" & Me.Password.password & "' "
        mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
        Try
            mySqlConnection.Open()
            myReader = mySqlCommand.ExecuteReader()
            If (myReader.HasRows) Then
                'Read in the first row to initialize the DataReader; we will on read the first row.
                myReader.Read()
                Dim content As ContentPlaceHolder
                content = Page.Master.FindControl("main")
                Dim lbl As New Label()
                lbl.Text = "The User Name or Password you choose is correct"
                content.Controls.Add(lbl)
            End If
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If Not (myReader Is Nothing) Then
                Dim content As ContentPlaceHolder
                content = Page.Master.FindControl("main")
                Dim lbl As New Label()
                lbl.Text = "The User Name or Password you choose is incorrect"
                content.Controls.Add(lbl)
                myReader.Close()
            End If
            If (mySqlConnection.State = Data.ConnectionState.Open) Then
                mySqlConnection.Close()
            End If
        End Try
    End Sub
End Class
</code>

you have earned some points already, even if you are unable to stick with me til the end :-)
I really appreciate your help and I believe I am headed in the right direction.
Bette





BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Those are html controls. Change them to asp:textbox server controls.

Have you stopped using the Login control?
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
Yes, Code Cruiser, I couldn't figure out how to pull out the user name and password from that control as it did not ID the 2 input boxes. I am supposed to connect with my database table MyUsers to see if there is a column with userNameId matched with userPassword to I needed to get the input from each of those 2 text boxes.
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can get username and password using logincontrol.username and logincontrol.password properties as shown in the example I linked. Please do take a moment to look on that.
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
Well that sure helped,Code Cruiser
I am down to 2 errors now:
Error 1      The 'Text' property of 'asp:TextBox' does not allow child objects. Default.aspx    line 12
Error 2      Handles clause requires a WithEvents variable defined in the containing type or one of its base types.Default.aspx.vb  line 5 column 95
My code in Default.aspx.vb has stayed the same as above
Here is my new Default.aspx:
<code>
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="header" Runat="Server"><h1>Whispering Oaks Arabians</h1>
<h2>Home of 16.2h Jumper Champion TLA Halynov! </h2>
</asp:Content>

<asp:Content ID="Content3" ContentPlaceHolderID="main" Runat="Server">
    <h3>Log-in</h3>
    Enter User Name <br />
    &nbsp;<asp:TextBox ID="LoginName" runat="server"></asp:TextBox>
    <br />
    Enter Password<br /><asp:TextBox ID="Password" runat="server"></asp:TextBox>
    <br />
    <br /><br />
    <input id="butLogon" type="submit" value="Logon" />&nbsp;&nbsp;&nbsp;&nbsp;<input id="butReset" type="reset" value="reset" /><br />
    <br />
    </asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="footer" Runat="Server"> <br />
    </asp:Content>
</code>
I really appreciate your help!

BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Change your code to below


Regarding error2, show me your code behind.
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Untitled Page" %>

<asp:Content ID="Content3" ContentPlaceHolderID="main" Runat="Server">
<h1>Whispering Oaks Arabians</h1>
<h2>Home of 16.2h Jumper Champion TLA Halynov! </h2>
    <h3>Log-in</h3>
    Enter User Name <br />
    &nbsp;<asp:TextBox ID="txtLoginName" runat="server" />
    <br />
    Enter Password<br /><asp:TextBox ID="txtPassword" runat="server" />
    <br />
    <br /><br />
    <input id="butLogon" type="submit" value="Logon" />&nbsp;&nbsp;&nbsp;&nbsp;<input id="butReset" type="reset" value="reset" /><br />
    <br />
    </asp:Content>

Open in new window

Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
With this link that you sent me to, CodeCruiser, I am used to using a XXX.aspx.vb behind code file. (see above) and not sure what kind of code for site specific custom is it referring to? Is it the connectionString statement? The question marks are my addition to the script
<code>
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
Function SiteSpecificAuthenticationMethod(ByVal UserName As String, ByVal Password As String) As Boolean
    //Insert code that implements a site-specific custom ??? connectionString????
    //authentication method here.???
    // This example implementation always returns false.?????Why would I want it to be false???
    Return False
End Function

Sub OnAuthenticate(ByVal sender As Object, ByVal e As AuthenticateEventArgs)
    Dim Authenticated As Boolean
    Authenticated = SiteSpecificAuthenticationMethod(Login1.UserName, Login1.Password)
    e.Authenticated = Authenticated
End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
        <form id="form1" runat="server">
            <asp:Login id="Login1" runat="server"
                OnAuthenticate="OnAuthenticate">
            </asp:Login>
        </form>
    </body>
</html>
</code>
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Now you keep jumping between using logon control and using your own UI. Your previous code was custom UI which I corrected. Now you are back to login control. First, make up your mind.
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
I haven't changed anything -- I was just commenting on the link you sent me. I am still using the code with the separate controls for user name and password. I am supposed to use the 2 separate controls -- I was getting desperate and trying to use anything that would work at this point. I just made reference to your link -- not saying I was going to change my whole code. I like using the code behind approach. Sorry if I confused you.
Bette
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Ok no problem. Now one of your errors is gone right? Do you still have the other error?
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
Hi CodeCruiser
Re your comment above -- I haven't changed my code behind -- it is the same as it was above
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
CodeCruiser
I followed your suggestion above to rename to "textLogonName" and textPassword"  yet it didn't change my only error message left which is
"Error 1 Handles clause requires a WithEvents variable defined in the containing type or one of its base types.  Default.aspx.vb  line5   column 95.  "

Just so that we are on the same page my aspx file:

<code>
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="header" Runat="Server"><h1>Whispering Oaks Arabians</h1>
<h2>Home of 16.2h Jumper Champion TLA Halynov! </h2>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="main" Runat="Server">
    <h3>Log-in</h3>
    Enter User Name <br />
    &nbsp;<asp:TextBox ID="textLoginName" runat="server"></asp:TextBox>
    <br />
    Enter Password<br /><asp:TextBox ID="textPassword" runat="server"></asp:TextBox>
    <br />
    <br /><br />
    <input id="butLogon" type="submit" value="Logon" />&nbsp;&nbsp;&nbsp;&nbsp;<input id="butReset" type="reset" value="reset" /><br />
    <br />
    </asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="footer" Runat="Server"> <br />
    </asp:Content>
</code>

Here is my behind code file has changed because of your suggestion of textUserName and textLogiName so it now reads

<code>

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub butLogon_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butLogon.Click
        Dim myReader As Data.SqlClient.SqlDataReader

        Dim mySqlConnection As Data.SqlClient.SqlConnection

        Dim mySqlCommand As Data.SqlClient.SqlCommand

        Dim butLogon_Click As System.Web.UI.WebControls.Button

        'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.

        mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())

        Dim sql As String = "SELECT userLogonId FROM MyUsers WHERE userLogonId = '" & Me.textLoginName.Text & "' AND userPassword = '" & Me.textPassword.Text & "' "

        mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)

        Try
            mySqlConnection.Open()
            myReader = mySqlCommand.ExecuteReader()
            If (myReader.HasRows) Then
                'Read in the first row to initialize the DataReader; we will on read the first row.
                myReader.Read()
                Dim content As ContentPlaceHolder
                content = Page.Master.FindControl("main")
                Dim lbl As New Label()
                lbl.Text = "The User Name or Password you choose is correct"
                content.Controls.Add(lbl)
            End If
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If Not (myReader Is Nothing) Then
                Dim content As ContentPlaceHolder
                content = Page.Master.FindControl("main")
                Dim lbl As New Label()
                lbl.Text = "The User Name or Password you choose is incorrect"
                content.Controls.Add(lbl)
                myReader.Close()
            End If
            If (mySqlConnection.State = Data.ConnectionState.Open) Then
                mySqlConnection.Close()
            End If
        End Try
    End Sub
End Class
</code>

I really appreciate your help!
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Change

<input id="butLogon" type="submit" value="Logon" />

to

<asp:button runat="server" id="butLogon" type="submit" value="Logon" />
BRONZE EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Change

<input id="butLogon" type="submit" value="Logon" />

to

<asp:button runat="server" id="butLogon"  text="Logon" />
Bette LamoreWeb Designer/Developer/GIS Professional

Author

Commented:
Both people helped me a LOT !! and it was the combination.
Really grateful!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.