Member Avatar for Nfurman

Hello. I am doing an exercise and faced an problem

my code is:

protected void ddlStudents_SelectedIndexChanged(object sender, EventArgs e)
    {
        
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand comm = new SqlCommand("GetFullStudentProfile",conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.CommandText = "GetFullStudentProfile";
        SqlDataReader reader;
        try
        {
            conn.Open();
            reader = comm.ExecuteReader();
            reader.Read();

            StringBuilder sb = new StringBuilder();
            sb.Append("Name: ");
            sb.Append(reader["StudentName"]);
            sb.Append(" ");
            sb.Append(reader["StudentFamilyName"]);
            sb.Append("<br>");
            sb.Append("Birthday: ");
            sb.Append(reader["Birthday"]);
            sb.Append("<br>");
            sb.Append("Class: ");
            sb.Append(reader["Classname"]);
            
            lbl.Text = sb.ToString();

            reader.Close();
        }
        catch (Exception ex)
        {
            lbl.Text = ex.Message;
        }

        finally
        {
            conn.Close();
        }
    }

SQL proc

create procedure [dbo].[GetFullStudentProfile2]
as
select StudentID,StudentName,StudentFamilyName,Birthday,cls.Classname
from dbo.Students
where studentId=ddlStudents.SelectedItem.Value
inner join
Classes as cls on cls.ClassId=dbo.Students.StudentId
GO

Problem:

How can I add to my query

ddlStudents.SelectedItemValue

?

Because it shows only the first position from DB.

  • 2 Contributors
  • 2 Replies
  • 133 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by Nfurman

Dani AI

Generated

Nice catch by and well done for getting it working. A few concise notes and checks that prevent the “only first row” or “no selected value” symptoms in ASP.NET WebForms + SQL setups:

  • Make sure the DropDownList actually posts back and the event fires: set AutoPostBack = true, wire SelectedIndexChanged, and bind the list only on first load (if (!IsPostBack)). Rebinding on every postback resets the selection to the first item.
  • Use the DropDownList SelectedValue (and parse it safely with int.TryParse) rather than referencing control state inside SQL. The stored procedure should accept an @studentId parameter and return exactly the row you want. Check the proc name you call matches the one deployed on the server.
  • Always check the reader before using values: test HasRows or use if (reader.Read()) so you don’t assume a row exists. Handle possible DBNull values when reading columns.

Example patterns to follow (not the same as earlier posts):

CREATE PROCEDURE GetFullStudentProfile
  @studentId INT
AS
SELECT s.StudentID, s.StudentName, s.StudentFamilyName, s.Birthday, c.Classname
FROM dbo.Students s
INNER JOIN dbo.Classes c ON s.ClassId = c.ClassId
WHERE s.StudentID = @studentId;
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("GetFullStudentProfile", conn))
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add(new SqlParameter("@studentId", SqlDbType.Int) { Value = studentId });
  conn.Open();
  using (var rdr = cmd.ExecuteReader())
  {
    if (rdr.Read()) { /* safely read columns, check for DBNull */ }
  }
}

Extra tips: prefer explicitly typed SqlParameter over AddWithValue to avoid type inference issues and parameter sniffing. Validate input, handle the “no rows” case gracefully, and log SQL errors to help debugging. For reference on parameters and parameter types, see the Microsoft SqlParameter docs: SqlParameter (Microsoft Docs).

Recommended Answers

All 2 Replies

Member Avatar for Akash Saikia

You can not write that directly into the query,instead use parameters in your Stored Procedure

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

This will be your altered stored procedure,i have declared @studentId as a parmenter in int datatype,hoping that student id might be in numeric or int format in your database.

Now change your C# code in this way.

protected void ddlStudents_SelectedIndexChanged(object sender, EventArgs e)
    {
        
        SqlConnection conn = new SqlConnection(connection);
        SqlCommand comm = new SqlCommand("GetFullStudentProfile",conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters .AddWithValue ("@studentId",Convert.ToInt32(ddlStudents.SelectedItem.Value));
        comm.CommandText = "GetFullStudentProfile";
        SqlDataReader reader;
        try
        {
            conn.Open();
            reader = comm.ExecuteReader();
            reader.Read();

            StringBuilder sb = new StringBuilder();
            sb.Append("Name: ");
            sb.Append(reader["StudentName"]);
            sb.Append(" ");
            sb.Append(reader["StudentFamilyName"]);
            sb.Append("<br>");
            sb.Append("Birthday: ");
            sb.Append(reader["Birthday"]);
            sb.Append("<br>");
            sb.Append("Class: ");
            sb.Append(reader["Classname"]);
            
            lbl.Text = sb.ToString();

            reader.Close();
        }
        catch (Exception ex)
        {
            lbl.Text = ex.Message;
        }

        finally
        {
            conn.Close();
        }
    }

So the parameter is passed in this way through the code,
1) the parameter name
2) with it's value in the type mentioned in the stored procedure
As i have declared in int,so i converted the argument in int32 format.

Done :)

Member Avatar for Nfurman

Thank you Akash Sir,

Your code has errors, but I fixed them. The main thing is that you showed me the right way to solve this problem. Now everything works fine. Thank you.

Edited by Nfurman because: n/a
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.