Resolved - Datagrid to edit Database
The following is sample code that I found. The code only works if you are Using only one table. I want to be able to list data from multiple tables and edit one column accordingly. In this case the Dynamic SQL generator cannot be used. Can you please share an example of what I need to do in order to build the update command manually.
***********Code Starts Here**********
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>
<html>
<head>
<title>Updateing Beverages</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form method="post" runat="server">
<asp
ataGrid ID="dgProducts" runat="server"
AutoGenerateColumns="false"
CellPadding="5"
showHeader="true"
OnUpdateCommand="Updaterecord"
OnEditCommand="EditRecord"
OnCancelCommand="CancelEdit">
<headerstyle BackColor="#C0C0FF" />
<itemstyle BackColor="#F1f1f1" />
<alternatingitemstyle BackColor="#e8e6e6" />
<columns>
<asp:BoundColumn DataField="ProductID" ReadOnly="true" Visible="False" />
<asp:BoundColumn DataField="ProductName" ReadOnly="true" HeaderText="Name" />
<asp:BoundColumn DataField="UnitPrice" HeaderText="Price" />
<asp:BoundColumn DataField="SupplierID" ReadOnly="true" HeaderText="Supplier" />
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Save"
CancelText="Cancel"
EditText="Edit" />
</columns>
</aspataGrid>
</form>
</body>
</html>
<script language="vb" runat="server">
Private strConnection As String = ConfigurationSettings.AppSettings("Local_NWind")
Private strSQLSelect As String = "select p.UnitPrice, p.ProductID, p.ProductName, s.SupplierID " & _
" from Products p left outer join Suppliers s ON (p.SupplierID = s.SupplierID)"
Private ProductTableName As String = "ProductTable"
Private objConnection As SqlConnection
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 adapter As New SqlDataAdapter(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
With dgProducts
.DataSource = ds.Tables(ProductTableName)
.DataBind()
End With
End Sub
Private Sub Connect()
If objConnection Is Nothing Then
objConnection = New SqlConnection(strConnection)
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)
'Retrieve the field values in the edited row
Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)
Dim PriceTextBox As TextBox = Ctype(E.Item.Cells(2).Controls(0), TextBox)
Dim Price As Decimal = Convert.ToDecimal(PriceTextBox.Text)
dgProducts.EditItemIndex = -1
UpdateProduct(ProductID, Price)
End Sub
Private Sub UpdateProduct(ByVal ProductID As Long, ByVal Price As Decimal)
'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.Item("UnitPrice") = Price
'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()
End Sub
</script>
The following is sample code that I found. The code only works if you are Using only one table. I want to be able to list data from multiple tables and edit one column accordingly. In this case the Dynamic SQL generator cannot be used. Can you please share an example of what I need to do in order to build the update command manually.
***********Code Starts Here**********
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>
<html>
<head>
<title>Updateing Beverages</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form method="post" runat="server">
<asp
ataGrid ID="dgProducts" runat="server" AutoGenerateColumns="false"
CellPadding="5"
showHeader="true"
OnUpdateCommand="Updaterecord"
OnEditCommand="EditRecord"
OnCancelCommand="CancelEdit">
<headerstyle BackColor="#C0C0FF" />
<itemstyle BackColor="#F1f1f1" />
<alternatingitemstyle BackColor="#e8e6e6" />
<columns>
<asp:BoundColumn DataField="ProductID" ReadOnly="true" Visible="False" />
<asp:BoundColumn DataField="ProductName" ReadOnly="true" HeaderText="Name" />
<asp:BoundColumn DataField="UnitPrice" HeaderText="Price" />
<asp:BoundColumn DataField="SupplierID" ReadOnly="true" HeaderText="Supplier" />
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Save"
CancelText="Cancel"
EditText="Edit" />
</columns>
</asp
ataGrid></form>
</body>
</html>
<script language="vb" runat="server">
Private strConnection As String = ConfigurationSettings.AppSettings("Local_NWind")
Private strSQLSelect As String = "select p.UnitPrice, p.ProductID, p.ProductName, s.SupplierID " & _
" from Products p left outer join Suppliers s ON (p.SupplierID = s.SupplierID)"
Private ProductTableName As String = "ProductTable"
Private objConnection As SqlConnection
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 adapter As New SqlDataAdapter(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds, ProductTableName)
Disconnect()
With dgProducts
.DataSource = ds.Tables(ProductTableName)
.DataBind()
End With
End Sub
Private Sub Connect()
If objConnection Is Nothing Then
objConnection = New SqlConnection(strConnection)
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)
'Retrieve the field values in the edited row
Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)
Dim PriceTextBox As TextBox = Ctype(E.Item.Cells(2).Controls(0), TextBox)
Dim Price As Decimal = Convert.ToDecimal(PriceTextBox.Text)
dgProducts.EditItemIndex = -1
UpdateProduct(ProductID, Price)
End Sub
Private Sub UpdateProduct(ByVal ProductID As Long, ByVal Price As Decimal)
'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.Item("UnitPrice") = Price
'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()
End Sub
</script>
Last edited:
