I'm making a module to save an array in an SQL database. For example, I want to save (889,4,01/12/2021),(889,4,02/12/2021), and (889,4,03/12/2021).

I'm using a gridview where I obtain the first value (889). Then I get the date with a textbox and I run a query to return the dates in rows and are stored in a gridview.

I'm trying to choose the 2nd gridview value (date) with GridView2.Rows[0].Cells[1].Text, but is outside of the range of valid values.

As this is an array, I save all the SQL sentences in a textbox an later I execute, so that is my code

string[,] arreglo = new string[GridView1.Rows.Count, 7];
foreach (GridViewRow row in GridView1.Rows)
{
    CheckBox chkbox1 = (CheckBox)row.FindControl("chkActive");
    if (chkbox1.Checked)
    {
        arreglo[row.RowIndex, 0] = GridView1.Rows[row.RowIndex].Cells[1].Text;
        string[,] array = new string[GridView2.Rows.Count, 2];
        foreach (GridViewRow col in GridView2.Rows)
            array[col.RowIndex, 0] = GridView2.Rows[col.RowIndex].Cells[1].Text;
        txtInsert.Text = txtInsert.Text   "insert into  T_USUARIO_dETALLE(id_usuario,campana,fecha,fecha_carga,id_superv,estado_dotacion) values ('"   arreglo[row.RowIndex, 0].ToString()   "', '"   lblcampana.Text   "','"  GridView2.Rows[0].Cells[1].Text    "','"   LBLSUPERV.Text   "','"   ddlEstado.SelectedValue   "')";
    }
}

Thanks for the help!!!

CodePudding user response：

Ok, so this is one of the most classic setups in db land!

We have some parent records, and for each record, we need to display (and edit) child rows.

Now there are more UI choices for this then flavors of ice-cream.

And this suggests we need to "nest" the master records (say in a grid) to display of the child reocrds (our 2nd grid).

Well, it turns out that grids REALLY don't nest well.

So, for parent record(s), lets use a ListView - they work much better.

Ok, so we can fire up the wizards - build the LV, and THEN GO NUCLEAR weapons here and delete blow out ALL the templates. And while we add this, delete the extra nested table. I count < 2 minutes of time.

So, we now have this simple LV.

    <asp:ListView ID="ListView1" runat="server" DataKeyNames="ID" >
       <ItemTemplate>
          <tr>
            <td><asp:Button ID="cmdView" runat="server" Text=" " /></td>
            <td><asp:Label ID="HotelNameLabel" runat="server" Text='<%# Eval("HotelName") %>' /></td>
            <td><asp:Label ID="CityLabel" runat="server" Text='<%# Eval("City") %>' /></td>
            <td><asp:Label ID="ProvinceLabel" runat="server" Text='<%# Eval("Province") %>' /></td>
            <td><asp:Label ID="DescriptionLabel" runat="server" Text='<%# Eval("Description") %>' /></td>
          </tr>

           </ItemTemplate>
       <LayoutTemplate>
        <table id="itemPlaceholderContainer" runat="server" class = "table table-hover" >
            <tr runat="server" style="">
                <th runat="server">View</th>
                <th runat="server">HotelName</th>
                <th runat="server">City</th>
                <th runat="server">Province</th>
                <th runat="server">Description</th>
            </tr>
            <tr id="itemPlaceholder" runat="server">
            </tr>
         </table>
       </LayoutTemplate>
    </asp:ListView>

 </div>

And our code to fill is this:

   protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadMainGrid();
    }

    void LoadMainGrid()
    {
        string strSQL = "SELECT * FROM tblHotels ORDER BY HotelName";
        ListView1.DataSource = MyRst(strSQL);
        ListView1.DataBind();
    }

And we now have this:

Ok, so now we need the child grid.

Then we MOVE this to be child grid into the above LV.

So, now we have this markup:

            <td><asp:Label ID="DescriptionLabel" runat="server" Text='<%# Eval("Description") %>' /></td>
          </tr>

