关于DATAGRID于SQL数据绑定后。在DG中插入行的问题和在列表中显示局域网的SQL服务器的问题。问题解决就结帖子。

和上一帖子一样。回复10人开新帖子加分
我用SQL和C#应用程序做了一个数据库读写的小程序。但不知道如何在DG中插入新的行。
我对DG编辑完了在写到SQL中好。还是。操作完毕后在写到SQL数据库中好。
局域网中如何现实可用的SQL服务器列表。
网上的用USING MICROSOFT……的方法。我调试的时候提示没有找该库什么的。。
我用的是VS。NET2000+SQL2000+ADO.NET+FW SDK
问题解决马上给分。在线等待。。

15 个解决方案

#1

1.在Datagrid增加新行，比较长，直接copy到你的ASPX页面即可
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<title>Adding Rows</title>
<style>
  a {behavior:url(..\..\mouseover.htc);}
  hr {height:2px;color:black;}
  .StdText {font-family:verdana;font-size:9pt;font-weight:bold;}
  .StdTextBox {font-family:verdana;font-size:9pt;border:solid 1px black;filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true');}
  .Shadow {filter:progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true');}
</style>

<script runat="server">
String [] aTitleOfCourtesy = new String[4] {"Ms.", "Mr.", "Mrs.", "Dr."};

public void Page_Load(Object sender, EventArgs e)
{
// Initialize only the first time...
if (!Page.IsPostBack)
{
lblURL.Text = Request.Url + "<hr>";
}
}

public void OnLoadData(Object sender, EventArgs e)
{
LoadData();
UpdateView();
}

public void AddNewRow(Object sender, EventArgs e)
{
// Grab the dataset
DataSet ds = (DataSet) Session["MyData"];
DataTable dt = ds.Tables["MyTable"];

// Add a blank row
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);

// If needed, assign default values
// dr["column_name"] = ...

// Update the in-memory dataset
Session["MyData"] = ds;

// Index of the new item in the page: last +1
int nNewItemIndex = grid.Items.Count;

// If the is full, move to next page. In this case, first item
if (nNewItemIndex >= grid.PageSize)
{
grid.CurrentPageIndex ++;
nNewItemIndex = 0;
}

// Turn edit mode on for the newly added row
grid.EditItemIndex = nNewItemIndex;

// Refresh the grid
UpdateView();
}

public void PageIndexChanged(Object sender, DataGridPageChangedEventArgs e)
{
// Set the current item to edit mode
grid.CurrentPageIndex = e.NewPageIndex;

// Refresh the grid
UpdateView();
}

public void ItemCreated(Object sender, DataGridItemEventArgs e)
{
ListItemType lit = e.Item.ItemType;
if (lit == ListItemType.EditItem)
{
// Get the data row
DataRowView drv = (DataRowView) e.Item.DataItem;

// Initializes controls
DropDownList ddTitles = (DropDownList) e.Item.FindControl("ddTitles");
if (drv != null)
ddTitles.SelectedIndex = Array.IndexOf(aTitleOfCourtesy, drv["titleofcourtesy"].ToString());
}

//////////////////////////////////////////////////////////////////////////

if (lit == ListItemType.Pager)
{
// The pager as a whole has the following layout:
//
// <TR><TD colspan=X> ... links ... </TD></TR>
//
// Item points to <TR>. The code below moves to <TD>.
TableCell pager = (TableCell) e.Item.Controls[0];

// Loop through the pager buttons skipping over blanks
// (Blanks are treated as LiteralControl(s)
for (int i=0; i<pager.Controls.Count; i+=2)
{
Object o = pager.Controls[i];
if (o is LinkButton)
{
LinkButton h = (LinkButton) o;
h.Text = "[ " + h.Text + " ]";
}
else
{
Label l = (Label) o;
l.Text = "<b>Page " + l.Text + "</b>";
}
}
}
}

public void EditCommand(Object sender, DataGridCommandEventArgs e)
{
// Set the current item to edit mode
grid.EditItemIndex = e.Item.ItemIndex;

// Refresh the grid
UpdateView();
}

public void UpdateCommand(Object sender, DataGridCommandEventArgs e)
{
// Retrieve the new text from bound columns
int nColPositionIndex = 2; // 0-based position of the column
int nColFromIndex = 3; // 0-based position of the column

TextBox txtPosition = (TextBox) e.Item.Cells[nColPositionIndex].Controls[0];
        TextBox txtFrom = (TextBox) e.Item.Cells[nColFromIndex].Controls[0];

// Retrieve the new text in the templated column
TextBox txtFirstName = (TextBox) e.Item.FindControl("txtFirstName");
TextBox txtLastName = (TextBox) e.Item.FindControl("txtLastName");
DropDownList ddTitles = (DropDownList) e.Item.FindControl("ddTitles");

// MUST decide whether to UPDATE or to INSERT. The decision is made based on
// the fact that the DataSet has added rows
DataSet ds = (DataSet) Session["MyData"];
DataTable dt = ds.Tables["MyTable"];
DataRow drLast = dt.Rows[dt.Rows.Count-1];

SqlConnection conn = new SqlConnection(txtConn.Text);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

if (drLast.RowState == DataRowState.Added)
{
// Drop the newly added row from memory
drLast.RejectChanges();

StringBuilder sb1 = new StringBuilder("");
sb1.Append("INSERT Employees (firstname, lastname, titleofcourtesy, title, country) VALUES(");
sb1.Append("@sFirstName, @sLastName, @sTitle, @sPosition, @sCountry)");
cmd.CommandText = sb1.ToString();

// EmployeeID is an identity column

SqlParameter p1 = new SqlParameter("@sPosition", SqlDbType.NVarChar, 30);
p1.Direction = ParameterDirection.Input;
p1.Value = txtPosition.Text;
cmd.Parameters.Add(p1);

SqlParameter p2 = new SqlParameter("@sCountry", SqlDbType.NVarChar, 15);
p2.Direction = ParameterDirection.Input;
p2.Value = txtFrom.Text;
cmd.Parameters.Add(p2);

SqlParameter p3 = new SqlParameter("@sTitle", SqlDbType.NVarChar, 25);
p3.Direction = ParameterDirection.Input;
p3.Value = ddTitles.SelectedItem.Text;
cmd.Parameters.Add(p3);

SqlParameter p4 = new SqlParameter("@sFirstName", SqlDbType.NVarChar, 10);
p4.Direction = ParameterDirection.Input;
p4.Value = txtFirstName.Text;
cmd.Parameters.Add(p4);

SqlParameter p5 = new SqlParameter("@sLastName", SqlDbType.NVarChar, 20);
p5.Direction = ParameterDirection.Input;
p5.Value = txtLastName.Text;
cmd.Parameters.Add(p5);
}
else
{
StringBuilder sb2 = new StringBuilder("");
sb2.Append("UPDATE Employees SET ");
sb2.Append("title=@sPosition, country=@sCountry, titleofcourtesy=@sTitle, ");
sb2.Append("firstname=@sFirstName, lastname=@sLastName ");
sb2.Append("WHERE employeeid=@nEmpID");
cmd.CommandText = sb2.ToString();

SqlParameter p1 = new SqlParameter("@nEmpID", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
p1.Value = grid.DataKeys[e.Item.ItemIndex];
cmd.Parameters.Add(p1);

SqlParameter p2 = new SqlParameter("@sPosition", SqlDbType.NVarChar, 30);
p2.Direction = ParameterDirection.Input;
p2.Value = txtPosition.Text;
cmd.Parameters.Add(p2);

SqlParameter p3 = new SqlParameter("@sCountry", SqlDbType.NVarChar, 15);
p3.Direction = ParameterDirection.Input;
p3.Value = txtFrom.Text;
cmd.Parameters.Add(p3);

SqlParameter p4 = new SqlParameter("@sTitle", SqlDbType.NVarChar, 25);
p4.Direction = ParameterDirection.Input;
p4.Value = ddTitles.SelectedItem.Text;
cmd.Parameters.Add(p4);

SqlParameter p5 = new SqlParameter("@sFirstName", SqlDbType.NVarChar, 10);
p5.Direction = ParameterDirection.Input;
p5.Value = txtFirstName.Text;
cmd.Parameters.Add(p5);

SqlParameter p6 = new SqlParameter("@sLastName", SqlDbType.NVarChar, 20);
p6.Direction = ParameterDirection.Input;
p6.Value = txtLastName.Text;
cmd.Parameters.Add(p6);
}

// Execute the command

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

// Reset the edit mode for the current item
grid.EditItemIndex = -1;

// Refresh the grid
LoadData();
UpdateView();

}

public void CancelCommand(Object sender, DataGridCommandEventArgs e)
{
// Reset the edit mode for the current item
grid.EditItemIndex = -1;

// Reject changes on the last row
DataSet ds = (DataSet) Session["MyData"];
DataTable dt = ds.Tables["MyTable"];
DataRow drLast = dt.Rows[dt.Rows.Count-1];
if (drLast.RowState == DataRowState.Added)
{
drLast.RejectChanges();
if (grid.Items.Count == 1)
grid.CurrentPageIndex--;
}

// Refresh the grid
UpdateView();
}

#2

接着上面的
////////////////////////////////////////////////////////////////////////

private void LoadData()
{
SqlConnection conn = new SqlConnection(txtConn.Text);
SqlDataAdapter da = new SqlDataAdapter(txtCommand.Text, conn);

DataSet ds = new DataSet();
da.Fill(ds, "MyTable");

Session["MyData"] = ds;
}

private void UpdateView()
{
DataSet ds = (DataSet) Session["MyData"];

// Bind the data
grid.DataSource = ds.Tables["MyTable"];

// Display the data
grid.DataBind();
}

private bool IsLastPage()
{
if (grid.CurrentPageIndex+1 == grid.PageCount)
return true;
return false;
}

</script>

<body bgcolor="ivory" style="font-family:arial;font-size:small">


<h2>Adding New Rows to DataGrids</h2>
<asp:Label runat="server" cssclass="StdText" font-bold="true">Current path: </asp:label>
<asp:Label runat="server" id="lblURL" cssclass="StdText" style="color:blue"></asp:label>

<form runat="server">

  <table>
  <tr>
  <td><asp:label runat="server"  text="Connection String" cssclass="StdText" /></td>
  <td><asp:textbox runat="server" id="txtConn"
Enabled="false"
cssclass="StdTextBox"
width="600px"
text="DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;" /></td></tr>

  <tr>
  <td><asp:label runat="server"  text="Command Text" cssclass="StdText"/></td>
  <td><asp:textbox runat="server" id="txtCommand"
        Enabled="false"
width="600px"
cssclass="StdTextBox"
text="SELECT employeeid, titleofcourtesy, firstname, lastname, title, country FROM Employees" /></td></tr></table>

    <br>
    <asp:linkbutton runat="server" text="Go get data..." onclick="OnLoadData" />

    <hr>

    <asp:DataGrid id="grid" runat="server"
AutoGenerateColumns="false"
CssClass="Shadow" BackColor="white"
CellPadding="2" CellSpacing="0"
BorderStyle="solid" BorderColor="black" BorderWidth="1"
Font-Size="x-small" Font-Names="verdana"
ShowFooter="true"
AllowPaging="true"
PageSize="4"
DataKeyField="employeeid"
OnItemCreated="ItemCreated"
OnPageIndexChanged="PageIndexChanged"
        OnEditCommand="EditCommand"
        OnUpdateCommand="UpdateCommand"
        OnCancelCommand="CancelCommand">

<AlternatingItemStyle BackColor="palegoldenrod" />
<ItemStyle BackColor="beige" />
<PagerStyle Mode="NumericPages" HorizontalAlign="right" />
<EditItemStyle BackColor="yellow" Font-Bold="true" />
<HeaderStyle ForeColor="white" BackColor="brown" HorizontalAlign="center" Font-Bold="true" />

        <columns>
   <asp:BoundColumn runat="server" DataField="employeeid" HeaderText="ID" Readonly="true"
DataFormatString="<span style='margin-left:5;margin-right:5'>{0}</span>" >
<itemstyle backcolor="lightblue" font-bold="true" HorizontalAlign="right" />
   </asp:BoundColumn>

   <asp:TemplateColumn runat="server" HeaderText="Employee Name">
<itemtemplate>
<asp:label runat="server"
style="margin-left:5;margin-right:5"
Text='<%# DataBinder.Eval(Container.DataItem, "TitleOfCourtesy") + "<b> " +
  DataBinder.Eval(Container.DataItem, "LastName") + "</b>" + ", " +
  DataBinder.Eval(Container.DataItem, "FirstName") %>' />
</itemtemplate>
<edititemtemplate>
<asp:dropdownlist runat="server" id="ddTitles"
DataSource='<% # aTitleOfCourtesy %>' />
<asp:textbox runat="server" width="80px" id="txtFirstName" tabindex="0"
Text='<%# DataBinder.Eval(Container.DataItem, "firstname") %>' /><br>
<asp:textbox runat="server" width="140px" id="txtLastName"
Text='<%# DataBinder.Eval(Container.DataItem, "lastname") %>' />
</edititemtemplate>
<footertemplate>
<asp:linkbutton runat="server" id="btnNewRow" onclick="AddNewRow"
Enabled='<%# IsLastPage() %>' Text="Add new row..." />
</footertemplate>
   </asp:TemplateColumn>

   <asp:BoundColumn runat="server" DataField="title" HeaderText="Position" />
   <asp:BoundColumn runat="server" DataField="country" HeaderText="From" />

   <asp:EditCommandColumn runat="server"
EditText="<img src=edit.gif border=0 align=absmiddle alt='Edit this item'>"
UpdateText="<img src=ok.gif border=0 align=absmiddle alt='Save changes'>"
CancelText="<img src=cancel.gif border=0 align=absmiddle alt='Cancel editing'>">
<itemstyle BackColor="yellow" HorizontalAlign="center" />
   </asp:EditCommandColumn>

</columns>
     </asp:DataGrid>
</form>

</body>
</html>

#3

说明一下
增加一行，必须向DataGrid绑定的DataTable对象附加一个新的DataRow，代码如下
DataRow dr=dt.NewRow();
dt.Rows.Add(dr);
int nNextItemIndex=DataGrid.Items.Count;  //页中新记录的索引，总数+1
if (nNewItemIndex>=DataGrid.PageSize) //如果大于当前页，则移到下页的第一项
{
  DataGrid.CurrentPageIndex++;
  nNewItemIndex=0;
}
DataGrid.EditItemIndex=nNewItemIndex;  //为新增加的记录打开编辑模式

#4

DataGrid.Items.Count这个我的VS。NET 里面找不到啊。能详细些吗。
hychieftain(不同）
我正在看你的帖子。长了点。
谢谢。你曾经在一个关于这方面的帖子发过网址。最后那个网页找不到了。请问你的恢复是你要发的内容吗。不是话能不能在发一下啊。。谢谢。。

#5

在DG中插入行最简单的办法是直接在数据源表中插入一空行就行了!!

#6

这么麻烦呀！帮你up吧。

#7

谢谢。。。

#8

是一本书的示例代码
《构建Web解决方案 --- 应用ASP.NET和ADO.NET》

#9

DataGrid.Items.Count说明在下面有，是MSDN2003的地址
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003APR.1033/cpref/html/frlrfsystemwebuiwebcontrolsdatagriditemcollectionclasscounttopic.htm

#10

好长啊，up

#11

先将数据源绑定到DataTable
然后用DataTable的NewRow新加入一行。

如：DataTable table=ds.Tables["aaa"];
DataRow newRow=table.NewRow();
newRow["ParameterID"]=s;
newRow["ParameterName"]=this.txt_ParaName.Text;
newRow["ParameterType"]=this.txt_ParaType.Text;
newRow["SetValue"]=this.txt_ParaValue.Text;
newRow["ParameterDesc"]=this.txt_ParaDesc.Text;
newRow["DataType"]=this.txt_DataType.Text;
newRow["InitValue"]=this.txt_ParaDefault.Text;
table.Rows.Add(newRow);
然后提交修改

#12

我对DG编辑完了在写到SQL中好。还是操作完毕后在写到SQL数据库中好？
：：DG编辑完了
网上的用USING MICROSOFT……的方法。我调试的时候提示没有找该库什么的？
：：可能是你没有加入该库，找找Down一个。

#13

sy246(新手！多关照！)在那里下载？？
能不能提供一个网址啊。。谢谢。。

#14

hychieftain(不同)
你给的网址我打不开啊。。
能不把他发上来吗。。
谢谢。。。

#15

sy246(新手！多关照！) 。我想能不能在不指定列的情况下更新到SQL啊。。

#1

#2

#3

#4

#5