<%@ Page Language="VB" AutoEventWireup="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<link rel="stylesheet" href="css/BentooCommonControls.css" type="text/css">
<script language="VB" runat="server">
Dim MyConnection As SqlConnection
Function CreateDataSource() As ICollection
Dim MyCommand As SqlDataAdapter = new SqlDataAdapter("select * from SP_Families order by Family_ID DESC", MyConnection) Dim DS As DataSet = new DataSet()
MyCommand.Fill(DS, "SP_Families")
Dim dv As New DataView(DS.Tables("SP_Families"))
Return dv
End Function
Sub Page_Load(sender As Object, e As EventArgs)
MyConnection = New SqlConnection("server=localhost;uid=sa;pwd=aaa;database=test")
BindGrid()
End Sub
Sub MyDataGrid_Page(sender As Object, e As DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
BindGrid()
End Sub
Sub BindGrid()
MyDataGrid.DataSource = CreateDataSource()
MyDataGrid.DataBind() End Sub
Sub addNewValue(sender As Object, e As EventArgs)
Dim sSQL as String
Dim MyCommand As SqlCommand
sSQL = "insert into SP_Families (Family_Name,Family_Subject) values ('" & fName.value & "','" & fSubject.value & "')"
MyCommand = New SqlCommand(sSQL, MyConnection)
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
lblEnabled.InnerHtml = "<b>已添加记录</b><br>" & sSQL.ToString()
Catch Exp As SQLException
lblEnabled.InnerHtml = Exp.Number
If Exp.Number = 2627
lblEnabled.InnerHtml = "错误:已存在具有相同主键的记录"
Else
lblEnabled.InnerHtml = "错误:未能添加记录,请确保正确填写了字段"
End If
End Try
MyCommand.Connection.Close()
BindGrid()
lblEnabled.InnerHtml = sSQL
End Sub
Sub MyDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub
Sub MyDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs) MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub
Sub MyDataGrid_Update(Sender As Object, E As DataGridCommandEventArgs) Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim UpdateCmd As String
Dim id as Integer = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
Dim CurrentTextBox As TextBox
CurrentTextBox = E.Item.Cells(2).Controls(0)
Dim sName as String = CurrentTextBox.Text CurrentTextBox = E.Item.Cells(3).Controls(0)
Dim sSubject as String = CurrentTextBox.Text
UpdateCmd = "UPDATE SP_Families SET Family_Name = '" & sName & "', Family_Subject = '" & sSubject & "' where Family_ID = " & id
lblEnabled.InnerHtml = UpdateCmd
Return
MyCommand = New SqlCommand(UpdateCmd, MyConnection)
MyCommand.Connection.Open() Try
MyCommand.ExecuteNonQuery()
lblEnabled.InnerHtml = "<b>已更新记录</b><br>" & UpdateCmd.ToString()
MyDataGrid.EditItemIndex = -1
Catch Exp As SQLException
If Exp.Number = 2627
lblEnabled.InnerHtml = "错误:已存在具有相同主键的记录"
Else
lblEnabled.InnerHtml = "错误:未能更新记录,请确保正确填写了字段"
End If
lblEnabled.Style("color") = "red"
End Try MyCommand.Connection.Close() BindGrid()
End Sub
</script>
<body>
<p>
<h3>Paging with DataGrid</h3>
<form runat=server>
<table bgcolor="#eeeeee" border="0" cellpadding="6">
<tr>
<td>系列名称:</td>
<td><Input id="fName" runat="server"></td>
<td>系列主题:</td>
<td><Input id="fSubject" runat="server"></td>
</tr>
<tr>
<td align="right" colspan="4"><Input Type="button" onserverclick="addNewValue" class="posbtn" value=" 新增 " runat="server"></td>
</tr>
</table><br>
<asp:DataGrid id="MyDataGrid" runat="server"
AllowPaging="True"
PageSize="10"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="right"
OnPageIndexChanged="MyDataGrid_Page"
BorderColor="black"
Width="800"
border="0"
BorderWidth="1"
GridLines="Both"
CellPadding="3"
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="#eeeeee"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
DataKeyField="Family_ID"
AutoGenerateColumns="false"
>
<Columns>
<asp:EditCommandColumn EditText="编辑" CancelText="取消" UpdateText="更新" ItemStyle-Wrap="false"/>
<asp:BoundColumn HeaderText="Family_ID" SortExpression="Family_ID" ReadOnly="True" DataField="Family_ID" ItemStyle-Wrap="false"/>
<asp:BoundColumn HeaderText="Family_Name" SortExpression="Family_Name" DataField="Family_Name"/>
<asp:BoundColumn HeaderText="Family_Subject" SortExpression="Family_Subject" DataField="Family_Subject"/>
<asp:BoundColumn HeaderText="Family_Date" SortExpression="Family_Date" ReadOnly="True" DataField="Family_Date" ItemStyle-Wrap="false"/>
</Columns>
</asp:DataGrid>
<p>
<table bgcolor="#eeeeee" cellpadding="6">
<tr>
<td nowrap>
<span id="lblEnabled" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
</td>
</tr>
</table>
</form>
</body>
</html>
if (!IsPostBack)
{
//here
}
MyConnection = New SqlConnection("server=localhost;uid=sa;pwd=aaa;database=test")
BindGrid()
End IF
Dim MyCommand As SqlCommand
Dim UpdateCmd As String
Dim id as Integer = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
Dim CurrentTextBox As TextBox
CurrentTextBox = E.Item.Cells(2).Controls(0)
Dim sName as String = CurrentTextBox.Text CurrentTextBox = E.Item.Cells(3).Controls(0)
Dim sSubject as String = CurrentTextBox.Text
UpdateCmd = "UPDATE SP_Families SET Family_Name = '" & sName & "', Family_Subject = '" & sSubject & "' where Family_ID = " & id
lblEnabled.InnerHtml = UpdateCmd Return '这个地方是不是多了一个return?,VB不是很熟 MyCommand = New SqlCommand(UpdateCmd, MyConnection)
MyCommand.Connection.Open() Try
MyCommand.ExecuteNonQuery()
lblEnabled.InnerHtml = "<b>已更新记录</b><br>" & UpdateCmd.ToString()
MyDataGrid.EditItemIndex = -1
Catch Exp As SQLException
If Exp.Number = 2627
lblEnabled.InnerHtml = "错误:已存在具有相同主键的记录"
Else
lblEnabled.InnerHtml = "错误:未能更新记录,请确保正确填写了字段"
End If
lblEnabled.Style("color") = "red"
End Try MyCommand.Connection.Close() BindGrid()
Return
是用来调试的,忘了去掉。加了If Not IsPostBack Then 是可以了,可是要点两次“编辑”才会转到编辑状态,input里的默认值也没了,翻页也翻不动了。
汗!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Families]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SP_Families]
GOCREATE TABLE [dbo].[SP_Families] (
[Family_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Family_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Family_Subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Family_Date] [datetime] NULL
) ON [PRIMARY]
GO表结构,拜托帮忙测试!