[Solved] ASP.Net Error: There is already an open DataReader associated with this Command which must be closed first

indradeo

SOLVED CLOSED ACTIVE ANSWERED
User: indradeo
Posted: on Oct 21, 2021 12:06 AM
Forum: Data Controls
Answer: 1
Views: 24202

dear team,

while i have make multiple login function with dropdown.

i have getting metion error.

Server Error in '/' Application.

There is already an open DataReader associated with this Command which must be closed first.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. Source Error:

Line 42:             }
Line 43: 
Line 44:             sda.Fill(dt);
Line 45:             
Line 46:             if (dt.Rows.Count > 0)

namespace TS
{
    public partial class login1 : System.Web.UI.Page
    {
        protected void btnLogin_Click(object sender, EventArgs e)
        {
            int companyId;
            
            SqlConnection con = new SqlConnection(@"Data Source=(localdb)\Projects;Initial Catalog=WCF;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False");
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from emp_mast1 where empid='" + txtusername.Text + "' and Password='" + txtpassword.Text + "' and companyId='" + ddltype.SelectedValue + "'", con);
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            cmd.Parameters.AddWithValue("@empid", txtusername.Text.ToString());
            cmd.Parameters.AddWithValue("@Password", txtpassword.Text.ToString());
            cmd.Parameters.AddWithValue("@companyId", ddltype.SelectedValue.ToString());
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Session["empid"] = Convert.ToInt32(reader["empid"].ToString());
                Session["firstname"] = reader["firstname"].ToString();
                Session["lastname"] = reader["lastname"].ToString();
                Session["department"] = reader["department"].ToString();
                reader.Close();
               
            }

            sda.Fill(dt);
            
            if (dt.Rows.Count > 0)
            {
                companyId = int.Parse(dt.Rows[0]["CompanyId"].ToString());
                switch (companyId)
                {
                    case 1:
                        Response.Redirect("Index.aspx");
                        break;
                    case 2:
                        Response.Redirect("Index1.aspx");
                        break;
                    case 3:
                    Response.Redirect("cisf_approv.aspx");
                    break;
                }
            }
            else
            {
                reader.Close();
                cmd.Dispose();
                con.Close();

                lblMessage.Font.Size = FontUnit.Large;
                lblMessage.Text = "Invalid credentials";
                lblMessage.ForeColor = System.Drawing.Color.Red;
            }
        }
    }

}

CREATE TABLE [dbo].[emp_mast1] (
    [Id]          INT           IDENTITY (1, 1) NOT NULL,
    [empid]       NCHAR (6)     NOT NULL,
    [firstname]   NVARCHAR (50) NULL,
    [lastname]    NVARCHAR (50) NULL,
    [companyId]   NCHAR (10)    NULL,
    [designation] NVARCHAR (50) NULL,
    [department]  NVARCHAR (50) NULL,
    [dept_code]   NCHAR (2)     NULL,
    [dob]         DATETIME      NULL,
    [mobile]      FLOAT (53)    NULL,
    [email_id]    NCHAR (50)    NULL,
    [photo]       IMAGE         NULL,
    [status]      NCHAR (1)     NULL,
    [Password]    VARCHAR (50)  NULL
);

arjunv

0 12

Reply
ANSWER
User: arjunv
Replied: on Oct 28, 2021 12:59 AM
Modified: on Oct 28, 2021 01:43 AM
Report

Hi Indradeo,

Please refer below Sample.

SQL

CREATE TABLE [dbo].[emp_mast_TS] (
    [empid]       NCHAR (6)     NOT NULL,
    [Password]    VARCHAR (50)  NULL,
    [companyId]   VARCHAR (50)  NULL,
    [firstname]   NVARCHAR (50) NULL,
    [lastname]    NVARCHAR (50) NULL,
    [designation] NVARCHAR (50) NULL,
    [department]  NVARCHAR (50) NULL,
    [dept_code]   NCHAR (2)     NULL,
    [dob]         DATETIME      NULL,
    [mobile]      FLOAT (53)    NULL,
    [email_id]    NCHAR (50)    NULL,
    [photo]       IMAGE         NULL,
    [status]      NCHAR (1)     NULL
    
);
INSERT INTO emp_mast_TS
VALUES ('1','pass@123','1','Mudassar','Khan',NULL,'Computer',NULL,NULL,NULL,NULL,NULL,NULL)

HTML

