Problem with a SELECT Query

Question

Hello, I have a problem with a Query in a Nested GridView, I want to show Employees that takes the Session course, I'm using the Course ID like key, but when I have more of one same course name, only works the first Row of that course, and in that Row, appear all the Employees, regardless of whether that course has been taken in another session.

Result:

Expected result:

I using the ID Name, but if I use the Date too, desn't fix the issue.

C#:

    protected void Page_Load(object sender, EventArgs e)  
            {  
                if (!IsPostBack)  
                {  
                    BindGrid();  
                }  
            }  
    private void BindGrid(string sortExpression = null)  
            {  
                string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;  
                using (SqlConnection con = new SqlConnection(constr))  
                {  
                    using (SqlCommand cmd = new SqlCommand("SELECT distinct No_curso, Room, Idioma, Fecha_inicio, Fecha_final, Training, PMax from [Agregar_sesion]"))  
                    {  
                        using (SqlDataAdapter sda = new SqlDataAdapter())  
                        {  
                            cmd.Connection = con;  
                            sda.SelectCommand = cmd;  
                            using (DataTable dt = new DataTable())  
                            {  
                                sda.Fill(dt);  
                            }  
                        }   
                    }  
                }  
            }  
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)  
            {  
                if (e.Row.RowType == DataControlRowType.DataRow)  
                {  
                    SqlConnection con = new SqlConnection();  
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;  
                    con.Open();  
                    GridView gv = (GridView)e.Row.FindControl("GridView2") ;  
                    string curso = e.Row.Cells[1].Text.ToString();  
                    string fecha = e.Row.Cells[4].Text.ToString();  
                    SqlCommand cmd = new SqlCommand("SELECT distinct AgS.No_curso, AgS.Fecha_inicio, M.Num_empleado, M.Nombre,M.Leader From Agregar_sesion Ags, Maestro M WHERE  Ags.No_emp = M.Num_empleado and Ags.No_curso ='" + curso + "'", con);  
                    SqlDataAdapter da = new SqlDataAdapter(cmd);  
                    DataSet ds = new DataSet();  
                    da.Fill(ds);  
                    con.Close();  
                    gv.DataSource = ds;  
                    gv.DataBind();  
                }  
            }  
    protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)  
            {  
                GridView1.PageIndex = e.NewPageIndex;  
                this.BindGrid();  
            }

HTML:

SQL exec:

exec sp_addtype curso, 'varchar(80)'  
exec sp_addtype resumen, 'varchar(360)'

CREATE TABLES:

 Create Table Maestro(  
 ID int IDENTITY(1,1) PRIMARY KEY,  
 Leader curso null,  
 Num_empleado curso not null,   
 Nombre curso null)  
  
 create Table Agregar_sesion  
  (  Id int IDENTITY (1,1) Primary Key,  
 No_curso resumen null,  
 Room resumen null,  
 Idioma resumen null,  
 Fecha_inicio resumen null,  
 Fecha_final resumen null,  
 Training resumen null,  
 PMax resumen null,  
 No_emp curso null)

INSERT INTO:

INSERT INTO Maestro VALUES('AUSENSIO MORALES', '80134', 'ESTEBAN AMERICO ELIZONDO MARIN')  
INSERT INTO Maestro VALUES('AUSENSIO MORALES', '80519', 'JORGE ANTONIO RIVERA SAUCEDA')  
INSERT INTO Maestro VALUES('AUSENSIO MORALES', '81620', 'KAMILA YANETH GRAJEDA BERMUDEZ')  
  
  INSERT INTO Agregar_sesion VALUES('mxprueba', 'audiovisual', 'Spanish', '7/3/2021', '7/3/2021', 'David', '5', '80134')  
  INSERT INTO Agregar_sesion VALUES('mxprueba', 'audiovisual', 'Spanish', '7/3/2021', '7/3/2021', 'David', '5', '80519')  
  INSERT INTO Agregar_sesion VALUES('mxprueba', 'C', 'Spanish', '6/28/2021', '6/28/2021', 'Sergio', '2', '80134')  
  INSERT INTO Agregar_sesion VALUES('mxprueba', 'C', 'Spanish', '6/28/2021', '6/28/2021', 'Sergio', '2', '81620')

Answer

I don't understand whether this is an SQL question or a .NET question, but it looks more like the latter, so I leave to the .NET people to answer.

However, being an SQL Server person, I noticed this piece of code:

   SqlCommand cmd = new SqlCommand("SELECT distinct AgS.No_curso, AgS.Fecha_inicio, M.Num_empleado, M.Nombre,M.Leader From Agregar_sesion Ags, Maestro M WHERE  Ags.No_emp = M.Num_empleado and Ags.No_curso ='" + curso + "'", con);

This is a total no-no! Never must you interleave parameter values into SQL strings like that. You get all sorts of problems:

Opens for SQL injection.
Litters the cache in SQL Server.
Gives you a headache if there single quotes in the value.
Extra pain for date values.
Is more difficult to get right.
Opens for SQL injection. (Yeah, I've already said, but I say it again.)

The correct way is this:

   SqlCommand cmd = new SqlCommand("SELECT distinct AgS.No_curso, AgS.Fecha_inicio, M.Num_empleado, M.Nombre,M.Leader From Agregar_sesion Ags, Maestro M WHERE  Ags.No_emp = M.Num_empleado and Ags.No_curso =[@](/users/na/?userId=b780d6d2-4001-0003-0000-000000000000)", con);  
   cmd.Parameters.Add("[@](/users/na/?userId=b780d6d2-4001-0003-0000-000000000000), SqlDbType.VarChar, 80).Value = e.Row.Cells[1].Text.ToString();

Problem with a SELECT Query

1 answer