Log in Webform validation using Access

Question

tricket_7 -3 Newbie Poster

12 Years Ago

I am having trouble getting the codebehind to execute properly. I am new to c#.net, what I have is a web-based log in form that when the user enters their username and Id it will check against ms access to verify the username and password. I am getting this error "ExecuteScalar requires an open and available Connection. The connection's current state is closed."
code below for both form and codebehind
form:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index1.aspx.cs" Inherits="index1.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">
    <title>Website Services Login</title>
    <style type="text/css">
        .style1
        {
            color: #000080;
        }
        .style2
        {
            width: 176px;
        }
         .style8
        {
            color:#FF0000;
            width: 176px;
        }
    </style>
</head>
<body>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<!-- ********* Top Logo *********** -->

    <tr>
        <td><img src="http://www.website.com/images/spacer.gif" height="1" width="20" alt="line"/>A website Division</td>
        <td align="right">
        <!-- Display Date -->
<script type="text/javascript" language="javascript">
<!--    Begin
    d = new Array(
"Sunday",
"Monday",
"Tuesday",
"Wednesday",
"Thursday",
"Friday",
"Saturday"
);
    m = new Array(
"January",
"February",
"March",
"April",
"May",
"June",
"July",
"August",
"September",
"October",
"November",
"December"
);

    today = new Date();
    day = today.getDate();
    year = today.getYear();

    if (year < 2000)
        year = year + 1900;

    end = "th";
    if (day == 1 || day == 21 || day == 31) end = "st";
    if (day == 2 || day == 22) end = "nd";
    if (day == 3 || day == 23) end = "rd";
    day += end;

    document.write("<font face='Verdana, Arial, Helvetica' size='1'>");
    document.write(d[today.getDay()] + ", " + m[today.getMonth()] + " ");
    document.write(day + ", " + year);
    document.write("</font><img src='http://www.website.com/images/spacer.gif' height='1' width='20'>");
// End -->
</script>


        </td></tr>
        <tr><td valign="top" colspan="2"><img src="http://www.website.com/images/line.gif" height="1" width="100%" alt="line"/></td></tr>        
        </table>
        <table width="100%">
<tr>
    <td align="center" valign="top">
        <span class="style1">&nbsp;New users can </span> <a href="signup.aspx">
        <span class="style1">register here.</span></a><br />

    <form id="form1" runat="server">
    <div>
          <table border="3" cellpadding="0" cellspacing="1" 
              style="background-color: #EBEDEC; border-color: #C0C0C0; border-style: solid; border-width: 2px 20px 2px 20px">
<tr>
    <td>

<table border="0" width="230" cellpadding="4">
<tr>
        <td colspan="2" align="left">&nbsp;<strong style="color: #000080">Please Login.</strong></td>
    </tr>
    <tr>
        <td class="style2">
            <asp:Label ID="lbl_username" runat="server" Text="Username" style="color: #000080"></asp:Label></td>
        <td>
            <asp:TextBox ID="txt_username" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td class="style2">
            <asp:Label ID="lbl_password" runat="server" Text="Password" style="color: #000080"></asp:Label></td>
        <td>
            <asp:TextBox ID="txt_password" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Button ID="btn_submit" runat="server" Text="Login" onclick="btnSubmit_Click" ></asp:Button></td></tr>

    </tr>

</table>
<tr>
            <td class="style8" align="center"><asp:Label ID="labelMessage" runat="server" Text=""></asp:Label></td></tr>
</td>
</tr>
</table> 

    </div>
    </form>
    <br />

If you have difficulty accessing the system or cannot remember your username and password, please contact Website Support via email at <a href="mailto:support@website.com">support@website.com</a>.
<br /><br />
<em>Unauthorized access to this system is strictly prohibited.</em>
<br /><br /><br />
&nbsp;Contents Copyright © 2003 <a href="http://www.website.com">Website</a>. All rights reserved.    
</td>
</tr>
</table> 

</body>
</html>

codebehind: This is what is giving me trouble

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

namespace index1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        System.Data.OleDb.OleDbConnection con;
        DataSet ds1;





        protected void Page_Load(object sender, EventArgs e)
        {

            con = new System.Data.OleDb.OleDbConnection();
            ds1 = new DataSet();



        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:/Users/Christie/Desktop/Christie/website/FB Inquiry/access files/users.mdb";

            string cmdstr = "Select count(*) from tblCustRecords where Username='" + txt_username.Text + "'";
            OleDbCommand checkUser = new OleDbCommand(cmdstr, con);
            int temp = Convert.ToInt32(checkUser.ExecuteScalar().ToString());
            if (temp == 1)
            {
                string cmdstr2 = "Select Password from tblCustRecords where Username='" + txt_password.Text + "'";
                OleDbCommand pass = new OleDbCommand(cmdstr2, con);
                string password = pass.ExecuteScalar().ToString();
                con.Close();
                if (password == txt_password.Text)
                {
                    labelMessage.Visible = true;
                    labelMessage.Text = "you are logged in";
                }
                else
                {
                    labelMessage.Visible = true;
                    labelMessage.Text = "Invalid password..!!";
                }
            }
            else
            {
                labelMessage.Visible = true;
                labelMessage.Text = "Invalid username..!!";
            }
            labelMessage.Text = "*Incorrect Password or Username";

        }
    }
    }