<table align="left" style="margin-left: 4px; width: 390px; height: 210px;" class="auto-style18">
    <tr>
        <td align="center" class="auto-style3" colspan="3">
            <body>
                <font color="" size="5"><strong>TS active sheet Login Screen</strong></font><strong></b><br />
                </strong>
            </body>
        </td>
        <body>
    </tr>
    <tr>
        <td>User Id</td>
        <td colspan="2">
            <asp:TextBox runat="server" ID="txtusername" ValidationGroup="add" CssClass="validate[required]" Width="295px" Height="36px"></asp:TextBox></td>
    </tr>
    <tr>
        <td>Password</td>
        <td>
            <asp:TextBox runat="server" ID="txtpassword" TextMode="Password" CssClass="validate[required]" Width="296px" Height="36px"></asp:TextBox><br />
        </td>
        <td>
            <asp:Label ID="lblMessage" runat="server" CssClass="auto-style17" />
        </td>
    </tr>
    <tr>
        <td>Report</td>
        <td colspan="2">
            <asp:DropDownList ID="ddltype" runat="server" Height="36px" Width="292px" CssClass="auto-style3">
                <asp:ListItem Value="2">Bill Processing Status</asp:ListItem>
                <asp:ListItem Value="1">Praposal Status</asp:ListItem>
                <asp:ListItem Value="3">Follow Ups</asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td></td>
        <td colspan="2">
            <asp:Button runat="server" ID="btnLogin" Text="Login" OnClick="btnLogin_Click" class="button button4" Height="31px" CssClass="auto-style2" Width="90px" />
            <asp:Button runat="server" ID="btnClear" Text="Clear" class="button button4" OnClick="btnClear_Click" Height="32px" CssClass="auto-style1" Width="108px" />
            <br />
        </td>
    </tr>
</table>

Namespaces

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Code

login1

protected void btnLogin_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    SqlDataAdapter sda = new SqlDataAdapter("Select * from emp_mast_TS where empid='" + txtusername.Text + "' and Password='" + txtpassword.Text + "'", con);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    if (dt.Rows.Count > 0)
    {
        // Setting Session.
        Session["empid"] = "Id: " + dt.Rows[0]["empid"].ToString();
        Session["firstname"] = "First Name: " + dt.Rows[0]["firstname"].ToString();
        Session["lastname"] = "Last Name: " + dt.Rows[0]["lastname"].ToString();
        Session["department"] = "Department: " + dt.Rows[0]["department"].ToString();

        if (ddltype.SelectedValue == "1")
        {
            Server.Transfer("Index.aspx");
        }
        if (ddltype.SelectedValue == "2")
        {
            Server.Transfer("Index1.aspx");
        }
        else if (ddltype.SelectedValue == "3")
        {
            Server.Transfer("Index2.aspx");
        }
    }
    else
    {
        Response.Write("Error in your input");
    }
}

protected void btnClear_Click(object sender, EventArgs e)
{
    txtusername.Text = "";
    txtusername.Text = "";
}

Index

protected void Page_Load(object sender, EventArgs e)
{
    string message = Session["empid"].ToString() + "<br/>";
    message += Session["firstname"].ToString() + "<br/>";
    message += Session["lastname"].ToString() + "<br/>";
    message += Session["department"].ToString();
    Response.Write(message);

}

VB.Net

    Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim sda As SqlDataAdapter = New SqlDataAdapter("Select * from emp_mast_TS where empid='" & txtusername.Text & "' and Password='" & txtpassword.Text & "'", con)
        Dim dt As DataTable = New DataTable()
        sda.Fill(dt)

        If dt.Rows.Count > 0 Then
            Session("empid") = "Id: " & dt.Rows(0)("empid").ToString()
            Session("firstname") = "First Name: " & dt.Rows(0)("firstname").ToString()
            Session("lastname") = "Last Name: " & dt.Rows(0)("lastname").ToString()
            Session("department") = "Department: " & dt.Rows(0)("department").ToString()

            If ddltype.SelectedValue = "1" Then
                Server.Transfer("Index.aspx")
            End If

            If ddltype.SelectedValue = "2" Then
                Server.Transfer("Index1.aspx")
            ElseIf ddltype.SelectedValue = "3" Then
                Server.Transfer("Index2.aspx")
            End If
        Else
            Response.Write("Error in your input")
        End If
    End Sub

    Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As EventArgs)
        txtusername.Text = ""
        txtusername.Text = ""
    End Sub

Index

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim message As String = Session("empid").ToString() + "<br/>"
    message += Session("firstname").ToString() + "<br/>"
    message += Session("lastname").ToString() + "<br/>"
    message += Session("department").ToString()
    Response.Write(message)
End Sub

Screenshot

[Solved] ASP.Net Error: There is already an open DataReader associated with this Command which must be closed first

Server Error in '/' Application.

There is already an open DataReader associated with this Command which must be closed first.

Company

Explore

Follow us

Contact Us