DropDownList problem

Question

Nfurman 0 Junior Poster in Training

15 Years Ago

Hello again,

I have a problem with dropdownlist.

DropDownList:

<asp:DropDownList id="ddlStudents" runat="server" CssClass="textbox" OnSelectedIndexChanged="ddlStudents_SelectedIndexChanged" AutoPostBack="True" EnableViewState="False">
      <asp:ListItem Value="0">Choose</asp:ListItem>
</asp:DropDownList>

Method which fills the ddl with data from DB

protected void GetStudents()
    {
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("GetFullStudentProfile", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetFullStudentProfile";
        SqlDataReader reader;
        {
            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                reader.Read();
                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Text = reader["StudentName"].ToString() + " " + reader["StudentFamilyname"].ToString();
                    item.Value = reader["StudentID"].ToString();
                    ddlStudents.Items.Add(item);
                }
            }
            catch (Exception ex)
            {
                lbl.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }
    }

Method that suppose to fill lbls with retrieved data when I choose some item from this ddl.

protected void ddlStudents_SelectedIndexChanged(object sender, EventArgs e)
    {
        lbl.Text = "";

        tbName.Visible = false;
        tbFamilyname.Visible = false;
        ddlClasses.Visible = false;
        lblBirth.Visible = true;
        tbYear.Visible = false;
        ddlDay.Visible = false;
        ddlMonth.Visible = false;
        btnSubmit.Visible = false;
        lblDay.Visible = false;
        lblMonth.Visible = false;
        lblYear.Visible = false;

        lblClass.Visible = true;
        lblFamilyName.Visible = true;
        lblName.Visible = true;
        
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("ThisOne", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ThisOne";
        cmd.Parameters.AddWithValue("@StudentId", Convert.ToInt32(ddlStudents.SelectedItem.Value));
        SqlDataReader reader;
        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            reader.Read();

            //Filling the controls

            lblClass.Text= reader["Classname"].ToString();
            lblBirth.Text = reader["Birthday"].ToString();
            lblName.Text = reader["StudentName"].ToString();
            lblFamilyName.Text = reader["StudentFamilyName"].ToString();
            reader.Close();
        }
        catch (Exception ex)
        {
            lbl.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }

    protected void PopulateClasses()
    {
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("GetClassesToDDL",conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetClassesToDDL";
        SqlDataReader reader;
        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            reader.Read();
            while (reader.Read())
            {
                ListItem items = new ListItem();
                items.Text = reader["ClassName"].ToString();
                items.Value = reader["ClassId"].ToString();
                ddlClasses.Items.Add(items);
            }
        }
        catch (Exception ex)
        {
            lbl.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }

SQL Procs

create procedure [dbo].[GetFullStudentProfile]
as
select StudentID,StudentName,StudentFamilyName,Birthday,cls.Classname
from dbo.Students
inner join
Classes as cls on cls.ClassId=dbo.Students.StudentId
GO

create procedure [dbo].[ThisOne]
(@StudentId int)
as
select StudentID,StudentName,StudentFamilyName,Birthday,cls.Classname
from dbo.Students
inner join
Classes as cls on cls.ClassId=dbo.Students.StudentId
where StudentID=@studentId
GO

Problems:

1. ddl doesn't show all students from table
2. When I do insert, and it successful, ddl doesn't show new student currently inserted.
3. When I choose a student in ddl. I get "Invalid attempt to read when no data is present." error.

where's the bug and how to fix it.

P.S The page is AJAXed.

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
        <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
        </asp:contentplaceholder>
        </ContentTemplate>
        </asp:UpdatePanel>

in masterpage.

please help thank you.

.net microsoft:sql-server

3 Contributors
5 Replies
197 Views
4 Days Discussion Span
Latest Post 15 Years Ago Latest Post by Nfurman

Dani AI

Generated 7 Months Ago

Brief summary of what actually went wrong in this thread and what to check before changing more code. Several small problems combined to cause each symptom: a wrong JOIN in the stored procedures (explains the missing rows), unsafe DataReader usage that skips or assumes rows exist (explains the "Invalid attempt to read..." exception), and ViewState/rebinding issues with a dynamically populated DropDownList inside an UpdatePanel (explains why newly inserted students did not appear). was right to call out the DataReader misuse, and correctly discovered the ViewState interaction — both are parts of the full picture.

The stored-proc bug is critical: the JOIN in both procedures appears to match Classes.ClassId to Students.StudentId. That will filter results to only rows where those IDs happen to match. Verify the FK between Students and Classes (typically Students.ClassId = Classes.ClassId) and fix the JOIN. Run the SELECT from each proc directly in SQL Server to confirm row counts and sample values before wiring results into the UI.

For the reader logic: do not call Read() once and then loop with while(Read()) — that pattern skips the first row. Use a single while(reader.Read()) to enumerate rows, or use if(reader.Read()) when you expect exactly one row, and always check HasRows or the boolean result of Read() before accessing columns. Always close/dispose the reader and connection (prefer using blocks) so resources are released and subsequent commands behave predictably.

For the DropDownList lifecycle: either enable ViewState for the control or recreate/bind its items on every postback before the event handling that needs them. A common pattern is bind the list in Page_Load only when !IsPostBack, and after an insert call the same bind method so the new item appears immediately. When using UpdatePanel, ensure the panel is updated by the insert action (or call UpdatePanel.Update()). Finally, guard your SelectedIndexChanged handler so it only reads the DB if a row actually exists and the selected value is valid. Quick checklist:

Fix the JOIN in the stored procedures and re-test the query directly.
Replace the reader.Read()/while(Read()) pattern with proper checks and always close the reader.
Either enable ViewState for the DDL or rebind it after inserts; ensure UpdatePanel updates if needed.
Add defensive checks in SelectedIndexChanged (verify Read returned true) to avoid the exception.

All 5 Replies

jbisono 51 Posting Pro in Training

15 Years Ago

There are few thing that i should revise here.
1. when you make a instance of the SqlCommand with the Name of the stored procedure and the SqlConnection you do not have to set the CommandText property of the SqlCommand, you can delete that line.

2. I see you execute the method Read() of the DataReader with out the while loop, you dont have to do that, just do this while(Reader.Read()){code here}

3. When you finish reading thru the DataReader you have to execute the method Close of the DataReader.

first fix these things try to run and let me know what do you get.

egrullard 0 Newbie Poster

15 Years Ago

Hello again,

I have a problem with dropdownlist.

DropDownList:

<asp:DropDownList id="ddlStudents" runat="server" CssClass="textbox" OnSelectedIndexChanged="ddlStudents_SelectedIndexChanged" AutoPostBack="True" EnableViewState="False">
      <asp:ListItem Value="0">Choose</asp:ListItem>
</asp:DropDownList>

Method which fills the ddl with data from DB

protected void GetStudents()
    {
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("GetFullStudentProfile", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetFullStudentProfile";
        SqlDataReader reader;
        {
            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                reader.Read();
                while (reader.Read())
                {
                    ListItem item = new ListItem();
                    item.Text = reader["StudentName"].ToString() + " " + reader["StudentFamilyname"].ToString();
                    item.Value = reader["StudentID"].ToString();
                    ddlStudents.Items.Add(item);
                }
            }
            catch (Exception ex)
            {
                lbl.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
        }
    }

Method that suppose to fill lbls with retrieved data when I choose some item from this ddl.

protected void ddlStudents_SelectedIndexChanged(object sender, EventArgs e)
    {
        lbl.Text = "";

        tbName.Visible = false;
        tbFamilyname.Visible = false;
        ddlClasses.Visible = false;
        lblBirth.Visible = true;
        tbYear.Visible = false;
        ddlDay.Visible = false;
        ddlMonth.Visible = false;
        btnSubmit.Visible = false;
        lblDay.Visible = false;
        lblMonth.Visible = false;
        lblYear.Visible = false;

        lblClass.Visible = true;
        lblFamilyName.Visible = true;
        lblName.Visible = true;
        
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("ThisOne", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ThisOne";
        cmd.Parameters.AddWithValue("@StudentId", Convert.ToInt32(ddlStudents.SelectedItem.Value));
        SqlDataReader reader;
        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            reader.Read();

            //Filling the controls

            lblClass.Text= reader["Classname"].ToString();
            lblBirth.Text = reader["Birthday"].ToString();
            lblName.Text = reader["StudentName"].ToString();
            lblFamilyName.Text = reader["StudentFamilyName"].ToString();
            reader.Close();
        }
        catch (Exception ex)
        {
            lbl.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }

    protected void PopulateClasses()
    {
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand cmd = new SqlCommand("GetClassesToDDL",conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetClassesToDDL";
        SqlDataReader reader;
        try
        {
            conn.Open();
            reader = cmd.ExecuteReader();
            reader.Read();
            while (reader.Read())
            {
                ListItem items = new ListItem();
                items.Text = reader["ClassName"].ToString();
                items.Value = reader["ClassId"].ToString();
                ddlClasses.Items.Add(items);
            }
        }
        catch (Exception ex)
        {
            lbl.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }

SQL Procs

create procedure [dbo].[GetFullStudentProfile]
as
select StudentID,StudentName,StudentFamilyName,Birthday,cls.Classname
from dbo.Students
inner join
Classes as cls on cls.ClassId=dbo.Students.StudentId
GO

create procedure [dbo].[ThisOne]
(@StudentId int)
as
select StudentID,StudentName,StudentFamilyName,Birthday,cls.Classname
from dbo.Students
inner join
Classes as cls on cls.ClassId=dbo.Students.StudentId
where StudentID=@studentId
GO

Problems:

1. ddl doesn't show all students from table
2. When I do insert, and it successful, ddl doesn't show new student currently inserted.
3. When I choose a student in ddl. I get "Invalid attempt to read when no data is present." error.

where's the bug and how to fix it.

P.S The page is AJAXed.

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
        <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
        </asp:contentplaceholder>
        </ContentTemplate>
        </asp:UpdatePanel>

in masterpage.

please help thank you.

Hello,

>Hello again,
>
>I have a problem with dropdownlist.
>
>DropDownList:

But you did not say what the error is happening to you

greeting

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.

Nfurman 0 Junior Poster in Training · Answer 1 · 2011-04-15T01:58:18+00:00

There are few thing that i should revise here.
1. when you make a instance of the SqlCommand with the Name of the stored procedure and the SqlConnection you do not have to set the CommandText property of the SqlCommand, you can delete that line.
2. I see you execute the method Read() of the DataReader with out the while loop, you dont have to do that, just do this while(Reader.Read()){code here}
3. When you finish reading thru the DataReader you have to execute the method Close of the DataReader.
first fix these things try to run and let me know what do you get.

Thank you Jose for pointing me to mistakes I made. I fixed them, but it didn't improve the situation with ddlStudents_SelectedIndexChanged method. It still refuses to output data into Labels.

Nfurman 0 Junior Poster in Training · Answer 2 · 2011-04-15T01:59:45+00:00

Hello,
>Hello again,
>
>I have a problem with dropdownlist.
>
>DropDownList:
But you did not say what the error is happening to you
greeting

I said even 3 problems:

1. ddl doesn't show all students from table
2. When I do insert, and it successful, ddl doesn't show new student currently inserted.
3. When I choose a student in ddl. I get "Invalid attempt to read when no data is present." error.

Nfurman 0 Junior Poster in Training · Answer 3 · 2011-04-15T02:54:00+00:00

Ok. Now ddlStudents_SelectedIndexChanged works. The problem was EnableViewState="true" assigned to ddl control. Don't remember why I did add this property to control.

Thanks to Jose who did help me much!

DropDownList problem

Dani AI

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers