locked
Updating data in a SQL Table using data from Gridview RRS feed

  • Question

  • User980347791 posted

    Good day, this is my first time posting here so apologies in advance if I do something wrong.

    I've been stucked for days with the next thing. I'm bringing data from a SQL Table and showing it in a Gridview. What I need now is to update a field that I left empty in the table using a textbox that is inside the cells of a column, it's like sharing messages. I've been using examples from internet but nothing seems to work. 

    This is the code of my gridview

    <asp:GridView ID="GridViewS" runat="server" CssClass="mydatagrid simpleshape1" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" autogeneratecolumns="False" RowStyle-CssClass="rows">
    <Columns>
    <asp:BoundField DataField="MessageID" HeaderText="ID" SortExpression="DateField"/>
    <asp:BoundField DataField="Number" HeaderText="Number" SortExpression="DateField" />
    <asp:BoundField DataField="Code" HeaderText="Code" SortExpression="DateField" />
    <asp:TemplateField>
    <ItemTemplate>
    <asp:TextBox runat="server" ID="bodymes"></asp:TextBox> <asp:Button runat="server" ID="bsend" OnClick="bsend_Click" text="Answer" />
    </ItemTemplate>
    </asp:TemplateField>

    </Columns>

    </asp:GridView>

    I'm completely lost on what to do in the "bsend_Click" part. I just know that when the user clicks the button the data from the textbox should be save in the SQL table row where the MessageID matches, but I havent figure it out how to obtain those two values. 

    Thank you in advance!

    Monday, August 14, 2017 3:33 PM

Answers

  • User2103319870 posted

    I think the problem lies with accessing the data key from Gridview.

    You can try with below code

      protected void GridViewS_RowUpdating(object sender, GridViewUpdateEventArgs e)
            {
                int row = e.RowIndex;
                //Find the control and get the text from texbox control
                string txtAnswer = ((TextBox)GridViewS.Rows[row].FindControl("bodymes")).Text;
                //Get the message from datakey
                int id = Int32.Parse(GridViewS.DataKeys[e.RowIndex].Value.ToString());
                //Your code here
    
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 16, 2017 1:04 AM

All replies

  • User-359936451 posted

    you do not indicate which technology you are using. But in Web Forms, when you select the datasource, you can tell the Gridview control to create the Edit, Insert and Delete commands. When you do this, they are build for you automatically in the Connection Sting. When you view the control it will display the buttons for Edit and Delete, and when you click Edit it will display the textbox for each database value. When you enter edit mode, the Update button will appear and when you enter text you can click Update to post back to the database.

    Check out these awesome videos, search for Gridview...

    http://www.misfitgeek.com/asp-net-data-access-tutorials-videos-114/

    Monday, August 14, 2017 6:40 PM
  • User980347791 posted

    Thank you, I've finally been able to insert data in the Table, sadly I still cannot pass the data from the TextBox, and cannot store it in the specified ID. 

    New GridView

    <asp:GridView ID="GridViewS" OnRowUpdating="GridViewS_RowUpdating" runat="server" CssClass="mydatagrid simpleshape1" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" autogeneratecolumns="False" RowStyle-CssClass="rows">
    <Columns>
    <asp:BoundField DataField="MessageID" HeaderText="ID" SortExpression="DateField"/>
    <asp:BoundField DataField="Number" HeaderText="Number" SortExpression="DateField" />
    <asp:BoundField DataField="Code" HeaderText="Code" SortExpression="DateField" />
    <asp:TemplateField>
    <ItemTemplate>
    <asp:TextBox runat="server" ID="bodymes"></asp:TextBox>
    </ItemTemplate>
    </asp:TemplateField>

    <asp:ButtonField CommandName="Update" Text="Responder" />

    </Columns>

    </asp:GridView>

    CodeBehind

    public void GridViewS_RowUpdating(Object sender, GridViewUpdateEventArgs e)
    {
    string strcon = "server=000.000.00.000;DB=test;userid=fakeuser;pass=fakepass;";
    string str;


    string mesbody = this.GridViewS.Rows.ToString();
    int mesid = Convert.ToInt32(e.Keys);


    MySqlCommand com;
    object obj;
    MySqlConnection con = new MySqlConnection(strcon);
    con.Open();
    str = "UPDATE Messages SET Message=@Answer where MessageID=@mescode";
    com = new MySqlCommand(str, con);
    com.CommandType = CommandType.Text;
    com.Parameters.AddWithValue("@Answer", mesbody);
    com.Parameters.AddWithValue("@mescode", mesid);
    obj = com.ExecuteScalar();
    }

    I've used those two methods in mesbody and mesid because the other ones that I found on internet were showing errors. Thank you in advance again. 

    Monday, August 14, 2017 10:34 PM
  • User2103319870 posted

    cannot pass the data from the TextBox, and cannot store it in the specified ID. 

    You need to find the textbox control first and then get the content from textbox 

      protected void GridViewS_RowUpdating(object sender, GridViewUpdateEventArgs e)
            {
                int row = e.RowIndex;
                //Find the control and get the text from texbox control
                string txtAnswer = ((TextBox)GridViewS.Rows[row].FindControl("bodymes")).Text;
                //Your code here
    
            }

    Monday, August 14, 2017 10:50 PM
  • User980347791 posted

    Thank you so much for your answer. I added this to the GridView

    <asp:GridView ID="GridViewS" DataKeyNames="MessageID" OnRowUpdating="GridViewS_RowUpdating" runat="server" CssClass="mydatagrid simpleshape1" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" autogeneratecolumns="False" RowStyle-CssClass="rows">

    And tried this 4 different versions, without luck. 

    int row = e.RowIndex;
    string txtAnswer = ((TextBox)GridViewS.Rows[row].FindControl("bodymes")).Text;
    int mesid = 21;

    It saves something in the table, because the gridview only shows the rows of NULL Messages, but when I cheked in the Database, the field has nothing. 

    int row = e.RowIndex;
    string txtAnswer = ((TextBox)GridViewS.Rows[row].FindControl("bodymes")).Text;

    If I try it like this it does nothing.

    int row = e.RowIndex;
    string txtAnswer =((TextBox)GridViewS.Rows[row].FindControl("bodymes")).Text;
    int mesid = ConvertToInt32(e.Keys);

    This one shows System.InvalidCastException; "You cannot convert an System.Collections.Specialized.OrderedDictionary object to System.Iconvertible"

    int row = e.RowIndex;
    string txtAnswer =((TextBox)GridViewS.Rows[row].FindControl("bodymes")).Text;
    int mesid = int.Parse(e.Keys.ToString()):

    This shows "System.FormatException, the string doesnt have the right format"

    So, sadly I still have no luck.

    Tuesday, August 15, 2017 4:00 PM
  • User2103319870 posted

    I think the problem lies with accessing the data key from Gridview.

    You can try with below code

      protected void GridViewS_RowUpdating(object sender, GridViewUpdateEventArgs e)
            {
                int row = e.RowIndex;
                //Find the control and get the text from texbox control
                string txtAnswer = ((TextBox)GridViewS.Rows[row].FindControl("bodymes")).Text;
                //Get the message from datakey
                int id = Int32.Parse(GridViewS.DataKeys[e.RowIndex].Value.ToString());
                //Your code here
    
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 16, 2017 1:04 AM
  • User980347791 posted

    Thank you so much. It finally works!

    Monday, August 21, 2017 1:44 PM