Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
|
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 3rd, 2007, 09:38 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default Help with creating ASP UPDATE Statement

One of the hardest things for me to learn in the WROX series is the various ways of implementing DB connectivity in ASP. One book approached it via writing simple code, the ASP & DB book (beginners) dables in this a little--but relies alot on SQLDataSource.

What I am trying to do is implement my own object and then tie it to the DataSource of my form.

What I am unclear on is the various variables I have to create to do this in my own function (for example, reader/adapter/command/etc). Could someone help me in what I would need to finish (or change) the function I started below so as to create a function that would UPDATE a table called Member.

Also--is this the right way to set up my parameters? Basically the Uname (first variable) is my key..the rest are what I am wanting to change--is there something I need to do special with the form DataSource?

UPDATE FUNCTION:
Public Sub UpdateMember(ByVal uName As String, ByVal email As String, ByVal address As String, ByVal city As String, ByVal state As String, ByVal zip As String, ByVal phone As String, ByVal cell As String, ByVal role As String)
        Dim knightsDBConn As New SqlConnection(conString)
        Dim sqlString As String = "UPDATE Members SET email = '" & email & "', phone = '" & phone & "', cell = '" & cell & "', address = '" & address & "', city = '" & city & "', state = '" & state & "', zip = '" & zip & "', role = '" & role & "' WHERE userName = '" & uName & "'"
        Dim sqlCmd As New SqlCommand(sqlString, knightsDBConn)
        ????
    End Sub

SELECT FUNCTION
Public Shared Function GetMember(ByVal uName As String) As SqlDataReader
        Dim knightsDBConn As New SqlConnection(conString)
        Dim sqlString As String = "SELECT * FROM Members WHERE userName = '" & uName & "'"
        Dim sqlCmd As New SqlCommand(sqlString, knightsDBConn)
        knightsDBConn.Open()
        Dim memberSQLDR As SqlDataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnecti on)
        sqlCmd = Nothing
        Return memberSQLDR
    End Function

Thanks SOOO Much,
Rob

(My select works, but in this one book, it said that if I want to be able to sort, etc, I want to use a DataSet instead of Reader? Would this function not work then **below**)



 
Old January 3rd, 2007, 10:14 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Ok...I think all I had to do was add one line to the update:

Public Sub UpdateMember(ByVal uName As String, ByVal email As String, ByVal address As String, ByVal city As String, ByVal state As String, ByVal zip As String, ByVal phone As String, ByVal cell As String, ByVal role As String)
        Dim knightsDBConn As New SqlConnection(conString)
        Dim sqlString As String = "UPDATE Members SET email = '" & email & "', phone = '" & phone & "', cell = '" & cell & "', address = '" & address & "', city = '" & city & "', state = '" & state & "', zip = '" & zip & "', role = '" & role & "' WHERE userName = '" & uName & "'"
        Dim sqlCmd As New SqlCommand(sqlString, knightsDBConn)
        sqlCmd.ExecuteNonQuery()
    End Sub

My question now is that I went into the form and configured datasource so that the UpdateMethod points to my function above. It therefore creates the code below...but how do I relate the parameters to the fields in the form?

<asp:ObjectDataSource ID="knightsDataSource" runat="server" SelectMethod="GetMember" TypeName="Member" UpdateMethod="UpdateMember">
    <SelectParameters>
        <asp:QueryStringParameter Name="uName" QueryStringField="id" Type="String" />
    </SelectParameters>
    <UpdateParameters>
        <asp:Parameter Name="uName" Type="String"/>
        <asp:Parameter Name="email" Type="String" />
        <asp:Parameter Name="address" Type="String" />
        <asp:Parameter Name="city" Type="String" />
        <asp:Parameter Name="state" Type="String" />
        <asp:Parameter Name="zip" Type="String" />
        <asp:Parameter Name="phone" Type="String" />
        <asp:Parameter Name="cell" Type="String" />
        <asp:Parameter Name="role" Type="String" />
    </UpdateParameters>
</asp:ObjectDataSource>

 
Old January 3rd, 2007, 10:31 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Sigh--now I'm confused. I'm trying to test this - thinking that as long as the parameters equal the databound items in the form, I should be ok--but not sure if that is how it works. Just having a hard time understanding the relation between the form field, the parameter in the ASP Update statement and the parameter in the update function in my Member Class.

Ok--I put all the data in the edititemtemplate and set default to edit...but I don't see an update button?