<tr>
<td colspan="5">
    <div id="MyGrid" runat="server" style="display:none;margin-left:1%">

    <div>
    <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="ID" CssClass="table table-hover table-responsive borderhide" >
        <Columns>
            <asp:TemplateField HeaderText="First Name">
                <ItemTemplate>
                    <asp:TextBox ID="FirstName" runat="server" Text='<%# Eval("FirstName") %>'  />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Last Name">
                <ItemTemplate>
                    <asp:TextBox ID="LastName" runat="server" Text='<%# Eval("LastName") %>'  />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="City">
                <ItemTemplate>
                    <asp:TextBox ID="City" runat="server" Text='<%# Eval("City") %>'  />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Start date">
                <ItemTemplate>
                    <asp:TextBox ID="dtStart" runat="server" Text='<%# Eval("dtStart", "{0:yyyy-MM-ddTHH:mm}") %>'  TextMode="DateTimeLocal" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="End date">
                <ItemTemplate>
                    <asp:TextBox ID="dtEnd" runat="server" Text='<%# Eval("dtEnd","{0:yyyy-MM-ddTHH:mm}") %>' TextMode="DateTimeLocal" />
                </ItemTemplate>
            </asp:TemplateField>
        
           
        </Columns>
        
    </asp:GridView>
    </div>

        <div style="float:right;margin-right:-10px">
        <asp:Button ID="cmdSave" runat="server" Text="Save Edits" CssClass="btn" OnClick="cmdSave_Click"  />
        <asp:Button ID="cmdAdd" runat="server" Text="Add New Row" CssClass="btn" style="margin-left:5px" OnClick="cmdAdd_Click" />
        </div>
    </div>
</td>

So we dropped in above right after the markup for the lv columns.

Now, all we have to do is wire up the the " " button to expand:

That was the first row button in the LV

 <td><asp:Button ID="cmdView" runat="server" Text=" "  OnClick="cmdView_Click"/></td>

And the code is this:

   protected void cmdView_Click(object sender, EventArgs e)
    {
        Button cmd = (Button)sender;
        ListViewDataItem gVR = (ListViewDataItem)cmd.NamingContainer;
        GridView gChild = (GridView)gVR.FindControl("GridView2");   // pluck out the grid for this row
        HtmlGenericControl MyDiv = (HtmlGenericControl)gVR.FindControl("MyGrid");

        //if (gChild.Style["display"] == "normal")
        if (MyDiv.Style["display"] == "normal")
        {
            // if grid is already display, then hide it, and exit
            MyDiv.Style["display"] = "none";
            return;
        }
        MyDiv.Style["display"] = "normal";
        int HotelPK = (int)ListView1.DataKeys[gVR.DataItemIndex]["ID"];

        // only re-load if never loaded
        if (gChild.Rows.Count == 0)
        {
            gChild.DataSource = MyRst("SELECT * from People where hotel_id = "   HotelPK);
            gChild.DataBind();
        }
    }

So, now we have this:

Ok, so now all we have to do is create a "save" button that saves any changes we made to this GV.

that is quite easy, and so we drop in a save button like this:

So, right below our grid, we drop in this:

    </asp:GridView>
    </div>

        <div style="float:right;margin-right:-10px">
        <asp:Button ID="cmdSave" runat="server" Text="Save Edits" CssClass="btn" />
        <asp:Button ID="cmdAdd" runat="server" Text="Add New Row" CssClass="btn" style="margin-left:5px" />
        </div>
    </div>

And we now have this:

Ok, so for the button, we add a click event:

(just type in onclick= and intelsense will offer to create the click event, looks like nothing occurred, but going to code behind, you will see the even stub.

And we use this code:

    protected void cmdSave_Click(object sender, EventArgs e)
    {
        Button cmdSave = (Button)sender;
        ListViewDataItem lvRow = (ListViewDataItem)cmdSave.NamingContainer;

        GridView MyGrid = (GridView)lvRow.FindControl("GridView2");
        int HotelID = (int)ListView1.DataKeys[lvRow.DisplayIndex]["ID"];
        // send grid to table
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = "SELECT * from People WHERE hotel_id = "   HotelID;
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
                SqlCommandBuilder daU = new SqlCommandBuilder(da);

                DataTable rstGrid = new DataTable();
                rstGrid.Load(cmdSQL.ExecuteReader());
                foreach (GridViewRow gRow in MyGrid.Rows)
                {
                    DataRow OneRow = rstGrid.Rows[gRow.RowIndex];
                    OneRow["FirstName"] = ((TextBox)gRow.FindControl("FirstName")).Text;
                    OneRow["LastName"] = ((TextBox)gRow.FindControl("LastName")).Text;
                    OneRow["City"] = ((TextBox)gRow.FindControl("City")).Text;
                    string myDate = ((TextBox)gRow.FindControl("dtStart")).Text;
                    if (myDate == "")
                        OneRow["dtStart"] = DBNull.Value;
                    else
                        OneRow["dtStart"]= DateTime.ParseExact(myDate, "yyyy-MM-ddTHH:mm", null);

                    myDate = ((TextBox)gRow.FindControl("dtEnd")).Text;
                    if (myDate == "")
                        OneRow["dtEnd"] = DBNull.Value;
                    else
                        OneRow["dtEnd"]  = DateTime.ParseExact(myDate, "yyyy-MM-ddTHH:mm", null);
                }
                da.Update(rstGrid);
            }
        }            
    }

Ok, we might as well wire up the "new row button".

