Context

All rows in a gridview have a sequence value (it is a type double, ex: 2.4). The user updates this sequence number by checking the row's checkbox and using a stored procedure that produces the next available sequence (ex: 2.4 -> 2.5) on button press. The stored procedure puts the next available sequence in a TextBox for the user view as they update.

Question

Would it be possible to increment the value in the SQL statement by 0.1 for each row that is selected?

Ex: 4 rows are selected with the starting sequence value of 2.4, so on button press the stored procedure would produce 2.5 in the TextBox and within the foreach loop that looks for checked rows, it will increment 2.5 by 0.1 each cycle. So the rows would have their values be updated to "2.5, 2.6, 2.7, 2.8" respectfully.

C# code file:

    protected void SequenceUpdate(object sender, EventArgs e)
    {
        string strSQL = "SELECT data1, data2, seq FROM Table1 "  
                        "UPDATE Table1 SET seq = '"   textBox.Text   "'"  
                        "WHERE Table1.id = @ID";

        MultipleUpdate(Gridview1, strSQL);
    }

    void MultipleUpdate(GridView gv, string strSQL)
    {
        double Seq;

        SqlConnection sqlconn = new SqlConnection(cs);
        SqlCommand sqlcomm = new SqlCommand("dbo.seq_inc", sqlconn);
        sqlcomm.CommandType = CommandType.StoredProcedure;
        sqlcomm.Parameters.AddWithValue("@date", txtDate.Text);
        sqlcomm.Parameters.AddWithValue("@seq", txtSEQ.Text);
        sqlcomm.CommandType = System.Data.CommandType.StoredProcedure;

        sqlconn.Open();
        SqlDataReader dr = sqlcomm.ExecuteReader();
        dr.Read();

        Seq = Convert.ToDouble(dr["New_Seq"]);

        foreach (GridViewRow OneRow in gv.Rows)
        {
            CheckBox ckBox = OneRow.FindControl("chkSel") as CheckBox;
            if (ckBox.Checked)
            {
                textBox.Text = Seq.ToString("0.0");

                int PKID = (int)gv.DataKeys[OneRow.RowIndex]["ID"];
                SqlCommand cmdSQL = new SqlCommand(strSQL);
                cmdSQL.Parameters.Add("@ID", SqlDbType.Int).Value = PKID;
                SqlRun(cmdSQL);

                Seq = Convert.ToDouble(Seq   0.1);
            }
        }

        sqlconn.Close();
    }

Currently my code does increment the TextBox value according to how many rows are checked, but each row is still only updated by the initial value.

(So if I select four rows, the TextBox Value will be 2.8 but each row will have 2.5 as their sequence)

CodePudding user response：

I tend to think of the GV or whatever as UI display system. but, to update data, then we update the data, and if we happen to feel like displaying some information to the user? That's a separate issue!!

this does somewhat suggest that we have to re-bind the grid. But then again, that's ok, since we probably want to clear those check boxes anyway, right?

Hum, let me find a table --

Ok, this markup:

            <asp:Label ID="Label1" runat="server" Text="Select Date" Font-Size="Medium"></asp:Label>
            <asp:TextBox ID="txtDate" runat="server" TextMode="Date"
                style="margin-left:10px"
                ></asp:TextBox>

            <asp:Label ID="Label2" runat="server" Text="Start Sequence#" Font-Size="Medium"
                style="margin-left:20px" >

            </asp:Label>
            <asp:TextBox ID="txtStartSeq" runat="server"
                style="margin-left:10px;width:60px" >
            </asp:TextBox>
            <asp:Button ID="cmdUpdate" runat="server" Text="UPdate Selected"
                CssClass="btn" style="margin-left:25px" OnClick="cmdUpdate_Click" />

            <br />
            <br />

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
             CssClass="table table-hover" Width="50%"
                DataKeyNames="ID">
            <Columns>
                <asp:BoundField DataField="FirstName"   HeaderText="FirstName"   />
                <asp:BoundField DataField="LastName"    HeaderText="LastName"    />
                <asp:BoundField DataField="City"        HeaderText="City"        />
                <asp:BoundField DataField="HotelName"   HeaderText="HotelName"   />
                <asp:BoundField DataField="Description" HeaderText="Description" />
                <asp:BoundField DataField="MyDate"      HeaderText="My Date" ItemStyle-Width="95px" DataFormatString="{0:d}" />
                <asp:BoundField DataField="Sequence"    HeaderText="Seq"    />
                <asp:TemplateField HeaderText="Select" ItemStyle-HorizontalAlign="Center">
                    <ItemTemplate>
                        <asp:CheckBox ID="chkSel" runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

And code to load:

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

    void LoadGrid()
    {
        string strSQL = @"SELECT * FROM tblHotelsC ORDER BY HotelName";
        DataTable rstData = General.MyRst(strSQL);
        GridView1.DataSource = rstData;
        GridView1.DataBind();
    }

And we now have this:

So I set the date box, enter starting seq, and then selected some rows.

Code behind button click is this:

    protected void cmdUpdate_Click(object sender, EventArgs e)
    {
        double SeqStart = double.Parse(txtStartSeq.Text);
        DateTime dtDate = DateTime.Parse(txtDate.Text);

        foreach (GridViewRow gRow in GridView1.Rows)
        {
            CheckBox ckBox = (CheckBox)gRow.FindControl("chkSel");
            if (ckBox.Checked)
            {
                int PK = (int)GridView1.DataKeys[gRow.RowIndex]["ID"];
                MyUpdate(PK, dtDate, SeqStart);
                SeqStart  = 0.1;
            }
        }
        LoadGrid();
    }

    void MyUpdate(int PK, DateTime dtDate, double SeqNum)
    {
        // update the data
        string strSQL =
            @"UPDATE tblHotelsC SET MyDate = @dtDate, Sequence = @Seq 
            WHERE ID = @ID";
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                cmdSQL.Parameters.Add("@dtDate", SqlDbType.Date).Value = dtDate;
                cmdSQL.Parameters.Add("@Seq",SqlDbType.Float).Value = SeqNum;
                cmdSQL.Parameters.Add("@ID", SqlDbType.Int).Value = PK;
                cmdSQL.ExecuteNonQuery();
            }
        }
    }

And now we see this: