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]
GO
<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