在GridView的使用文本框更新数据到SQL Server [英] Using Textbox in Gridview to Update Data to SQL Server
问题描述
至少可以这么说,我想不出如何使文本框里面输入的信息张贴到SQL数据库用的更新下的列按钮注释
。每个文本内的信息正在编辑的时候,新的信息应该发布到SQL数据库,但我不知道如何开始。任何帮助是AP preciated。
< ASP:GridView控件ID =GridView1=服务器AllowSorting =真
的AutoGenerateColumns =FALSE背景色=白BORDERCOLOR =#DEDFDE
边框样式=无边框宽度=1px的CELLPADDING =4
的DataSourceID =SqlDataSource3前景色=黑网格=垂直>
< AlternatingRowStyle背景色=白/>
<柱体和GT;
< ASP:BoundField的数据字段=姓氏的HeaderText =姓
SORTEX pression =姓氏/>
< ASP:BoundField的数据字段=名字的HeaderText =名
SORTEX pression =名字/>
< ASP:BoundField的数据字段=公司名称的HeaderText =公司
SORTEX pression =公司名称/>
< ASP:的TemplateField的HeaderText =CONF费SORTEX pression =ConferenceFee>
<&ItemTemplate中GT;
<%#Convert.ToDecimal(EVAL(ConferenceFee))的ToString(#,## 0.00)%>
< / ItemTemplate中>
< / ASP:的TemplateField>
< ASP:的TemplateField的HeaderText =总成本SORTEX pression =TOTALCOST>
<&ItemTemplate中GT;
<%#Convert.ToDecimal(EVAL(TOTALCOST))的ToString(#,## 0.00)%>
< / ItemTemplate中>
< / ASP:的TemplateField>
< ASP:BoundField的数据字段=PaymentInfoID的HeaderText =发票
SORTEX pression =PaymentInfoID/>
< ASP:的TemplateField的HeaderText =注意事项SORTEX pression =注意事项>
<&ItemTemplate中GT;
< ASP:文本框ID =notesTXTBOX=服务器>< / ASP:文本框>
< / ItemTemplate中>
< / ASP:的TemplateField>
< ASP:ButtonField字段按钮类型=按钮的CommandName =更新文本=更新/>
< /专栏>
< FooterStyle背景色=#CCCC99/>
< HeaderStyle背景色=#6B696BFONT-粗体=真前景色=白/>
< PagerStyle背景色=#F7F7DE前景色=黑HorizontalAlign =右/>
< RowStyle的BackColor =#F7F7DE/>
< SelectedRowStyle背景色=#CE5D5AFONT-粗体=真前景色=白/>
< SortedAscendingCellStyle背景色=#FBFBF2/>
< SortedAscendingHeaderStyle背景色=#848384/>
< SortedDescendingCellStyle背景色=#EAEAD3/>
< SortedDescendingHeaderStyle背景色=#575357/>
< / ASP:GridView的>
< ASP:SqlDataSource的ID =SqlDataSource1=服务器
的ConnectionString =下;%$的ConnectionStrings:ConfOnline%>中
的SelectCommand =SELECT cr.ConferenceID,p.LastName,p.FirstName,c.CompanyName,pay.ConferenceFee,pay.PaymentInfoID,pay.Notes,pay.TotalCost从会议CR,人民P,C公司,PaymentInfo工资WHERE铬。 UsersIDNum = p.UsersIDNum AND c.CompanyID = p.CompanyIDNum AND pay.PaymentInfoID = cr.PaymentInfoIDNum和cr.ConferenceIDNum=@confID和cr.Deleted ='N'AND pay.Deleted ='N'ORDER BY c.CompanyName >
< SelectParameters>
< ASP:ControlParameter控件ID =conferenceDDLNAME =confID
属性名=的SelectedValue/>
< / SelectParameters>
< / ASP:SqlDataSource的>
下面是一个 GridView控件
有一个标签$ C的标记$ C>控制,这将是系列号,3
文本框
控制插入的文本,以及下拉列表
选择一些默认值。
< ASP:GridView控件ID =GridView1=服务器ShowFooter =真的AutoGenerateColumns =假
OnRowDeleting =GridView1_RowDeleting>
<柱体和GT;
< ASP:的TemplateField的HeaderText =序列号>
<&ItemTemplate中GT;
< ASP:标签ID =Label1的=服务器文本='<%#Container.DataItemIndex + 1%>'>< / ASP:标签>
< / ItemTemplate中>
< / ASP:的TemplateField>
< ASP:的TemplateField的HeaderText =COL1>
<&ItemTemplate中GT;
< ASP:文本框=服务器ID =TXT1文本='<%#的eval(列1)%GT;'>< / ASP:文本框>
< / ItemTemplate中>
< / ASP:的TemplateField>
< ASP:的TemplateField的HeaderText =col2的>
<&ItemTemplate中GT;
< ASP:文本框ID =TXT2=服务器文本='<%#的eval(列2)%>'>< / ASP:文本框>
< / ItemTemplate中>
< / ASP:的TemplateField>
< ASP:的TemplateField的HeaderText =COL3>
<&ItemTemplate中GT;
< ASP:文本框ID =txt3=服务器文本='<%#的eval(栏3)%>'>< / ASP:文本框>
< / ItemTemplate中>
< / ASP:的TemplateField>
< ASP:的TemplateField的HeaderText =下拉>
<&ItemTemplate中GT;
< ASP:DropDownList的ID =DropDownList1=服务器>
< ASP:ListItem的>伦敦和LT; / ASP:ListItem的>
< ASP:ListItem的>巴黎及LT; / ASP:ListItem的>
< ASP:ListItem的>新德里< / ASP:ListItem的>
< ASP:ListItem的>纽约< / ASP:ListItem的>
< / ASP:DropDownList的>
< / ItemTemplate中>
< FooterStyle HorizontalAlign =右/>
< FooterTemplate>
< ASP:按钮的ID =btnAddNewRow=服务器文本=AddRow的OnClick =添加/>
< / FooterTemplate>
< / ASP:的TemplateField>
< ASP:CommandField中按钮类型=按钮的ShowDeleteButton =真/>
< /专栏>
< / ASP:GridView的>
现在的code背后:
列表<串GT; newlist =新的List<串GT;();
保护无效的Page_Load(对象发件人,EventArgs的发送)
{
如果(!的IsPostBack)
{
// SetInitialRow();
变种表= CreateDataTable();
table.Rows.Add(,,);
BindGridView(表);
}
//设置第一个空行到网格视图
私人数据表CreateDataTable()
{
VAR DT =新的DataTable
{
列= {列1,列2,栏3,Column4}
};
返回DT;
} 私人无效BindGridView(DataTable的表)
{
GridView1.DataSource =表;
GridView1.DataBind();
的for(int i = 0; I< GridView1.Rows.Count-1;我++)
{
DropDownList的DDL2 =(DropDownList的)GridView1.Rows [I] .FindControl(DropDownList1);
ddl2.ClearSelection();
。ddl2.Items.FindByText(newlist [I])选择= TRUE;
}
}
保护无效添加(对象发件人,EventArgs的发送)
{
变种newtable的= PopulateTableFromGridView();
newTable.Rows.Add(,,);
BindGridView(newtable的);
}
私人数据表PopulateTableFromGridView()
{
变种表= CreateDataTable();
的for(int i = 0; I< GridView1.Rows.Count;我++)
{
//提取文本框值
文本框BOX1 =(文本框)GridView1.Rows [I] .FindControl(TXT1);
文本框BOX2 =(文本框)GridView1.Rows [I] .FindControl(TXT2);
文本框BOX3 =(文本框)GridView1.Rows [I] .FindControl(txt3);
DropDownList的DDL =(DropDownList的)GridView1.Rows [I] .FindControl(DropDownList1);
table.Rows.Add(box1.Text,box2.Text,box3.Text,ddl.SelectedItem.Text);
newlist.Add(ddl.SelectedItem.Text);
}
返回表;
}
保护无效GridView1_RowDeleting(对象发件人,GridViewDeleteEventArgs E)
{
VAR DT = PopulateTableFromGridView(); 如果(dt.Rows.Count→1)
{
dt.Rows [e.RowIndex] .Delete();
}
BindGridView(DT);
}
保护无效btnSavetoDB_Click(对象发件人,EventArgs的发送)
{
DataTable的DT =新的DataTable();
dt.Columns.AddRange(新的DataColumn [5] {新的DataColumn(序列号,typeof运算(字符串))
新的DataColumn(列1,typeof运算(字符串))
新的DataColumn(列2中的typeof(字符串))
新的DataColumn(栏3中的typeof(字符串))
新的DataColumn(Column4,typeof运算(字符串))});
的foreach(在GridView1.Rows GridViewRow行)
{
标签serNo = row.FindControl(标签1)作为标签;
文本框TXT1 = row.FindControl(TXT1)的文本框;
文本框TXT2 = row.FindControl(TXT2)的文本框;
文本框txt3 = row.FindControl(txt3)的文本框;
DropDownList的DDL = row.FindControl(DropDownList1)作为DropDownList的;
dt.Rows.Add(serNo.Text,txt1.Text,txt2.Text,txt3.Text,ddl.SelectedItem.Text);
} 如果(dt.Rows.Count大于0)
{
字符串consString = ConfigurationManager.ConnectionStrings [数据库连接]的ConnectionString。
使用(SqlConnection的CON =新的SqlConnection(consString))
{
使用(SqlBulkCopy的SqlBulkCopy的=新SqlBulkCopy的(CON))
{
//设置数据库表名
sqlBulkCopy.DestinationTableName =dbo.GridTable; // [可选]:与数据库表的地图数据表列
sqlBulkCopy.ColumnMappings.Add(序列号,序列号);
sqlBulkCopy.ColumnMappings.Add(列1,列1);
sqlBulkCopy.ColumnMappings.Add(列2,列2);
sqlBulkCopy.ColumnMappings.Add(栏3,栏3);
sqlBulkCopy.ColumnMappings.Add(Column4,Column4);
con.Open();
sqlBulkCopy.WriteToServer(DT);
con.Close();
}
}
}
}
基本上上述code片会帮助你这个样子:
在 pageLoad的
,网格将有一个空行,在其中您可以编写自己的价值观。有一个AddRow按钮,它会帮助你增加一排,也删除按钮删除特定的行。
快照:
To say the least, I can't figure how to make the info typed inside the textbox to post to the SQL database with an Update button under column Notes
. Each time the info within the textbox is being edited, the new info should post to the SQL database but I don't know how to begin. Any help is appreciated.
<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="False" BackColor="White" BorderColor="#DEDFDE"
BorderStyle="None" BorderWidth="1px" CellPadding="4"
DataSourceID="SqlDataSource3" ForeColor="Black" GridLines="Vertical">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="LastName" HeaderText="Last Name"
SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="First Name"
SortExpression="FirstName" />
<asp:BoundField DataField="CompanyName" HeaderText="Company"
SortExpression="CompanyName" />
<asp:TemplateField HeaderText="Conf Fee" SortExpression="ConferenceFee">
<ItemTemplate>
<%# Convert.ToDecimal(Eval("ConferenceFee")).ToString("#,##0.00") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Total Cost" SortExpression="TotalCost">
<ItemTemplate>
<%# Convert.ToDecimal(Eval("TotalCost")).ToString("#,##0.00") %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="PaymentInfoID" HeaderText="Invoice"
SortExpression="PaymentInfoID" />
<asp:TemplateField HeaderText="Notes" SortExpression="Notes">
<ItemTemplate>
<asp:TextBox ID="notesTXTBOX" runat="server"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:ButtonField ButtonType="Button" CommandName="Update" Text="Update" />
</Columns>
<FooterStyle BackColor="#CCCC99" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FBFBF2" />
<SortedAscendingHeaderStyle BackColor="#848384" />
<SortedDescendingCellStyle BackColor="#EAEAD3" />
<SortedDescendingHeaderStyle BackColor="#575357" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConfOnline %>"
SelectCommand="SELECT cr.ConferenceID, p.LastName, p.FirstName, c.CompanyName, pay.ConferenceFee, pay.PaymentInfoID, pay.Notes, pay.TotalCost FROM Conference cr, People p, Company c, PaymentInfo pay WHERE cr.UsersIDNum=p.UsersIDNum AND c.CompanyID=p.CompanyIDNum AND pay.PaymentInfoID=cr.PaymentInfoIDNum AND cr.ConferenceIDNum=@confID AND cr.Deleted='N' AND pay.Deleted='N' ORDER BY c.CompanyName">
<SelectParameters>
<asp:ControlParameter ControlID="conferenceDDL" Name="confID"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
Below is the markup of a GridView
having a Label
control, which will be the SerialNo, 3 TextBox
controls to insert texts, and a dropdown list
to select some default values.
<asp:GridView ID="GridView1" runat="server" ShowFooter="true" AutoGenerateColumns="false"
OnRowDeleting="GridView1_RowDeleting">
<Columns>
<asp:TemplateField HeaderText="Serial No">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Container.DataItemIndex + 1 %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Col1">
<ItemTemplate>
<asp:TextBox runat="server" ID="txt1" Text='<%# Eval("Column1") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Col2">
<ItemTemplate>
<asp:TextBox ID="txt2" runat="server" Text='<%# Eval("Column2") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Col3">
<ItemTemplate>
<asp:TextBox ID="txt3" runat="server" Text='<%# Eval("Column3") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="DropDown">
<ItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem>London</asp:ListItem>
<asp:ListItem>Paris</asp:ListItem>
<asp:ListItem>New Delhi</asp:ListItem>
<asp:ListItem>New York</asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
<FooterStyle HorizontalAlign="Right" />
<FooterTemplate>
<asp:Button ID="btnAddNewRow" runat="server" Text="AddRow" OnClick="Add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ButtonType="Button" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
Now the code behind:
List<string> newlist = new List<string>();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//SetInitialRow();
var table = CreateDataTable();
table.Rows.Add("", "", "");
BindGridView(table);
}
//Sets the first empty row to the grid view
private DataTable CreateDataTable()
{
var dt = new DataTable
{
Columns = { "Column1", "Column2", "Column3","Column4" }
};
return dt;
}
private void BindGridView(DataTable table)
{
GridView1.DataSource = table;
GridView1.DataBind();
for(int i=0;i<GridView1.Rows.Count-1;i++)
{
DropDownList ddl2 = (DropDownList)GridView1.Rows[i].FindControl("DropDownList1");
ddl2.ClearSelection();
ddl2.Items.FindByText(newlist[i]).Selected = true;
}
}
protected void Add(object sender, EventArgs e)
{
var newTable = PopulateTableFromGridView();
newTable.Rows.Add("", "", "");
BindGridView(newTable);
}
private DataTable PopulateTableFromGridView()
{
var table = CreateDataTable();
for (int i = 0; i < GridView1.Rows.Count; i++)
{
//extract the TextBox values
TextBox box1 = (TextBox)GridView1.Rows[i].FindControl("txt1");
TextBox box2 = (TextBox)GridView1.Rows[i].FindControl("txt2");
TextBox box3 = (TextBox)GridView1.Rows[i].FindControl("txt3");
DropDownList ddl = (DropDownList)GridView1.Rows[i].FindControl("DropDownList1");
table.Rows.Add(box1.Text, box2.Text, box3.Text,ddl.SelectedItem.Text);
newlist.Add(ddl.SelectedItem.Text);
}
return table;
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
var dt = PopulateTableFromGridView();
if (dt.Rows.Count > 1)
{
dt.Rows[e.RowIndex].Delete();
}
BindGridView(dt);
}
protected void btnSavetoDB_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Serial No",typeof(string)),
new DataColumn("Column1", typeof(string)),
new DataColumn("Column2", typeof(string)),
new DataColumn("Column3",typeof(string)),
new DataColumn("Column4", typeof(string)) });
foreach (GridViewRow row in GridView1.Rows)
{
Label serNo = row.FindControl("Label1") as Label;
TextBox txt1 = row.FindControl("txt1") as TextBox;
TextBox txt2 = row.FindControl("txt2") as TextBox;
TextBox txt3 = row.FindControl("txt3") as TextBox;
DropDownList ddl=row.FindControl("DropDownList1") as DropDownList;
dt.Rows.Add(serNo.Text,txt1.Text, txt2.Text, txt3.Text, ddl.SelectedItem.Text);
}
if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings["DBConnect"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.GridTable";
//[OPTIONAL]: Map the DataTable columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Serial No", "Serial No.");
sqlBulkCopy.ColumnMappings.Add("Column1", "Column1");
sqlBulkCopy.ColumnMappings.Add("Column2", "Column2");
sqlBulkCopy.ColumnMappings.Add("Column3", "Column3");
sqlBulkCopy.ColumnMappings.Add("Column4", "Column4");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
}
Basically the above code piece will help you like this:
On PageLoad
, the grid will have an empty row, in which you can write your own values. There is an AddRow button which will help you to add a row, also Delete button to delete a particular row.
Snapshot:
这篇关于在GridView的使用文本框更新数据到SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!