Can someone please help me, I feel so lost, am I at least in the right direction?

4 Contributors
7 Replies
157 Views
1 Week Discussion Span
Latest Post 11 Years Ago Latest Post by Mike Askew

vasanth19 14 Newbie Poster

12 Years Ago

Set TextMode="Password" like this,
<asp:TextBox ID="txt_password" TextMode="Password" runat="server"></asp:TextBox>

Mike Askew 131 Veteran Poster

11 Years Ago

"The Microsoft Jet database engine cannot find the input table or query 'tblCustRecords'. Make sure it exists and that its name is spelled correctly."

Check your spelling your database table name correctly to solve that one, and that it is pointing to the correct database.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Bhuvan Ram 0 Newbie Poster · Answer 1 · 2012-11-08T12:26:01+00:00

Open a Connection Before if Statement "* int temp = Convert.ToInt32(checkUser.ExecuteScalar().ToString());" as Conn.Open(); Then it will work fine...

tricket_7 -3 Newbie Poster · Answer 2 · 2012-11-14T15:05:02+00:00

It sort of works, now I am getting this error:

"The Microsoft Jet database engine cannot find the input table or query 'tblCustRecords'. Make sure it exists and that its name is spelled correctly."

Here is the code with the fixes,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

namespace index1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        System.Data.OleDb.OleDbConnection con;
        DataSet ds1;





        protected void Page_Load(object sender, EventArgs e)
        {

            con = new System.Data.OleDb.OleDbConnection();
            ds1 = new DataSet();


        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=F:\\Website\\prosearchusers.mdb";
            string cmdstr = "Select count(*) from tblCustRecords where Username='" + txt_username.Text + "'";
            OleDbCommand checkUser = new OleDbCommand(cmdstr, con);
            con.Open();
            int temp = Convert.ToInt32(checkUser.ExecuteScalar().ToString());

            if (temp == 1)
            {
                string cmdstr2 = "Select Password from tblCustRecords where Username='" + txt_password.Text + "'";
                OleDbCommand pass = new OleDbCommand(cmdstr2, con);
                string password = pass.ExecuteScalar().ToString();

                if (password == txt_password.Text)
                {
                    labelMessage.Visible = true;
                    labelMessage.Text = "you are logged in";
                }
                else
                {
                    labelMessage.Visible = true;
                    labelMessage.Text = "Invalid password..!!";
                }
            }
            else
            {
                labelMessage.Visible = true;
                labelMessage.Text = "Invalid username..!!";
            }
            labelMessage.Text = "*Incorrect Password or Username";
        }
    }
}

tricket_7 -3 Newbie Poster · Answer 3 · 2012-11-14T15:38:42+00:00

also, how do I make it where when the user enters their password it shows **** instead of the text?

tricket_7 -3 Newbie Poster · Answer 4 · 2012-11-20T18:26:21+00:00

It was because the word tbl was not before CustRecords, however, now that I have that fixed I am getting an error for my reader "System.Data.OleDb.OleDbException was unhandled by user code

Message="No value given for one or more required parameters."
My new code is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Configuration;


namespace index1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        //System.Data.OleDb.OleDbConnection con;
        //DataSet ds1;





        protected void Page_Load(object sender, EventArgs e)
        {



        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {

            OleDbConnection dbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Users\\Christie\\Desktop\\prosearchusers.mdb");
            string queryString = "SELECT CUST_ID, CUST_PASSWORD FROM tblCustRecords where username='" + txt_username.Text + "' AND password='" + txt_pass.Text + "'";

            OleDbCommand command = new OleDbCommand(queryString, dbConn);
            dbConn.Open();
            OleDbDataReader reader = command.ExecuteReader();
            // Read before accessing data.
            reader.Read();
            if (reader.HasRows)
            {
                // userID plus user password combination found
                labelMessage.Visible = true;
                labelMessage.Text = "It works..";

            }
            else
            {
                // userID plus user password combination not found
                labelMessage.Visible = true;
                labelMessage.Text = "Incorrect..";

            }
            // call Close when done reading.
            reader.Close();

            // Close this OleDbConnection instance
            dbConn.Close();
        }
    }
}

Mike Askew 131 Veteran Poster Featured Poster · Answer 5 · 2012-11-20T19:11:15+00:00

So now your declaring parameters in the stored proc which you then do not pass a value too from C# side