dropdownlist + SQL

[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.

[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 :)

[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.