so for that code we have this:

    protected void cmdAdd_Click(object sender, EventArgs e)
    {
        Button cmdNew = (Button)sender;
        ListViewDataItem lvRow = (ListViewDataItem)cmdNew.NamingContainer;

        GridView MyGrid = (GridView)lvRow.FindControl("GridView2");
        int HotelID = (int)ListView1.DataKeys[lvRow.DisplayIndex]["ID"];
        string strSQL = "INSERT INTO People (Hotel_ID) VALUES("   HotelID   ")";
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                cmdSQL.ExecuteNonQuery();
            }

            DataTable rstGrid = MyRst("SELECT * from People where Hotel_ID = "   HotelID);
            MyGrid.DataSource = rstGrid;
            MyGrid.DataBind();
        }
    }

I also have a helper routine - its a global function, and that just returns a data table for a given sql string. I did use that a few times in above.

This routine:

    public DataTable MyRst(string strSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

Edit/Upate (making the UI better):

One issue? If a user hits add new row, they will no doubt start typing, and then VERY likly hit add new row again (but with above, they MUST hit save data first).

so, I suggest a SAVE data before adding a new row.

This forces us to break out that longer routine anyway.

So we add a "Grid to Table", and then a "save table" to database. Breaking this out into two steps not only fixes the above "bug", or "issue", but it actually somewhat less code.

So, I suggest a update to these 3 (well 4) routines, and they are all shorter, and should used.

So, the Add row, save edits, and MyRst have all been udpated.

I suggest this code and thus if a user hits add-row, they will NOT lose any existing edits which I dare say will VERY likly occur.

hence, I suggest this code for the Save and add rows buttons

   protected void cmdSave_Click(object sender, EventArgs e)
    {
        Button cmdSave = (Button)sender;
        ListViewDataItem lvRow = (ListViewDataItem)cmdSave.NamingContainer;

        GridView MyGrid = (GridView)lvRow.FindControl("GridView2");
        int HotelID = (int)ListView1.DataKeys[lvRow.DisplayIndex]["ID"];
        DataTable rstGrid = MyRst("SELECT * FROM People WHERE Hotel_ID = "   HotelID);
        // send grid to table
        GridToTable(MyGrid, rstGrid);
        // save to database
        SaveGrid(MyGrid, rstGrid);
    }

    protected void cmdAdd_Click(object sender, EventArgs e)
    {
        Button cmdNew = (Button)sender;
        ListViewDataItem lvRow = (ListViewDataItem)cmdNew.NamingContainer;

        GridView MyGrid = (GridView)lvRow.FindControl("GridView2");
        int HotelID = (int)ListView1.DataKeys[lvRow.DisplayIndex]["ID"];
        DataTable rstGrid = MyRst("SELECT * FROM People where Hotel_ID = "   HotelID);
        // send any data edits from Grid to table
        GridToTable(MyGrid, rstGrid);
        // add new row
        DataRow MyNewRow = rstGrid.NewRow();
        MyNewRow["Hotel_ID"] = HotelID;
        rstGrid.Rows.Add(MyNewRow); 
        
        MyGrid.DataSource = rstGrid;
        MyGrid.DataBind();            
        // save data (must do AFTER grid bind - new row!!!)
        SaveGrid(MyGrid, rstGrid);
    }

    void SaveGrid(GridView MyGrid, DataTable rstGrid)
    {
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(rstGrid.TableName, conn))
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
                SqlCommandBuilder daU = new SqlCommandBuilder(da);
                da.Update(rstGrid);
            }
        }
    }

    void GridToTable(GridView MyGrid, DataTable rstGrid)
    {
        foreach (GridViewRow gRow in MyGrid.Rows)
        {
            DataRow OneRow = rstGrid.Rows[gRow.RowIndex];
            OneRow["FirstName"] = ((TextBox)gRow.FindControl("FirstName")).Text;
            OneRow["LastName"] = ((TextBox)gRow.FindControl("LastName")).Text;
            OneRow["City"] = ((TextBox)gRow.FindControl("City")).Text;
            string myDate = ((TextBox)gRow.FindControl("dtStart")).Text;
            if (myDate == "")
                OneRow["dtStart"] = DBNull.Value;
            else
                OneRow["dtStart"] = DateTime.ParseExact(myDate, "yyyy-MM-ddTHH:mm", null);

            myDate = ((TextBox)gRow.FindControl("dtEnd")).Text;
            if (myDate == "")
                OneRow["dtEnd"] = DBNull.Value;
            else
                OneRow["dtEnd"] = DateTime.ParseExact(myDate, "yyyy-MM-ddTHH:mm", null);
        }
    }

    public DataTable MyRst(string strSQL)
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
                rstData.TableName = strSQL;
            }
        }
        return rstData;
    }