question

GonzalezLunaEstebanEliud-3790 avatar image
0 Votes"
GonzalezLunaEstebanEliud-3790 asked YijingSun-MSFT commented

Problem with a SELECT Query

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:
113934-captura.png

Expected result:
113960-captura1.png

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:
113935-tb.png

113991-tb1.png

113936-tb2.png


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')







sql-server-generaldotnet-csharpdotnet-aspnet-general
captura.png (103.4 KiB)
captura1.png (23.1 KiB)
tb.png (106.0 KiB)
tb1.png (76.2 KiB)
tb2.png (79.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GonzalezLunaEstebanEliud-3790,

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.

What's your meaning? Do you show all records according to CourseID? As far as I think,your problem is sql query. You need to check courseID and room.
Best regards,
Yijing Sun
0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog edited

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 =@curso", con);
cmd.Parameters.Add("@curso, SqlDbType.VarChar, 80).Value = e.Row.Cells[1].Text.ToString();
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.