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**)

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>

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:

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>

Hi Rob,

The error message is staring you right in the face: It says it can't find a method with a parameter called userName, email etc. It gets these names from the <UpdateParameters>: However if you look at your method, you have this: 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.