..so I put my own button and put CommandName as "UPDATE" but I get an error (It's not seeing the update method I wrote in my datasource--see code below)

No parameterless constructor defined for this object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.MissingMethodException: No parameterless constructor defined for this object.


<asp:ObjectDataSource ID="knightsDataSource" runat="server" SelectMethod="GetMember" TypeName="Member" UpdateMethod="UpdateMember">
    <SelectParameters>
        <asp:QueryStringParameter Name="uName" QueryStringField="id" Type="String" />
    </SelectParameters>
    <UpdateParameters>
        <asp:Parameter Name="uName" Type="String"/>
        <asp:Parameter Name="email" Type="String" />
        <asp:Parameter Name="address" Type="String" />
        <asp:Parameter Name="city" Type="String" />
        <asp:Parameter Name="state" Type="String" />
        <asp:Parameter Name="zip" Type="String" />
        <asp:Parameter Name="home_phone" Type="String" />
        <asp:Parameter Name="cell_phone" Type="String" />
    </UpdateParameters>
</asp:ObjectDataSource>

Source Error:
 
Old January 3rd, 2007, 11:23 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Ok..Im lost..getting Server Error in '/Knights Site' Application.
--------------------------------------------------------------------------------

ObjectDataSource 'knightsDataSource' could not find a non-generic method 'UpdateMember' that has parameters: userName, email, address, city, state, zip, home_phone, cell_phone, password.


Man..almost to the point where I'd pay money. I've been spending hours...I know its probably something simple....here's my entire code:

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="editmember.aspx.vb" Inherits="Admin_editmember" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table id="addcontainer">
<tr>
<td id="listmem" valign="top">
<div class="fnt" align="center">Members</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" ShowHeader="False" BorderStyle="None" BorderWidth="0px">
    <Columns>
        <asp:BoundField DataField="fname"/>
        <asp:BoundField DataField="lname"/>
        <asp:HyperLinkField DataNavigateUrlFields="userName" DataNavigateUrlFormatString="~/Admin/editmember.aspx?id={0}" DataTextFormatString="{0}" Text="Edit" />
    </Columns>
    </asp:GridView>
    <br />
    <div class="centerfont">
    <asp:LinkButton ID="LinkButton1" runat="server" PostBackUrl="~/Admin/addmember.aspx">Go Back To Create User</asp:LinkButton>
    </div>
</td>
<td id="addmem" align="center" valign="top">
                    <asp:FormView ID="FormView1" runat="server" DataSourceID="knightsDataSource" DefaultMode="Edit">

                        <EditItemTemplate>
                            <table border="0" id="TABLE1">
                        <tr>
                            <td align="center" colspan="2" class="fnt">
                                Edit Account</td>
                        </tr>
                        <tr>
                            <td align="right" style="height: 25px">
                                <asp:Label ID="UserNameLabel" runat="server">User Name:</asp:Label></td>
                            <td style="height: 25px">
                                <asp:Label ID="UserNameLabelReturn" cssClass="boldfont" runat="server" Text='<%# Eval("userName") %>' ></asp:Label>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="ConfirmFirstNameLabel" runat="server">First Name:</asp:Label></td>
                            <td>
                                <asp:Label ID="FirstNameLabelReturn" runat="server" cssClass="boldfont" Text='<%# Eval("fName") %>'></asp:Label>
                            </td>
                        </tr>
                        <tr>
                            <td align="right" style="height: 26px">
                                <asp:Label ID="ConfirmLastNameLabel" runat="server">Last Name:</asp:Label></td>
                            <td style="height: 26px">
                                <asp:Label ID="LastNameLabelReturn" cssClass="boldfont" runat="server" Text='<%# Eval("lName") %>'></asp:Label>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="passwordLabel" runat="server">Password:</asp:Label></td>
                            <td style="height: 26px">
                                <asp:TextBox ID="PasswordTextBox" runat="server" Text ='<%# Bind("password") %>'></asp:TextBox>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="EmailLabel" runat="server">E-mail:</asp:Label></td>
                            <td>
                                <asp:TextBox ID="EmailTextBoxReturn" runat="server" Text='<%# Bind("email") %>'></asp:TextBox>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="AddressLabel" runat="server">Street Address:</asp:Label></td>
                            <td>
                                <asp:TextBox ID="AddressTextBoxReturn" runat="server" Text='<%# Bind("address") %>'></asp:TextBox>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="cityLabel" runat="server">City:</asp:Label></td>
                            <td>
                                <asp:TextBox ID="CityTextBoxReturn" runat="server" Text='<%# Bind("city") %>'></asp:TextBox>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="stateLabel" runat="server">State:</asp:Label></td>
                            <td>
                                <asp:TextBox ID="StateTextBoxReturn" runat="server" Text='<%# Bind("state") %>'></asp:TextBox>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="zipLabel" runat="server">Zip:</asp:Label></td>
                            <td>
                                <asp:TextBox ID="ZipTextBoxReturn" runat="server" Text='<%# Bind("zip") %>'></asp:TextBox>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="phoneLabel" runat="server">Home Phone:</asp:Label></td>
                            <td>
                                <asp:TextBox ID="PhoneTextBoxReturn" runat="server" Text='<%# Bind("home_phone") %>'></asp:TextBox>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                <asp:Label ID="cellLabel" runat="Server">Cell Phone:</asp:Label></td>
                            <td>
                                <asp:TextBox ID="CellTextBoxReturn" runat="server" Text='<%# Bind("cell_phone") %>'></asp:TextBox>
                            </td>
                        </tr>

                        <tr>
                            <td align="right">
                                <asp:Label ID="RoleLabel" runat="Server">Role For User:</asp:Label></td>
                            <td>
                                <asp:DropDownList ID="RoleDrop" runat="server">
                                </asp:DropDownList>
                            </td>
                        </tr>
                        <tr>
                            <td>
                                <asp:Button ID="update" runat="server" Text="Update" CommandName="Update" />
                            </td>
                        </tr>
                    </table>
                        </EditItemTemplate>

                        </asp:FormView>
</td>
</tr>
</table>
<asp:ObjectDataSource ID="knightsDataSource" runat="server" SelectMethod="GetMember" TypeName="Member" UpdateMethod="UpdateMember">
    <SelectParameters>
        <asp:QueryStringParameter Name="uName" QueryStringField="id" Type="String" />
    </SelectParameters>
    <UpdateParameters>
        <asp:Parameter Name="userName" Type="String"/>
        <asp:Parameter Name="email" Type="String" />
        <asp:Parameter Name="address" Type="String" />
        <asp:Parameter Name="city" Type="String" />
        <asp:Parameter Name="state" Type="String" />
        <asp:Parameter Name="zip" Type="String" />
        <asp:Parameter Name="home_phone" Type="String" />
        <asp:Parameter Name="cell_phone" Type="String" />
        <asp:Parameter Name="password" Type="String" />
    </UpdateParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllMembers" TypeName="Member"></asp:ObjectDataSource>
</asp:Content>



 
Old January 4th, 2007, 03:23 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Rob,

The error message is staring you right in the face:
Quote:
quote:ObjectDataSource 'knightsDataSource' could not find a non-generic method 'UpdateMember' that has parameters: userName, email, address, city, state, zip, home_phone, cell_phone, password.
It says it can't find a method with a parameter called userName, email etc. It gets these names from the <UpdateParameters>:
Code:
<UpdateParameters>
  <asp:Parameter Name="userName" Type="String"/>
  <asp:Parameter Name="email" Type="String" />
  <asp:Parameter Name="address" Type="String" />
</UpdateParameters>
However if you look at your method, you have this:
Code:
Public Sub UpdateMember(ByVal uName As String, ByVal email As String, 
ByVal address As String, ByVal city As String, ByVal state As String, 
ByVal zip As String, ByVal phone As String, ByVal cell As String, 
ByVal role As String)
There's quite a mismatch between the <UpdateParameters> and the actual parameter names of your UpdateMember method. All of them need to match....

You could also decide to have the UpdateMember accept an actual Member, like this:

Public Sub UpdateMember(myMember As Member)
...
End Sub

Then inside this Sub you can access member properties like you normally would: myMember.PropertyName.

You may want to take another look at the Instant Results book; especially chapter 12 the BugBase. Inside the file BugManagerDB.vb in the DataAccess folder you find the exact same code you're trying to write; the only difference is that it's for a Bug object, not for a Member....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.





Similar Threads
Thread Thread Starter Forum Replies Last Post
update multiple columns in an update statement debbiecoates SQL Server 2000 1 August 17th, 2008 04:01 AM
UPDATE statement takwirira ASP.NET 2.0 Basics 7 March 7th, 2007 07:48 PM
Newbie needs help creating Select statement! Mintyman SQL Language 4 November 13th, 2006 01:11 PM
Update Statement some error thas123 ADO.NET 1 January 8th, 2006 10:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.