在gridview中使用下拉列表更新SQL Server数据库表
本文关键字：SQL Server 数据库 更新 下拉列表 gridview | 更新日期: 2023-09-27 18:04:10
我有一个带有几个文本框和网格视图的web表单。一旦输入id号，网格视图将显示来自sql数据源的特定数据。理论上，网格视图中的信息可以通过下拉列表删除或编辑。问题是，当在下拉列表中进行选择并单击更新按钮时，网格视图刷新到原始选择。
我已经研究过我可能做错了什么，但我想我已经看了太久了，我只是在白费力气。我有一种感觉，这可能是由于在c#代码中反复试验而产生的一些格式错误，或者可能是我没有在那里的东西。我想再多一双眼睛也无妨。
感谢这是我的HTML: <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="../Styles/Styles.css" rel="stylesheet" />
<style type="text/css">
.auto-style1 {
width: 50%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="main outside">
<div class="user">
<table class="auto-style1">
<tr>
<td>
<asp:Label ID="lblName" runat="server" Text="Name: "></asp:Label>
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblEmail" runat="server" Text="Email:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblUserName" runat="server" Text="User Name:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblTest" runat="server" Text="Test User ID:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtTest" runat="server"></asp:TextBox>
</td>
</tr>
</table>
</div>
<div class="baby">
<asp:GridView ID="grdvwBabyInfo" runat="server" AutoGenerateColumns="False"
DataKeyNames="id_baby" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="id_baby" HeaderText="id_baby" InsertVisible="False"
ReadOnly="True" SortExpression="id_baby" Visible="False" />
<asp:BoundField DataField="id_user" HeaderText="id_user"
SortExpression="id_user" Visible="False" />
<asp:TemplateField HeaderText="Your Relation" SortExpression="relation_to_baby">
<EditItemTemplate>
<asp:DropDownList ID="drpDwnRelation" runat="server"
SelectedValue='<%# Bind("relation_to_baby") %>' AutoPostBack="False">
<asp:ListItem>mother</asp:ListItem>
<asp:ListItem>father</asp:ListItem>
<asp:ListItem>brother</asp:ListItem>
<asp:ListItem>sister</asp:ListItem>
<asp:ListItem>aunt</asp:ListItem>
<asp:ListItem>uncle</asp:ListItem>
<asp:ListItem>grandmother</asp:ListItem>
<asp:ListItem>grandfather</asp:ListItem>
<asp:ListItem>niece</asp:ListItem>
<asp:ListItem>nephew</asp:ListItem>
<asp:ListItem>friend</asp:ListItem>
<asp:ListItem>other</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("relation_to_baby") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Baby's Gender" SortExpression="sex_of_baby">
<EditItemTemplate>
<asp:DropDownList ID="drpDwnGender" runat="server"
SelectedValue='<%# Bind("sex_of_baby") %>' AutoPostBack="False">
<asp:ListItem>a baby boy</asp:ListItem>
<asp:ListItem>a baby girl</asp:ListItem>
<asp:ListItem>twins</asp:ListItem>
<asp:ListItem>triplets</asp:ListItem>
<asp:ListItem>unknown</asp:ListItem>
<asp:ListItem>other</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("sex_of_baby") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Est. Due Date" SortExpression="due_date">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("due_date") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="baby_first_name" HeaderText="Baby's First Name"
SortExpression="baby_first_name" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:bbvs_d01ConnectionString %>" DeleteCommand="DELETE FROM [bbuser_baby] WHERE [id_baby] = @original_id_baby AND [id_user] = @original_id_user AND [relation_to_baby] = @original_relation_to_baby AND [sex_of_baby] = @original_sex_of_baby AND (([due_date] = @original_due_date) OR ([due_date] IS NULL AND @original_due_date IS NULL)) AND (([baby_first_name] = @original_baby_first_name) OR ([baby_first_name] IS NULL AND @original_baby_first_name IS NULL))" InsertCommand="INSERT INTO [bbuser_baby] ([id_user], [relation_to_baby], [sex_of_baby], [due_date], [baby_first_name]) VALUES (@id_user, @relation_to_baby, @sex_of_baby, @due_date, @baby_first_name)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [bbuser_baby] WHERE ([id_user] = @id_user)" UpdateCommand="UPDATE [bbuser_baby] SET [id_user] = @id_user, [relation_to_baby] = @relation_to_baby, [sex_of_baby] = @sex_of_baby, [due_date] = @due_date, [baby_first_name] = @baby_first_name WHERE [id_baby] = @original_id_baby AND [id_user] = @original_id_user AND [relation_to_baby] = @original_relation_to_baby AND [sex_of_baby] = @original_sex_of_baby AND (([due_date] = @original_due_date) OR ([due_date] IS NULL AND @original_due_date IS NULL)) AND (([baby_first_name] = @original_baby_first_name) OR ([baby_first_name] IS NULL AND @original_baby_first_name IS NULL))">
<DeleteParameters>
<asp:Parameter Name="original_id_baby" Type="Int32" />
<asp:Parameter Name="original_id_user" Type="Int32" />
<asp:Parameter Name="original_relation_to_baby" Type="String" />
<asp:Parameter Name="original_sex_of_baby" Type="String" />
<asp:Parameter DbType="Date" Name="original_due_date" />
<asp:Parameter Name="original_baby_first_name" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="id_user" Type="Int32" />
<asp:Parameter Name="relation_to_baby" Type="String" />
<asp:Parameter Name="sex_of_baby" Type="String" />
<asp:Parameter DbType="Date" Name="due_date" />
<asp:Parameter Name="baby_first_name" Type="String" />
</InsertParameters>
<SelectParameters>
<asp:ControlParameter ControlID="txtTest" Name="id_user" PropertyName="Text" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="id_user" Type="Int32" />
<asp:Parameter Name="relation_to_baby" Type="String" />
<asp:Parameter Name="sex_of_baby" Type="String" />
<asp:Parameter DbType="Date" Name="due_date" />
<asp:Parameter Name="baby_first_name" Type="String" />
<asp:Parameter Name="original_id_baby" Type="Int32" />
<asp:Parameter Name="original_id_user" Type="Int32" />
<asp:Parameter Name="original_relation_to_baby" Type="String" />
<asp:Parameter Name="original_sex_of_baby" Type="String" />
<asp:Parameter DbType="Date" Name="original_due_date" />
<asp:Parameter Name="original_baby_first_name" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
</div>
<div class="testbox">
</div>
<div class="bottom">
<div class="buttons"><asp:Button ID="btnSave" runat="server" Text="Save Changes" OnClick="btnSave_Click" /><asp:Button ID="btnLoad" runat="server" Text="Load" OnClick="btnLoad_Click" /></div></div>
</div>
</form>
</body>
</html>
和我的c#代码:
using System;
using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace BabyEditForm
{
public partial class EditForm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnLoad_Click(object sender, EventArgs e)
{
string source = System.Configuration.ConfigurationManager.
ConnectionStrings["bbvs_d01ConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlDataReader readerBBUser = null;
SqlCommand myCommand = new SqlCommand("select * from bbuser WHERE id_user='" + txtTest.Text + "'", conn);
readerBBUser = myCommand.ExecuteReader();
while (readerBBUser.Read())
{
txtName.Text = (readerBBUser["name"].ToString());
txtEmail.Text = (readerBBUser["email"].ToString());
txtUserName.Text = (readerBBUser["username"].ToString());
}
conn.Close();
}
protected void btnSave_Click(object sender, EventArgs e)
{
string source = System.Configuration.ConfigurationManager.
ConnectionStrings["bbvs_d01ConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand cmdA = new SqlCommand("UPDATE bbuser SET name = @name, email = @email, username = @username WHERE id_user='" + txtTest.Text + "'", conn);
cmdA.Parameters.AddWithValue("@name", txtName.Text);
cmdA.Parameters.AddWithValue("@email", txtEmail.Text);
cmdA.Parameters.AddWithValue("@username", txtUserName.Text);
cmdA.ExecuteNonQuery();
SqlCommand cmdB = new SqlCommand("UPDATE bbuser_baby SET relation_to_baby = @relation_to_baby, sex_of_baby = @sex_of_baby, due_date = @due_date, baby_first_name = @baby_first_name WHERE id_user'" + txtTest.Text + "'", conn);
if (!IsPostBack)
{
foreach (GridViewRow row in grdvwBabyInfo.Rows)
{
string dropDownRelationText = ((DropDownList)
row.FindControl("drpDwnRelation")).SelectedItem.Value;
Response.Write(dropDownRelationText);
string dropDownGenderText = ((DropDownList)
row.FindControl("drpDwnGender")).SelectedItem.Value;
Response.Write(dropDownGenderText);
cmdB.Parameters.AddWithValue("@relation_to_baby", dropDownRelationText);
cmdB.Parameters.AddWithValue("@sex_of_baby", dropDownGenderText);
grdvwBabyInfo.DataBind();
cmdB.ExecuteNonQuery();
}
}
conn.Close();
}
}
}
请尝试交换以下FROM:
grdvwBabyInfo.DataBind();
cmdB.ExecuteNonQuery();
:
cmdB.ExecuteNonQuery();
grdvwBabyInfo.DataBind();