无法更新SQL数据库
本文关键字：数据库 SQL 更新 | 更新日期: 2023-09-27 18:13:55
我在ASP创建一个网站。. NET (c#)，将活动列在数据列表中(通过在数据列表中使用<div>来相互分离，因此每个活动都列在一个块中)。
我不能通过c#更新活动表中的特定列，因为它找不到标量变量@camp_id(活动的ID)。
我用这个命令来更新:
sqlCmd = "UPDATE Campagnes SET camp_status=2 WHERE camp_id=@camp_id";
有人知道如何通过使用'camp_id'来更新'camp_status'到2，因此只有该特定活动的'camp_status'(而不是从数据列表中的其他人)将被更新?
编辑:这是我使用的全部代码:
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
string sqlConn;
string sqlCmd;
sqlConn = @"Data Source=my-ip,1433;Initial Catalog=DbName;
Integrated Security=False;user id=sa;password=password";
sqlCmd= "UPDATE Campagnes SET camp_status=2 WHERE camp_id=@camp_id";
conn.ConnectionString = sqlConn;
cmd.Connection = conn;
cmd.CommandText = sqlCmd;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
正如你所看到的，我还没有使用参数，因为我不知道如何添加这个，以及它如何从数据库读取值。
我现在使用的完整代码:
SqlConnection conn2 = new SqlConnection();
SqlCommand cmd2 = new SqlCommand();
string sqlConn2;
string sqlCmd2;
sqlConn2 = @"Data Source=my-ip,1433;Initial Catalog=dbname;Integrated Security=False;user id=sa;password=password";
sqlCmd2 = "select * from Campagnes";
conn2.ConnectionString = sqlConn2;
cmd2.Connection = conn2;
cmd2.CommandText = sqlCmd2;
conn2.Open();
SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
id = dr.GetInt32(0);
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
string sqlConn;
string sqlCmd;
sqlConn = @"Data Source=my-ip,1433;Initial Catalog=dbname;Integrated Security=False;user id=sa;password=password";
sqlCmd = "UPDATE Campagnes SET camp_status=1 WHERE camp_id=" + id;
cmd.Parameters.AddWithValue("@camp_id", id);
conn.ConnectionString = sqlConn;
cmd.Connection = conn;
cmd.CommandText = sqlCmd;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
不能在这里上传我的图片所以这里有一个外部链接到截图:https://i.stack.imgur.com/zHG9a.png
正如你在图片中看到的那样，所有分隔的白色块都是一个数据列表，但由一个div分隔，该div将在添加新活动时生成。当我们点击右下角的编辑按钮时，camp_status需要设置为2(这样管理员就知道该活动需要编辑)。
ASP来源: div id="popUpPanel">
<p>Waarom vindt u dat deze campagne nog niet in orde is? Geef uw feedback hieronder in:</p>
<asp:TextBox ID="TextBox1" CssClass="box" TextMode="MultiLine" runat="server"></asp:TextBox>
<asp:Button ID="Button1" OnClick="Button1_Click" runat="server" Text="OK" />
</div>
<asp:DataList CellPading="5" ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False" Font-Underline="False" style="margin-right: 0px" >
<ItemTemplate>
<div class="list" style="padding-left: 25px; padding-right: 10px; padding-top: 10px;">
<asp:Label ID="titelLabel" runat="server" style="font-size: xx-large" Text='<%# Eval("titel") %>' />
<br />
<asp:Label ID="Label1" runat="server" style="font-size: xx-large; display: none;" Text='<%# Eval("camp_id") %>' />
<asp:Label ID="datum_geplaatstLabel" runat="server" Text='<%# Eval("datum_geplaatst") %>' />
<br /><br />
<strong>Korte beschrijving:</strong><br />
<asp:Label ID="korte_beschrijvingLabel" runat="server" Text='<%# Eval("korte_beschrijving") %>' />
<br /><br />
<strong>Lange beschrijving:</strong><br />
<asp:Label ID="lange_beschrijvingLabel" runat="server" Text='<%# Eval("lange_beschrijving") %>' />
<br />
<table class="auto-style1">
<tr>
<td class="auto-style2"><strong>
<asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/img/edit.png" OnClientClick="showPopUp(); return false;" Style="margin-left:9px;" />
</strong></td>
<td><strong>
<asp:ImageButton ID="ImageButton1" runat="server" asp:Imagebutt="" ImageUrl="~/img/vink.png" Style="margin-left:0px;" />
</strong></td>
</tr>
</table>
<br />
</div>
</ItemTemplate>
</asp:DataList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:WebhoostConnectionString %>" SelectCommand="SELECT [titel], [datum_geplaatst], [korte_beschrijving], [lange_beschrijving], [camp_id] FROM [Campagnes]"></asp:SqlDataSource>
So when we click on the 'Button1' in the PopupPanel the camp_status needs to be set to 2 only of that specific campaign. As you can see in the source I was also trying to use the label I was talking about, but everytime a new div is created, all coming labels will have 'Label1' as ID so it will also pick all campaign ID's and not just one..
<div id="popUpPanel">
<p>Waarom vindt u dat deze campagne nog niet in orde is? Geef uw feedback hieronder in:</p>
<asp:TextBox ID="TextBox1" CssClass="box" TextMode="MultiLine" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Submit" CommandArgument='<%= Campagnes.camp_id %>' />
</div>
<asp:DataList CellPading="5" ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False" Font-Underline="False" style="margin-right: 0px" >
<ItemTemplate>
<div class="list" style="padding-left: 25px; padding-right: 10px; padding-top: 10px;">
<asp:Label ID="titelLabel" runat="server" style="font-size: xx-large" Text='<%# Eval("titel") %>' />
<br />
<asp:Label ID="Label1" runat="server" style="font-size: xx-large; display: none;" Text='<%# Eval("camp_id") %>' />
<asp:Label ID="datum_geplaatstLabel" runat="server" Text='<%# Eval("datum_geplaatst") %>' />
<br /><br />
<strong>Korte beschrijving:</strong><br />
<asp:Label ID="korte_beschrijvingLabel" runat="server" Text='<%# Eval("korte_beschrijving") %>' />
<br /><br />
<strong>Lange beschrijving:</strong><br />
<asp:Label ID="lange_beschrijvingLabel" runat="server" Text='<%# Eval("lange_beschrijving") %>' />
<br />
<table class="auto-style1">
<tr>
<td class="auto-style2"><strong>
<asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="~/img/edit.png" OnClientClick="showPopUp(); return false;" Style="margin-left:9px;" />
</strong></td>
<td><strong>
<asp:ImageButton ID="ImageButton1" runat="server" asp:Imagebutt="" ImageUrl="~/img/vink.png" Style="margin-left:0px;" />
</strong></td>
</tr>
</table>
<br />
</div>
</ItemTemplate>
</asp:DataList>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:WebhoostConnectionString %>" SelectCommand="SELECT [titel], [datum_geplaatst], [korte_beschrijving], [lange_beschrijving], [camp_id] FROM [Campagnes]"></asp:SqlDataSource>
c#源代码(编辑EventArgs到commanddeventargs e):
string id;
SqlConnection conn2 = new SqlConnection();
SqlCommand cmd2 = new SqlCommand();
string sqlConn2;
string sqlCmd2;
sqlConn2 = @"Data Source=81.169.242.73,1433;Initial Catalog=Webhoost;Integrated Security=False;user id=sa;password=63310Kw1c";
sqlCmd2 = "select * from Campagnes";
conn2.ConnectionString = sqlConn2;
cmd2.Connection = conn2;
cmd2.CommandText = sqlCmd2;
conn2.Open();
SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
id = e.CommandArgument.ToString();
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
string sqlConn;
string sqlCmd;
sqlConn = @"Data Source=81.169.242.73,1433;Initial Catalog=Webhoost;Integrated Security=False;user id=sa;password=63310Kw1c";
sqlCmd = "UPDATE Campagnes SET camp_status=1 WHERE camp_id=" + id;
cmd.Parameters.AddWithValue("@camp_id", id);
conn.ConnectionString = sqlConn;
cmd.Connection = conn;
cmd.CommandText = sqlCmd;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
您必须定义并添加参数@camp_id
cmd.Parameters.AddWithValue("@camp_id", youCampIdValue);