Dear all,

This is in regards with book: "Beginning ASP.NET Databases Using Vb.NET", chapter 7, Try out erxercise pp-240 to 246. Last step (13) on page 245 gives me a headache. I am trying for 2 days to find what is the problem with the code in this exercise but can't find it. If we type the exzact entire code and run it, the debugger points to the line before last of the UpdateRecord() private sub. It would appear that the ds is not declared in the following line:

dgProducts.DataSource = ds.Tables(ProductTableName)

My UpdateRecord() and UpdateProduct() sub are exactly so as stated in the exercise. The code compiles up until these last two subs.

Would you gys be kind enough to check this code and let me know if the book is correct, of if it is me that can't see something fundamental.

Thanks in advance!
Christiane

Is 'ds' declared? Can you post your code?

Hello,

Thanks for the prompt reply. The original code is the one of the book, as mentionned.

I have deleted the last 2 lines (with regards to DataBinding on the UpdateRecord() sub) contrary to what is suggested in the book. See my comments in the code. When I did, the code does not compile again: Apparently SqlCommandBuilder cannot generate the Update SQL statement, for what I can see.

Hier it is the code, and as we say in The Netherlands:

Alvast bedankt!
Christiane
---------------------------------------------
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

    Private strConnection as string = "server=(local); database=Northwind; " & _
         "integrated security = true; "
    Private strSQLSelect as string = "SELECT ProductID, ProductName, UnitPrice " & _
         " FROM Products WHERE CategoryID = 1"
    Private ProductTableName as string = "ProductName"
    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, strConnection)
             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)
             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, E as DataGridCommandEventArgs)
            dgProducts.EditItemIndex = E.item.ItemIndex
            LoadGrid()
         End sub



         Public sub CancelEdit(ByVal Sender as object, E as DataGridCommandEventArgs)
            'load grid without the edit textbox control...
            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)
            'Note that control Editbox is only possible if HTML field NOT ReadOnly
            Dim PriceTextBox As TextBox = CType(E.Item.Cells(2).Controls(0), TextBox)
            Dim Price As Decimal = Convert.ToDecimal(PriceTextBox.Text)

            'load grid without the edit textbox control...
            dgProducts.EditItemIndex = -1
            'Pass the ProductID of the right record, and entered Price value
            'as parameter for the UpdateProduct() sub
            UpdateProduct(ProductID, Price)


            'foutmelding over "ds" not being declared:
            dgProducts.DataSource = ds.Tables(ProductTableName)
            dgProducts.DataBind()

       End sub


         'foutmelding with next procedure, if I delete last 2 lines of UpdateRecord,
         'of if I replace them with LoadGrid():
         'Dynamic SQL generation for the UpdateCommand is not
         'supported against a SelectCommand that does not return any key column information
        Public sub UpdateProduct(ByVal ProductID As Long, ByVal Price As Decimal)
            'Load a dataset with records in DB (before the updating takes place)
            Connect()
            Dim da As New SqlDataAdapter(strSQLSelect, strConnection)
            Dim ds As New DataSet()
            da.Fill(ds, ProductTableName)
            Disconnect()


            'modify the UnitPrice of in-memory dataset...
            Dim tempTable as DataTable = ds.Tables(ProductTableName)
            'Define primary key to identify record
            tempTable.PrimaryKey = New DataColumn() _
                                   { _
                                        tempTable.Columns("ProductID") _
                                   }

            'Find the right row in tempTable using primary key...
            Dim row As DataRow = tempTable.Rows.Find(ProductID)
            row.Item("UnitPrice") = Price


            'Reconnect to DB for update van DB
            Dim cb As New SqlCommandBuilder(da)
            Connect()
            da.Update(ds, ProductTableName)
            Disconnect()
         End sub

</script>
<html>
<head>
    <title>Update a price</title>
</head>
<body>
    <form method="post" runat="server">
        <asp:DataGrid id="dgProducts" runat="server" AutoGenerateColumns="False" CellPadding="5" OnEditCommand="EditRecord" OnCancelCommand="CancelEdit" OnUpdateCommand="UpdateRecord">
            <Columns>
                <asp:BoundColumn DataField="ProductID" ReadOnly="true" Visible="False" />
                <asp:BoundColumn DataField="ProductName" ReadOnly="true" HeaderText="Name" />
                <asp:BoundColumn DataField="UnitPrice" HeaderText="Price" />
                <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Save" CancelText="Cancel" EditText="Edit" />
            </Columns>
        </asp:DataGrid>
    </form>
</body>
</html>