如何更新数据库中建议的状态? [英] How to update the status of the suggestion in the database?
本文介绍了如何更新数据库中建议的状态?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在为我的公司开发一个基于Web的安全建议程序，员工可以在此程序中提出自己的任何安全建议.现在，我正在研究该系统的管理"部分.管理员将能够看到表格中列出的所有安全建议.在表格的最后一列，状态将在此处列出.当管理员单击这些建议之一的状态时，将出现一个新的弹出窗口(asp.net ajax ModalPopUpExtender)，其中列出了所有可能的状态，例如:已执行，已批准...等.选择这些状态之一，建议的状态将在数据库中更新.我编写了代码，但是它仍然没有更新建议的状态， **所以您能帮我修改它吗?**
** ASP.NET代码:**
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID"
width="900px" CssClass="mGrid"
DataSourceID="SqlDataSource1"
OnRowDataBound="GridView1_RowDataBound">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" CssClass="alt" />
<HeaderStyle Font-Bold = "True" ForeColor="Black" Height="20px"/>
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Description" HeaderText="Description"
SortExpression="Description" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Username" HeaderText="Username"
SortExpression="Username" />
<asp:BoundField DataField="DivisionShortcut" HeaderText="DivisionShortcut"
SortExpression="DivisionShortcut" />
<asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
<%-- This to make status be opened and edited through the Ajax ModalPopUp Window --%>
<asp:TemplateField HeaderText="Status">
<ItemTemplate>
<asp:LinkButton runat="server" ID="lnkSuggestionStatus" Text=''<%#Eval("Status")%>''
OnClick="lnkSuggestionStatus_Click">
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<%--<asp:HyperLinkField HeaderText="Status"
SortExpression="Status" />--%>
</Columns>
<RowStyle HorizontalAlign="Center" />
</asp:GridView>
<asp:Button runat="server" ID="btnModalPopUp" style="display:none" />
<AjaxToolkit:ModalPopUpExtender ID="modalPopUpExtender1"
runat="server"
TargetControlID="btnModalPopUp"
PopupControlID="pnlPopUp"
BackgroundCssClass="popUpStyle"
PopupDragHandleControlID="panelDragHandle"
OkControlID="OKButton">
</AjaxToolkit:ModalPopUpExtender>
<asp:Panel runat="server" ID="pnlPopUp">
<asp:RadioButtonList ID="StatusList" runat="server" RepeatColumns="1" RepeatDirection="Vertical"
RepeatLayout="Table" TextAlign="Left" DataSourceID="SuggestionStatusDataSource"
DataTextField="Status" DataValueField="ID">
<asp:ListItem id="option1" runat="server" Value="ACTIONED" />
<asp:ListItem id="option2" runat="server" Value="APPROVED" />
<asp:ListItem id="option3" runat="server" Value="PENDING" />
<asp:ListItem id="option4" runat="server" Value="TRANSFERRED" />
</asp:RadioButtonList>
<asp:SqlDataSource ID="SuggestionStatusDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
SelectCommand="SELECT * FROM [SafetySuggestionsStatus]"></asp:SqlDataSource>
<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" />
<asp:Button ID="OKButton" runat="server" Text="Close" />
</asp:Panel>
</ContentTemplate>
</asp:UpdatePanel>
**隐藏代码:**
公共 void btnSendStatus_Click(对象发件人，EventArgs e){
var statusID = StatusList.SelectedValue;
字符串 connString = " ;
// 用于更新安全建议的状态
字符串 updateCommand = " ;
使用(SqlConnection conn = 新 SqlConnection(connString))
{
conn.Open();
// 使用(SqlCommand cmd =新的SqlCommand(cmdText，conn))
使用(SqlCommand cmd = 新 SqlCommand(updateCommand，conn))
{
cmd.Parameters.AddWithValue(" ，statusID);
cmd.ExecuteNonQuery();
}
}
SendSuggestionStatusToUser(statusID);
}
**更新:**
仅供参考，我具有以下数据库设计:
员工表:用户名，名称...
SafetySuggestionsLog:ID，标题，描述，用户名，StatusID
SafetySuggestionsStatus:ID，状态
解决方案
ConnectionStrings:testConnectionString％> SelectCommand ="SELECT * FROM [SafetySuggestionsStatus]"></asp:SqlDataSource> < asp:Button ID ="confirmButton" runat =服务器" Text =确认" OnClientClick ="javascript:return Confirm(``您确定要向所有者发送有关安全建议的电子邮件通知吗?") OnClick ="btnSendStatus_Click"/> < asp:Button ID ="OKButton" runat =服务器" Text =关闭"/> </asp:Panel> </ContentTemplate> </asp:UpdatePanel>
**隐藏代码:**
公共 void btnSendStatus_Click(对象发件人，EventArgs e){ var statusID = StatusList.SelectedValue; 字符串 connString = " ; // 用于更新安全建议的状态 字符串 updateCommand = " ; 使用(SqlConnection conn = 新 SqlConnection(connString)) { conn.Open(); // 使用(SqlCommand cmd =新的SqlCommand(cmdText，conn)) 使用(SqlCommand cmd = 新 SqlCommand(updateCommand，conn)) { cmd.Parameters.AddWithValue(" ，statusID); cmd.ExecuteNonQuery(); } } SendSuggestionStatusToUser(statusID); }
**更新:**
仅供参考，我具有以下数据库设计:
员工表:用户名，名称... SafetySuggestionsLog:ID，标题，描述，用户名，StatusID SafetySuggestionsStatus:ID，状态
为什么更新uggestionsStatus表?我认为您需要使用新的状态ID更新SafetySuggestionsLog..
如果是这种情况，则需要有两个值.一个是StatusID，另一个是当前的SafetySuggestionsLog ID.您的更新命令应进行如下更改.
并且您现在需要设置两个参数.
字符串 updateCommand = " + " ;
I am developing a web-based safety suggestions 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. The Admin will be able to see all safety suggestions listed in a table. In the last column of the table, 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. I wrote the code but still it doesn''t update the status of the suggestion, **so could you please help me in modifying it?**
**ASP.NET code:**
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID"
width="900px" CssClass="mGrid"
DataSourceID="SqlDataSource1"
OnRowDataBound="GridView1_RowDataBound">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" CssClass="alt" />
<HeaderStyle Font-Bold = "True" ForeColor="Black" Height="20px"/>
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Description" HeaderText="Description"
SortExpression="Description" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Username" HeaderText="Username"
SortExpression="Username" />
<asp:BoundField DataField="DivisionShortcut" HeaderText="DivisionShortcut"
SortExpression="DivisionShortcut" />
<asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
<%-- This to make status be opened and edited through the Ajax ModalPopUp Window --%>
<asp:TemplateField HeaderText="Status">
<ItemTemplate>
<asp:LinkButton runat="server" ID="lnkSuggestionStatus" Text=''<%#Eval("Status")%>''
OnClick="lnkSuggestionStatus_Click">
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<%--<asp:HyperLinkField HeaderText="Status"
SortExpression="Status" />--%>
</Columns>
<RowStyle HorizontalAlign="Center" />
</asp:GridView>
<asp:Button runat="server" ID="btnModalPopUp" style="display:none" />
<AjaxToolkit:ModalPopUpExtender ID="modalPopUpExtender1"
runat="server"
TargetControlID="btnModalPopUp"
PopupControlID="pnlPopUp"
BackgroundCssClass="popUpStyle"
PopupDragHandleControlID="panelDragHandle"
OkControlID="OKButton">
</AjaxToolkit:ModalPopUpExtender>
<asp:Panel runat="server" ID="pnlPopUp">
<asp:RadioButtonList ID="StatusList" runat="server" RepeatColumns="1" RepeatDirection="Vertical"
RepeatLayout="Table" TextAlign="Left" DataSourceID="SuggestionStatusDataSource"
DataTextField="Status" DataValueField="ID">
<asp:ListItem id="option1" runat="server" Value="ACTIONED" />
<asp:ListItem id="option2" runat="server" Value="APPROVED" />
<asp:ListItem id="option3" runat="server" Value="PENDING" />
<asp:ListItem id="option4" runat="server" Value="TRANSFERRED" />
</asp:RadioButtonList>
<asp:SqlDataSource ID="SuggestionStatusDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
SelectCommand="SELECT * FROM [SafetySuggestionsStatus]"></asp:SqlDataSource>
<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" />
<asp:Button ID="OKButton" runat="server" Text="Close" />
</asp:Panel>
</ContentTemplate>
</asp:UpdatePanel>
**Code-Behind:**
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";
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);
}
**UPDATE:**
FYI, I have the following database design:
Employee Table: Username, Name...
SafetySuggestionsLog: ID, Title, Description, Username, StatusID
SafetySuggestionsStatus: ID, Status 解决方案
ConnectionStrings:testConnectionString %>" SelectCommand="SELECT * FROM [SafetySuggestionsStatus]"></asp:SqlDataSource> <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" /> <asp:Button ID="OKButton" runat="server" Text="Close" /> </asp:Panel> </ContentTemplate> </asp:UpdatePanel>
**Code-Behind:**
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"; 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); }
**UPDATE:**
FYI, I have the following database design:
Employee Table: Username, Name... SafetySuggestionsLog: ID, Title, Description, Username, StatusID SafetySuggestionsStatus: ID, Status
why you update uggestionsStatus table? I think you need to update SafetySuggestionsLog with new Status id..
If that is the case you need to have two values. one is StatusID and other one is current SafetySuggestionsLog ID. your update command should be change as below.
and you need to set two parameters now.
string updateCommand = "UPDATE SafetySuggestionsLog SET StatusID= @statusID" + "WHERE ID= @SafetySuggestionsLogID";
这篇关于如何更新数据库中建议的状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
