Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > BOOK: Beginning ASP.NET Databases
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning ASP.NET Databases Also see the forum ASP Databases for more general discussions of ASP database issues not directly related to these books.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old January 30th, 2005, 06:57 PM
Registered User
Join Date: Jan 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating SQL record using DataSEt

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!

Reply With Quote
  #2 (permalink)  
Old January 31st, 2005, 09:07 AM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,111
Thanks: 0
Thanked 3 Times in 3 Posts

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

Reply With Quote
  #3 (permalink)  
Old January 31st, 2005, 01:26 PM
Registered User
Join Date: Jan 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts


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!
<%@ 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
             End if
         End sub

         Private Sub LoadGrid()
             Dim adapter As New SqlDataAdapter(strSQLSelect, strConnection)
             Dim ds As New DataSet()
             adapter.Fill(ds, ProductTableName)

             With dgProducts
                 .DataSource = ds.Tables(ProductTableName)
             End With
         end sub

         Private sub Connect()
             If objConnection Is Nothing Then
                 objConnection = New SqlConnection(strConnection)
             End if

             If objConnection.State = ConnectionState.Closed Then
             End if
        End sub

         Private sub Disconnect()
         End sub

         Public sub EditRecord(ByVal Sender as object, E as DataGridCommandEventArgs)
            dgProducts.EditItemIndex = E.item.ItemIndex
         End sub

         Public sub CancelEdit(ByVal Sender as object, E as DataGridCommandEventArgs)
            'load grid without the edit textbox control...
            dgProducts.EditItemIndex = -1
         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)

       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)
            Dim da As New SqlDataAdapter(strSQLSelect, strConnection)
            Dim ds As New DataSet()
            da.Fill(ds, ProductTableName)

            '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)
            da.Update(ds, ProductTableName)
         End sub

    <title>Update a price</title>
    <form method="post" runat="server">
        <asp:DataGrid id="dgProducts" runat="server" AutoGenerateColumns="False" CellPadding="5" OnEditCommand="EditRecord" OnCancelCommand="CancelEdit" OnUpdateCommand="UpdateRecord">
                <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" />

Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating Database from Dataset hexOffender VB Databases Basics 0 October 30th, 2006 12:40 PM
updating a dataset hexOffender VB.NET 2002/2003 Basics 1 September 28th, 2006 06:13 PM
how to add new record as first record in dataset hunzian78@yahoo.com ASP.NET 1.0 and 1.1 Professional 4 April 21st, 2006 06:23 AM
problem in updating in dataset vohra_vikas2004 ADO.NET 1 June 28th, 2005 06:26 AM
Updating MS SQL 2000 with C# Dataset paulhopkins ADO.NET 1 March 2nd, 2005 05:15 PM

All times are GMT -4. The time now is 07:18 PM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.