H
hoa
I am playing with .NET 2.0 Beata version. This code that works fine under .NET 1.1 is now broken. Any suggestions to fix it will be appreciated.
<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Drawing" %>
<script runat="server">
Private strConnection As String = "server=localhost;uid=sa;pwd=password;database=Northwind"
Private strSQLSelect As String = "SELECT * from Products"
Private ProductTableName As String = "Products"
Private objConnection As SqlConnection
' Important: Table must have a PRIMARY KEY. In this instance, it is PRODUCTID
' Cells are coded with column position in the datagrid counting from 0.
' ========================================
Private Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
If Not IsPostBack Then
LoadGrid()
End If
End Sub
' ========================================
Private Sub LoadGrid()
Connect()
'
Dim sql as string
If viewstate("sortfield") Is Nothing Then
sql = strSQLSelect
Else
sql = strSQLSelect & " order by " & viewstate("sortfield") & " " & viewstate("sortdirection")
End If
Dim adapter As New SqlDataAdapter(sql, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
'ok let's get fancy and insert a blank row at the top
dim sw1 as integer = 1
select case sw1
case 1
dim dr as datarow = ds.Tables(0).newrow()
'put something in the first primary key CustomerID
'dr("Productname") = " (Add new Product name here)"
ds.Tables(0).rows.insertat(dr, 0)
end select
dgProducts.DataSource = ds.Tables(ProductTableName)
dgProducts.DataBind()
End Sub
' ========================================
Private Sub Connect()
If objConnection Is Nothing Then
objConnection = New SqlConnection(strConnection)
End If
If objConnection.State = ConnectionState.Closed Then
objConnection.Open()
End If
End Sub
Private Sub Disconnect()
objConnection.Close()
End Sub
' ========================================
Public Sub EditRecord(ByVal Sender As Object, _
ByVal E As DataGridCommandEventArgs)
dgProducts.EditItemIndex = E.Item.ItemIndex
LoadGrid()
End Sub
' ========================================
Public Sub CancelEdit(ByVal Sender As Object, _
ByVal E As DataGridCommandEventArgs)
dgProducts.EditItemIndex = -1
LoadGrid()
End Sub
' ========================================
Public Sub UpdateRecord(ByVal Sender As Object, _
ByVal E As DataGridCommandEventArgs)
Dim NameTextBox As TextBox = CType(E.Item.Cells(1).Controls(0), TextBox)
Dim Name As string = Convert.tostring(NameTextBox.Text)
Dim PriceTextBox As TextBox = CType(E.Item.Cells(2).Controls(0), TextBox)
Dim Price As string = Convert.tostring(PriceTextBox.Text)
' Create and load a DataSet with records from Northwind.Products table
Connect()
Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
dim row as datarow
Dim currentRow As Integer = e.Item.DataSetIndex
'ADD NEW
if currentrow = 0 then
row = ds.tables(0).newrow()
row.Item("ProductName") = Name
row.item("Unitprice")= Price
ds.tables(0).rows.insertat(row, 0 )
else
'UPDATING EXISTING RECORD
' Retrieve the field values in the edited row
Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)
' Modify the in-memory records in the DataSet
Dim tbl As DataTable = ds.Tables(ProductTableName)
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("ProductID") _
}
row = tbl.Rows.Find(ProductID)
row.Item("ProductName") = Name
row.item("Unitprice")= Price
End if
' Reconnect the DataSet and update the database
Dim cb As New SqlCommandBuilder(adapter)
Connect()
adapter.Update(ds, ProductTableName)
Disconnect()
dgProducts.DataSource = ds.Tables(ProductTableName)
dgProducts.DataBind()
dgProducts.EditItemIndex = -1
Loadgrid()
End Sub
' ========================================
Sub DeleteRecord(Sender As Object, E As DataGridCommandEventArgs)
' Retrieve the field values in the edited row
Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)
' Create and load a DataSet with records from Northwind.Products table
Connect()
Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
' Modify the in-memory records in the DataSet
Dim tbl As DataTable = ds.Tables(ProductTableName)
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("ProductID") _
}
Dim row As DataRow = tbl.Rows.Find(ProductID)
row.Delete()
' Reconnect the DataSet and update the database
Dim cb As New SqlCommandBuilder(adapter)
Connect()
adapter.Update(ds, ProductTableName)
Disconnect()
dgProducts.DataSource = ds.Tables(ProductTableName)
dgProducts.DataBind()
dgProducts.EditItemIndex = -1
Loadgrid()
End Sub
' ===================================
public sub dg_itemcreated ( sender as object, e as datagriditemeventargs )
'
' Clear column headers for col 3 and col 4 col3 + edit col 4 = delete
If e.Item.ItemType = ListItemType.Header Then
e.Item.Cells(3).Backcolor = Color.White
e.Item.Cells(4).Backcolor = Color.White
end If
'-- add logic to modify text literal EDIT and Suppress the Delete
if e.item.itemindex = 0 then 'first row
'reference the AddNEW row
e.Item.BackColor = Color.gold
e.Item.Cells(4).Backcolor = Color.White
dim lbDelete as linkbutton = e.item.cells(4).controls(0)
dim lbEdit as linkbutton = e.item.cells(3).controls(0)
if lbDelete.text = "Delete" then
lbDelete.text = ""
end if
if lbEdit.text = "Edit" then
lbEdit.text = "Add New"
end if
if lbEdit.text = "Save" then
lbEdit.text = "Save"
end if
end if
if e.item.itemindex > 0 then
dim mydeletebutton as tablecell
mydeletebutton = e.item.cells(4)
mydeletebutton.attributes.add("onclick", _
"return confirm('Are you sure you want to delete this record?');" )
end if
end sub
' ========================================
Sub DataSort(Src As Object, E As DataGridSortCommandEventArgs)
viewstate.Add("sortfield", e.SortExpression)
If viewstate("sortdirection") Is Nothing Then
viewstate.Add("sortdirection", "ASC")
Else
If viewstate("sortdirection") = "ASC" Then
viewstate("sortdirection") = "DESC"
Else
viewstate("sortdirection") = "ASC"
End If
End If
LoadGrid()
End Sub
</script>
<html>
<head>
<title>Sample add/edit/delete datagrid SQL Server</title>
</head>
<body>
<form id="Form1" method="post" runat="server">
<br />
<div style="VERTICAL-ALIGN: top; OVERFLOW: auto; POSITION: relative; HEIGHT: 300px" align="left" width="70%">
<asp:datagrid id="dgProducts" runat="server" width="100%" OnSortCommand="Datasort" AllowSorting="True" CellPadding="5" AutoGenerateColumns="false" OnEditCommand="EditRecord" OnCancelCommand="CancelEdit" OnUpdateCommand="Updaterecord" DataKeyField="ProductID" ondeletecommand="DeleteRecord" onitemcreated="dg_itemcreated">
<HeaderStyle backcolor="Silver"></HeaderStyle>
<Columns>
<asp:BoundColumn ItemStyle-Width="100px" visible="false" DataField="productid" SortExpression="ProductID" ReadOnly="True"></asp:BoundColumn>
<asp:BoundColumn ItemStyle-Width="400px" DataField="ProductName" HeaderText="Name" SortExpression="ProductName"></asp:BoundColumn>
<asp:BoundColumn ItemStyle-Width="100px" DataField="unitprice" DataFormatString="{0:c}" SortExpression="UnitPrice" HeaderText="Price"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Save" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:ButtonColumn Text="Delete" CommandName="Delete"></asp:ButtonColumn>
</Columns>
</asp:datagrid>
</div>
</form>
</body>
</html>
___
Newsgroups brought to you courtesy of www.dotnetjohn.com
<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Drawing" %>
<script runat="server">
Private strConnection As String = "server=localhost;uid=sa;pwd=password;database=Northwind"
Private strSQLSelect As String = "SELECT * from Products"
Private ProductTableName As String = "Products"
Private objConnection As SqlConnection
' Important: Table must have a PRIMARY KEY. In this instance, it is PRODUCTID
' Cells are coded with column position in the datagrid counting from 0.
' ========================================
Private Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
If Not IsPostBack Then
LoadGrid()
End If
End Sub
' ========================================
Private Sub LoadGrid()
Connect()
'
Dim sql as string
If viewstate("sortfield") Is Nothing Then
sql = strSQLSelect
Else
sql = strSQLSelect & " order by " & viewstate("sortfield") & " " & viewstate("sortdirection")
End If
Dim adapter As New SqlDataAdapter(sql, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
'ok let's get fancy and insert a blank row at the top
dim sw1 as integer = 1
select case sw1
case 1
dim dr as datarow = ds.Tables(0).newrow()
'put something in the first primary key CustomerID
'dr("Productname") = " (Add new Product name here)"
ds.Tables(0).rows.insertat(dr, 0)
end select
dgProducts.DataSource = ds.Tables(ProductTableName)
dgProducts.DataBind()
End Sub
' ========================================
Private Sub Connect()
If objConnection Is Nothing Then
objConnection = New SqlConnection(strConnection)
End If
If objConnection.State = ConnectionState.Closed Then
objConnection.Open()
End If
End Sub
Private Sub Disconnect()
objConnection.Close()
End Sub
' ========================================
Public Sub EditRecord(ByVal Sender As Object, _
ByVal E As DataGridCommandEventArgs)
dgProducts.EditItemIndex = E.Item.ItemIndex
LoadGrid()
End Sub
' ========================================
Public Sub CancelEdit(ByVal Sender As Object, _
ByVal E As DataGridCommandEventArgs)
dgProducts.EditItemIndex = -1
LoadGrid()
End Sub
' ========================================
Public Sub UpdateRecord(ByVal Sender As Object, _
ByVal E As DataGridCommandEventArgs)
Dim NameTextBox As TextBox = CType(E.Item.Cells(1).Controls(0), TextBox)
Dim Name As string = Convert.tostring(NameTextBox.Text)
Dim PriceTextBox As TextBox = CType(E.Item.Cells(2).Controls(0), TextBox)
Dim Price As string = Convert.tostring(PriceTextBox.Text)
' Create and load a DataSet with records from Northwind.Products table
Connect()
Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
dim row as datarow
Dim currentRow As Integer = e.Item.DataSetIndex
'ADD NEW
if currentrow = 0 then
row = ds.tables(0).newrow()
row.Item("ProductName") = Name
row.item("Unitprice")= Price
ds.tables(0).rows.insertat(row, 0 )
else
'UPDATING EXISTING RECORD
' Retrieve the field values in the edited row
Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)
' Modify the in-memory records in the DataSet
Dim tbl As DataTable = ds.Tables(ProductTableName)
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("ProductID") _
}
row = tbl.Rows.Find(ProductID)
row.Item("ProductName") = Name
row.item("Unitprice")= Price
End if
' Reconnect the DataSet and update the database
Dim cb As New SqlCommandBuilder(adapter)
Connect()
adapter.Update(ds, ProductTableName)
Disconnect()
dgProducts.DataSource = ds.Tables(ProductTableName)
dgProducts.DataBind()
dgProducts.EditItemIndex = -1
Loadgrid()
End Sub
' ========================================
Sub DeleteRecord(Sender As Object, E As DataGridCommandEventArgs)
' Retrieve the field values in the edited row
Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)
' Create and load a DataSet with records from Northwind.Products table
Connect()
Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
' Modify the in-memory records in the DataSet
Dim tbl As DataTable = ds.Tables(ProductTableName)
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("ProductID") _
}
Dim row As DataRow = tbl.Rows.Find(ProductID)
row.Delete()
' Reconnect the DataSet and update the database
Dim cb As New SqlCommandBuilder(adapter)
Connect()
adapter.Update(ds, ProductTableName)
Disconnect()
dgProducts.DataSource = ds.Tables(ProductTableName)
dgProducts.DataBind()
dgProducts.EditItemIndex = -1
Loadgrid()
End Sub
' ===================================
public sub dg_itemcreated ( sender as object, e as datagriditemeventargs )
'
' Clear column headers for col 3 and col 4 col3 + edit col 4 = delete
If e.Item.ItemType = ListItemType.Header Then
e.Item.Cells(3).Backcolor = Color.White
e.Item.Cells(4).Backcolor = Color.White
end If
'-- add logic to modify text literal EDIT and Suppress the Delete
if e.item.itemindex = 0 then 'first row
'reference the AddNEW row
e.Item.BackColor = Color.gold
e.Item.Cells(4).Backcolor = Color.White
dim lbDelete as linkbutton = e.item.cells(4).controls(0)
dim lbEdit as linkbutton = e.item.cells(3).controls(0)
if lbDelete.text = "Delete" then
lbDelete.text = ""
end if
if lbEdit.text = "Edit" then
lbEdit.text = "Add New"
end if
if lbEdit.text = "Save" then
lbEdit.text = "Save"
end if
end if
if e.item.itemindex > 0 then
dim mydeletebutton as tablecell
mydeletebutton = e.item.cells(4)
mydeletebutton.attributes.add("onclick", _
"return confirm('Are you sure you want to delete this record?');" )
end if
end sub
' ========================================
Sub DataSort(Src As Object, E As DataGridSortCommandEventArgs)
viewstate.Add("sortfield", e.SortExpression)
If viewstate("sortdirection") Is Nothing Then
viewstate.Add("sortdirection", "ASC")
Else
If viewstate("sortdirection") = "ASC" Then
viewstate("sortdirection") = "DESC"
Else
viewstate("sortdirection") = "ASC"
End If
End If
LoadGrid()
End Sub
</script>
<html>
<head>
<title>Sample add/edit/delete datagrid SQL Server</title>
</head>
<body>
<form id="Form1" method="post" runat="server">
<br />
<div style="VERTICAL-ALIGN: top; OVERFLOW: auto; POSITION: relative; HEIGHT: 300px" align="left" width="70%">
<asp:datagrid id="dgProducts" runat="server" width="100%" OnSortCommand="Datasort" AllowSorting="True" CellPadding="5" AutoGenerateColumns="false" OnEditCommand="EditRecord" OnCancelCommand="CancelEdit" OnUpdateCommand="Updaterecord" DataKeyField="ProductID" ondeletecommand="DeleteRecord" onitemcreated="dg_itemcreated">
<HeaderStyle backcolor="Silver"></HeaderStyle>
<Columns>
<asp:BoundColumn ItemStyle-Width="100px" visible="false" DataField="productid" SortExpression="ProductID" ReadOnly="True"></asp:BoundColumn>
<asp:BoundColumn ItemStyle-Width="400px" DataField="ProductName" HeaderText="Name" SortExpression="ProductName"></asp:BoundColumn>
<asp:BoundColumn ItemStyle-Width="100px" DataField="unitprice" DataFormatString="{0:c}" SortExpression="UnitPrice" HeaderText="Price"></asp:BoundColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Save" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:ButtonColumn Text="Delete" CommandName="Delete"></asp:ButtonColumn>
</Columns>
</asp:datagrid>
</div>
</form>
</body>
</html>
___
Newsgroups brought to you courtesy of www.dotnetjohn.com