将文本框项目和网格视图项目插入sql数据库
本文关键字：项目 插入 sql 数据库 视图 文本 网格 | 更新日期: 2023-09-27 18:20:34
我正试图在SQL数据库中插入一组4个文本框值和一个动态网格。目前，我将要添加到的动态网格值设置为逗号分隔，然后拆分并输入SQL。这个部分本身是有效的，但当我试图在gridview之外添加textboxes时，无论我如何尝试，我都会遇到很多错误
最近的一次尝试是将gridview之外的文本框添加到相同的逗号分隔格式中，然后解析并插入到SQL中。这不起作用——我收到的错误说我有一个Null Exception，但就我的一生而言，我找不到它。任何帮助都将不胜感激。
private void InsertRecords(StringCollection sc)
{
StringBuilder sb = new StringBuilder(string.Empty);
string[] splitItems = null;
const string sqlStatement = "INSERT INTO Traveler_Management.dbo.traveler (FirstName, LastName, Email, Phone, FlightNo, ArrivalDate, DepartureDate, Country, City) VALUES (@firstName, @lastName, @email, @phone, @TextBox4, @TextBox1, @TextBox2, @ddl1, @TextBox3)";
{
foreach (string item in sc)
{
if (item.Contains(","))
{
splitItems = item.Split(",".ToCharArray());
sb.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3], splitItems[4], splitItems[5], splitItems[6], splitItems[7], splitItems[8]);
}
}
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(sb.ToString(), connection))
{
cmd.CommandType = CommandType.Text;
//cmd.ExecuteNonQuery();
}
}
lblMessage.Text = "Records successfully saved!";
}
}
protected void submit_Click(object sender, EventArgs e)
{
int rowIndex = 0;
StringCollection sc = new StringCollection();
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
if (dtCurrentTable.Rows.Count > 0)
{
TextBox firstName = (TextBox)FindControl("firstName");
TextBox lastName = (TextBox)FindControl("lastName");
TextBox email = (TextBox)FindControl("email");
TextBox phone = (TextBox)FindControl("phone");
sc.Add(string.Format("{5},{6},{7},{8}", firstName.Text, lastName.Text, email.Text, phone.Text));
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//extract the TextBox values
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox4");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("TextBox1");
TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("TextBox2");
TextBox box4 = (TextBox)Gridview1.Rows[rowIndex].Cells[4].FindControl("TextBox3");
DropDownList ddl1 = (DropDownList)Gridview1.Rows[rowIndex].Cells[6].FindControl("ddl1");
//Get the values from the textboxes and drop down list then add it to the collections with a comma as the delimited values
sc.Add(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}", box1.Text, box2.Text, box3.Text, box4.Text, ddl1.SelectedItem.Text));
rowIndex++;
}
//Call the insert method to execute inserts
InsertRecords(sc);
}
}
}
private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
}
我很抱歉这里是aspx文件
<%@ Page Language="C#" AutoEventWireup="true" MasterPageFile="~/Site.Master" CodeFile="Default16.aspx.cs" Inherits="Default16" %>
<asp:Content ID="intakeTripDets" ContentPlaceHolderID="MainContent" runat="server">
<div class="input">
<label>First Name</label><br />
<asp:TextBox ID="firstName" runat="server"></asp:TextBox><br /><br />
<label>Last Name</label><br />
<asp:TextBox ID="lastName" runat="server"></asp:TextBox><br /><br />
<label>Email Address</label><br />
<asp:TextBox ID="email" runat="server">email@example.com</asp:TextBox><br /><br />
<label>Phone Number</label><br />
<asp:TextBox ID="phone" MaxLength="16" runat="server">001-555-888-1234</asp:TextBox><br /><br />
</div>
<div class="input">
<p>Please enter trip details on the below form. You can add additional rows as needed for each leg of our trip.</p>
<asp:Label runat="server" ID="lblMessage" Text=""></asp:Label><br />
<asp:gridview ID="Gridview1" runat="server" ShowFooter="true" AutoGenerateColumns="false" >
<Columns>
<asp:BoundField DataField="RowNumber" HeaderText="" />
<asp:TemplateField HeaderText="Flight Number" runat="server">
<ItemTemplate>
<asp:TextBox ID="TextBox4" width="100px" runat="server"></asp:TextBox><br />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Arrival Date">
<ItemTemplate>
<asp:TextBox ID="TextBox1" width="100px" runat="server" ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Departure Date">
<ItemTemplate>
<asp:TextBox ID="TextBox2" width="100px" runat="server" ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:TextBox ID="TextBox3" width="100px" runat="server" ></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:DropDownList ID="ddl1" runat="server" Width="125px">
<asp:ListItem Value="-1">--SELECT COUNTRY--</asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID="buttonRemove" Width="20px" Height="20px" Text="-" runat="server" CommandName="Delete" OnClick="buttonRemove_Click"/>
<asp:Button ID="ButtonAdd" Width="20px" Height="20px" runat="server" Text="+" CommandName="Insert" onclick="ButtonAdd_Click" />
</ItemTemplate>
<FooterStyle />
<FooterTemplate>
<asp:Button ID="submit" runat="server" text="Submit Trip" Height="30px" Width="100px" OnClick="submit_Click"/></div>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:gridview>
</div>
</asp:Content>
更新：
我现在对代码做了一些更改，但回发时文本框似乎为空。有人能解释一下如何将我的文本框中的文本提交到数据库中吗？
由于无法看到前端标记（.aspx文件），我只能猜测文档的结构，但我认为这更接近于您想要实现的目标。
我假设这四个文本框出现在网格视图的行中。老实说，我不知道ViewState是如何参与的，但它可能不应该。此外，使用数据绑定可能会更容易完成这整件事。下面是一个视频，它很好地解释了如何绑定GridView控件：https://www.youtube.com/watch?v=-zAXVvxdt3o
protected void submit_Click(object sender, EventArgs e)
{
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
if (dtCurrentTable.Rows.Count > 0)
{
foreach (GridViewRow row in Gridview1.Rows)
{
StringCollection sc = new StringCollection();
TextBox box1 = (TextBox)row.FindControl("TextBox4");
TextBox box2 = (TextBox)row.FindControl("TextBox1");
TextBox box3 = (TextBox)row.FindControl("TextBox2");
TextBox box4 = (TextBox)row.FindControl("TextBox3");
DropDownList ddl1 = (DropDownList)row.FindControl("ddl1");
sc.Add(string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}", firstName.Text, lastName.Text, email.Text, phone.Text, box1.Text, box2.Text, box3.Text, box4.Text, ddl1.SelectedItem.Text));
InsertRecords(sc);
}
}
}
}
private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
}
private void InsertRecords(StringCollection sc)
{
foreach (string item in sc)
{
if (item.Contains(","))
{
string[] splitItems = item.Split(",".ToCharArray());
string sqlStatement =
"INSERT INTO Traveler_Management.dbo.traveler (FirstName, LastName, Email, Phone, FlightNo, ArrivalDate, DepartureDate, Country, City) VALUES (@firstName, @lastName, @email, @phone, @TextBox4, @TextBox1, @TextBox2, @ddl1, @TextBox3)";
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlStatement, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@firstName", splitItems[0]);
command.Parameters.AddWithValue("@lastName", splitItems[1]);
command.Parameters.AddWithValue("@email", splitItems[2]);
command.Parameters.AddWithValue("@phone", splitItems[3]);
command.Parameters.AddWithValue("@TextBox4", splitItems[4]);
command.Parameters.AddWithValue("@TextBox1", splitItems[5]);
command.Parameters.AddWithValue("@TextBox2", splitItems[6]);
command.Parameters.AddWithValue("@ddl1", splitItems[7]);
command.Parameters.AddWithValue("@TextBox3", splitItems[8]);
try
{
command.ExecuteNonQuery();
lblMessage.Text = "Records successfully saved!";
}
catch (Exception ex)
{
lblMessage.Text = "There was an error saving the record.";
//Code here to handle the exception however you want to do that
}
}
connection.Close();
}
}
}
}