Databases
StackOverflow2013
Postsdbo
POHow to update specific column in a GridView using Ajax ModalPopUpExtender control?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to update specific column in a GridView using Ajax ModalPopUpExtender control?
    text
    copied!<p>I am a new ASP.NET developer and I am developing a web-based suggestions box program for my company where the employees can submit any safety suggestions they have. Now, I am working on the Administration part of this system. </p> <p>The Admin will be able to see all suggestions listed in a GridView control. In the last column of the GridView, the status will be listed there. When the Admin clicks on the status of one of these suggestion, a new pop-up window (asp.net ajax ModalPopUpExtender) will be appeared with listing all the possible status such as: actioned, approved... etc. And when the Admin selects one of these status, the status of the suggestion will be updated in the database. <strong><em>I wrote the code but still it doesn't update the status of the suggestion,</em></strong> </p> <p><strong>so could you please help me in modifying it?</strong></p> <p>FYI, I have the following database design:</p> <pre><code>Employee Table: Username, Name... SafetySuggestionsLog: ID, Title, Description, Username, StatusID SafetySuggestionsStatus: ID, Status </code></pre> <p><strong>ASP.NET code:</strong></p> <pre><code>&lt;asp:UpdatePanel ID="UpdatePanel1" runat="server"&gt; &lt;ContentTemplate&gt; &lt;asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID" width="900px" CssClass="mGrid" DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound"&gt; &lt;AlternatingRowStyle BackColor="White" ForeColor="#284775" CssClass="alt" /&gt; &lt;HeaderStyle Font-Bold = "True" ForeColor="Black" Height="20px"/&gt; &lt;Columns&gt; &lt;asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" /&gt; &lt;asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /&gt; &lt;asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /&gt; &lt;asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /&gt; &lt;asp:BoundField DataField="Username" HeaderText="Username" SortExpression="Username" /&gt; &lt;asp:BoundField DataField="DivisionShortcut" HeaderText="DivisionShortcut" SortExpression="DivisionShortcut" /&gt; &lt;asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" /&gt; &lt;%-- This to make status be opened and edited through the Ajax ModalPopUp Window --%&gt; &lt;asp:TemplateField HeaderText="Status"&gt; &lt;ItemTemplate&gt; &lt;asp:LinkButton runat="server" ID="lnkSuggestionStatus" Text='&lt;%#Eval("Status")%&gt;' OnClick="lnkSuggestionStatus_Click"&gt; &lt;/asp:LinkButton&gt; &lt;/ItemTemplate&gt; &lt;/asp:TemplateField&gt; &lt;%--&lt;asp:HyperLinkField HeaderText="Status" SortExpression="Status" /&gt;--%&gt; &lt;/Columns&gt; &lt;RowStyle HorizontalAlign="Center" /&gt; &lt;/asp:GridView&gt; &lt;asp:Button runat="server" ID="btnModalPopUp" style="display:none" /&gt; &lt;AjaxToolkit:ModalPopUpExtender ID="modalPopUpExtender1" runat="server" TargetControlID="btnModalPopUp" PopupControlID="pnlPopUp" BackgroundCssClass="popUpStyle" PopupDragHandleControlID="panelDragHandle" OkControlID="OKButton"&gt; &lt;/AjaxToolkit:ModalPopUpExtender&gt; &lt;asp:Panel runat="server" ID="pnlPopUp"&gt; &lt;asp:RadioButtonList ID="StatusList" runat="server" RepeatColumns="1" RepeatDirection="Vertical" RepeatLayout="Table" TextAlign="Left" DataSourceID="SuggestionStatusDataSource" DataTextField="Status" DataValueField="ID"&gt; &lt;asp:ListItem id="option1" runat="server" Value="ACTIONED" /&gt; &lt;asp:ListItem id="option2" runat="server" Value="APPROVED" /&gt; &lt;asp:ListItem id="option3" runat="server" Value="PENDING" /&gt; &lt;asp:ListItem id="option4" runat="server" Value="TRANSFERRED" /&gt; &lt;/asp:RadioButtonList&gt; &lt;asp:SqlDataSource ID="SuggestionStatusDataSource" runat="server" ConnectionString="&lt;%$ ConnectionStrings:testConnectionString %&gt;" SelectCommand="SELECT * FROM [SafetySuggestionsStatus]"&gt;&lt;/asp:SqlDataSource&gt; &lt;asp:Button ID="confirmButton" runat="server" Text="Confirm" OnClientClick="javascript:return confirm('Are you sure you want to send an email notification about the safety suggestion to the owner?')" OnClick="btnSendStatus_Click" /&gt; &lt;asp:Button ID="OKButton" runat="server" Text="Close" /&gt; &lt;/asp:Panel&gt; &lt;/ContentTemplate&gt; &lt;/asp:UpdatePanel&gt; </code></pre> <p><strong>Code-Behind:</strong></p> <pre><code>protected void lnkSuggestionStatus_Click(object sender, EventArgs e) { LinkButton lnkSuggestionStatus = sender as LinkButton; //var safetySuggestionsId= //get reference to the row selected GridViewRow gvrow = (GridViewRow)lnkSuggestionStatus.NamingContainer; //set the selected index to the selected row so that the selected row will be highlighted GridView1.SelectedIndex = gvrow.RowIndex; //show the modalPopUp modalPopUpExtender1.Show(); } public void btnSendStatus_Click(object sender, EventArgs e) { var statusID = StatusList.SelectedValue; string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspdbTest;Integrated Security=True"; //For updating the status of the safety suggestion string updateCommand = "UPDATE SafetySuggestionsStatus SET ID= @statusID where ID=@SafetySuggestionsID""; using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); //using (SqlCommand cmd = new SqlCommand(cmdText, conn)) using (SqlCommand cmd = new SqlCommand(updateCommand, conn)) { cmd.Parameters.AddWithValue("@ID", statusID); cmd.ExecuteNonQuery(); } } SendSuggestionStatusToUser(statusID); } </code></pre> <p><strong>UPDATE:</strong></p> <p><strong>When I debugged the code, I got the following error:</strong></p> <pre><code>SqlException was unhandled by user code Must declare the scalar variable "@SafetySuggestionsID" </code></pre> <p><strong>UPDATE 2:</strong></p> <p>I modified my code as you suggested:</p> <pre><code>protected void lnkSuggestionStatus_Click(object sender, EventArgs e) { LinkButton lnkSuggestionStatus = sender as LinkButton; //var safetySuggestionsId = //get reference to the row selected GridViewRow gvrow = (GridViewRow)lnkSuggestionStatus.NamingContainer; //set the selected index to the selected row so that the selected row will be highlighted GridView1.SelectedIndex = gvrow.RowIndex; HiddenField1.Value = gvrow.RowIndex.ToString(); //show the modalPopUp modalPopUpExtender1.Show(); } public void btnSendStatus_Click(object sender, EventArgs e) { var statusID = StatusList.SelectedValue; string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspdbTest;Integrated Security=True"; //For updating the status of the safety suggestion string updateCommand = "UPDATE SafetySuggestionsLog SET StatusID= @statusID where ID=@SafetySuggestionsID"; using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(updateCommand, conn)) { cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(statusID)); cmd.Parameters.AddWithValue("@SafetySuggestionsID", Convert.ToInt32(HiddenField1.Value)); cmd.ExecuteNonQuery(); } //reset the value of hiddenfield HiddenField1.Value = "-1"; } //SendSuggestionStatusToUser(statusID); } </code></pre> <p>However, while I am debugging the code, I got the following error:</p> <blockquote> <p>Must declare the scalar variable"@statusID"</p> </blockquote> <p>I don't know why I am getting this error while I am already defined it.</p> <p><strong>UPDATE 3:</strong></p> <p>I added <code>GridView1.DataBind()</code> to update the GridView with the updated status of the selected suggestion but it does not work with me.</p> <pre><code>public void btnSendStatus_Click(object sender, EventArgs e) { var statusID = StatusList.SelectedValue; string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspdbTest;Integrated Security=True"; //For updating the status of the safety suggestion string updateCommand = "UPDATE SafetySuggestionsLog SET StatusID= @statusID where ID=@SafetySuggestionsID"; using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(updateCommand, conn)) { cmd.Parameters.AddWithValue("@statusID", Convert.ToInt32(statusID)); cmd.Parameters.AddWithValue("@SafetySuggestionsID", Convert.ToInt32(HiddenField1.Value)); cmd.ExecuteNonQuery(); } //reset the value of hiddenfield HiddenField1.Value = "-1"; } GridView1.DataBind(); //SendSuggestionStatusToUser(statusID); } </code></pre> <p><strong>UPDATE 4:</strong> I added the following but it did not work:</p> <pre><code>public void btnSendStatus_Click(object sender, EventArgs e) { var statusID = StatusList.SelectedValue; string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspdbTest;Integrated Security=True"; //For updating the status of the safety suggestion string updateCommand = "UPDATE SafetySuggestionsLog SET StatusID= @statusID where ID=@SafetySuggestionsID"; using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(updateCommand, conn)) { cmd.Parameters.AddWithValue("@statusID", Convert.ToInt32(statusID)); cmd.Parameters.AddWithValue("@SafetySuggestionsID", Convert.ToInt32(HiddenField1.Value)); cmd.ExecuteNonQuery(); } //reset the value of hiddenfield HiddenField1.Value = "-1"; } UpdatePanel1.Update(); GridView1.DataBind(); //SendSuggestionStatusToUser(statusID); } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload