Gridview and Dropdown - binding?

Discussion in 'ASP .Net Web Controls' started by Danny, Nov 28, 2007.

  1. Danny

    Danny Guest

    Hi Group,

    I am working on a simple user administration system based on VB ASP.NET and
    an Access database. The database contains the folowing data:

    Users:
    UserID, f_UserTypeID, Name, Email
    1, 1, Pat,
    2, 1, Joe,
    3, 2, Tim,

    UserType:
    UserTypeID, UserType
    1, Administrator
    2, User

    As seen here, key "UserTypeID" from table UserType is used as foreign key
    "f_UserTypeID" in table Users. Being an administrator it should be possible
    to alter the data in table Users. All data are represented using a GridView
    and a SqlDataSource with the following SQL statement:

    SELECT Users.*, UserType.* FROM (Users INNER JOIN UserType ON
    Users.f_UserTypeID = UserType.UserTypeID)

    When altering for example Tim's data, it should be possible to choose
    whether he should be an Administrator or a User using a dropdown box.
    Therefore, in the design, I have changed the column "UserType" to
    TemplateField. In TemplateField -> EditItemTemplate I have placed the
    dropdown box.

    To update Tim's data in the database, I have defined the following SQL
    statement

    UPDATE [Users] SET [f_UserTypeID] = @UserType, [Name] = @Name, =
    @Email WHERE [UserID] = @UserID

    My problem is that I cannot figure out the right way to bind the controls,
    so the data get updated. Using the dropdown box, where the Index field is
    related to "UserTypeID" and the Value field is related to "UserType", I
    would like to be able to update "F_UserTypeID" in the database.

    How am I supposed to do that?

    /Danny
    Danny, Nov 28, 2007
    #1
    1. Advertising

  2. Danny

    Phil H Guest

    On 28 Nov, 14:50, "Danny" <> wrote:
    > Hi Group,
    >
    > I am working on a simple user administration system based on VB ASP.NET and
    > an Access database. The database contains the folowing data:
    >
    > Users:
    > UserID, f_UserTypeID, Name, Email
    > 1, 1, Pat,
    > 2, 1, Joe,
    > 3, 2, Tim,
    >
    > UserType:
    > UserTypeID, UserType
    > 1, Administrator
    > 2, User
    >
    > As seen here, key "UserTypeID" from table UserType is used as foreign key
    > "f_UserTypeID" in table Users. Being an administrator it should be possible
    > to alter the data in table Users. All data are represented using a GridView
    > and a SqlDataSource with the following SQL statement:
    >
    > SELECT Users.*, UserType.* FROM (Users INNER JOIN UserType ON
    > Users.f_UserTypeID = UserType.UserTypeID)
    >
    > When altering for example Tim's data, it should be possible to choose
    > whether he should be an Administrator or a User using a dropdown box.
    > Therefore, in the design, I have changed the column "UserType" to
    > TemplateField. In TemplateField -> EditItemTemplate I have placed the
    > dropdown box.
    >
    > To update Tim's data in the database, I have defined the following SQL
    > statement
    >
    > UPDATE [Users] SET [f_UserTypeID] = @UserType, [Name] = @Name, =
    > @Email WHERE [UserID] = @UserID
    >
    > My problem is that I cannot figure out the right way to bind the controls,
    > so the data get updated. Using the dropdown box, where the Index field is
    > related to "UserTypeID" and the Value field is related to "UserType", I
    > would like to be able to update "F_UserTypeID" in the database.
    >
    > How am I supposed to do that?
    >
    > /Danny[/color]

    First thing I notice is that you cannot use an SqlDataSource with an
    Access database. You need an AccessDataSource.

    Second thing is that you cannot use the GridView Datasource to
    populate the items list of the DropDownList. You need to consider how
    that will be done. For the automatic update you could possibly bind
    the SelectedItemIndex or the SelectedValue to the f_UsertypeID column
    but that might throw up problems of event sequencing and the state of
    the control during the DataBind event.

    Best to do it all manually in code during the DataBound and
    ItemUpdating events. If you tie the @UserType parameter to a Session
    variable you can pass the SelectedValue that way as part of the
    ItemUpdating event code.
    Phil H, Nov 28, 2007
    #2
    1. Advertising

  3. Thanks for Phil's input.

    Hi Danny,

    I agree with Phil that for your scenario, since the select data record set
    are from two joined table, it it not quite suitable to use automatic update
    here. Automatic generated update will work well for a single table or
    updatable view scenario. You may consider hook those updating events of the
    GridView and manually do the update (to the two tables with the proper
    parameters)

    BTW, for AccessDataBase, we can use "SqlDataSource" to connect it as long
    as the correct OLEDB provider is choosed:)

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead



    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------
    >From: Phil H <>
    >Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols
    >Subject: Re: Gridview and Dropdown - binding?
    >Date: Wed, 28 Nov 2007 10:10:10 -0800 (PST)
    >
    >On 28 Nov, 14:50, "Danny" <> wrote:
    >> Hi Group,
    >>
    >> I am working on a simple user administration system based on VB ASP.NET

    and
    >> an Access database. The database contains the folowing data:
    >>
    >> Users:
    >> UserID, f_UserTypeID, Name, Email
    >> 1, 1, Pat,
    >> 2, 1, Joe,
    >> 3, 2, Tim,
    >>
    >> UserType:
    >> UserTypeID, UserType
    >> 1, Administrator
    >> 2, User
    >>
    >> As seen here, key "UserTypeID" from table UserType is used as foreign key
    >> "f_UserTypeID" in table Users. Being an administrator it should be

    possible
    >> to alter the data in table Users. All data are represented using a

    GridView
    >> and a SqlDataSource with the following SQL statement:
    >>
    >> SELECT Users.*, UserType.* FROM (Users INNER JOIN UserType ON
    >> Users.f_UserTypeID = UserType.UserTypeID)
    >>
    >> When altering for example Tim's data, it should be possible to choose
    >> whether he should be an Administrator or a User using a dropdown box.
    >> Therefore, in the design, I have changed the column "UserType" to
    >> TemplateField. In TemplateField -> EditItemTemplate I have placed the
    >> dropdown box.
    >>
    >> To update Tim's data in the database, I have defined the following SQL
    >> statement
    >>
    >> UPDATE [Users] SET [f_UserTypeID] = @UserType, [Name] = @Name, =
    >> @Email WHERE [UserID] = @UserID
    >>
    >> My problem is that I cannot figure out the right way to bind the[/color][/color]
    controls,[color=blue][color=green]
    >> so the data get updated. Using the dropdown box, where the Index field is
    >> related to "UserTypeID" and the Value field is related to "UserType", I
    >> would like to be able to update "F_UserTypeID" in the database.
    >>
    >> How am I supposed to do that?
    >>
    >> /Danny[/color]
    >
    >First thing I notice is that you cannot use an SqlDataSource with an
    >Access database. You need an AccessDataSource.
    >
    >Second thing is that you cannot use the GridView Datasource to
    >populate the items list of the DropDownList. You need to consider how
    >that will be done. For the automatic update you could possibly bind
    >the SelectedItemIndex or the SelectedValue to the f_UsertypeID column
    >but that might throw up problems of event sequencing and the state of
    >the control during the DataBind event.
    >
    >Best to do it all manually in code during the DataBound and
    >ItemUpdating events. If you tie the @UserType parameter to a Session
    >variable you can pass the SelectedValue that way as part of the
    >ItemUpdating event code.
    >[/color]
    Steven Cheng[MSFT], Nov 29, 2007
    #3
  4. Hi Danny,

    Any progress on this issue? Or if there is still anything need help,
    welcome to post here.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    This posting is provided "AS IS" with no warranties, and confers no rights.


    --------------------
    >From: (Steven Cheng[MSFT])
    >Organization: Microsoft
    >Date: Thu, 29 Nov 2007 03:31:09 GMT
    >Subject: Re: Gridview and Dropdown - binding?


    >
    >Thanks for Phil's input.
    >
    >Hi Danny,
    >
    >I agree with Phil that for your scenario, since the select data record set
    >are from two joined table, it it not quite suitable to use automatic

    update
    >here. Automatic generated update will work well for a single table or
    >updatable view scenario. You may consider hook those updating events of

    the
    >GridView and manually do the update (to the two tables with the proper
    >parameters)
    >
    >BTW, for AccessDataBase, we can use "SqlDataSource" to connect it as long
    >as the correct OLEDB provider is choosed:)
    >
    >Sincerely,
    >
    >Steven Cheng
    >
    >Microsoft MSDN Online Support Lead
    >
    >
    >
    >This posting is provided "AS IS" with no warranties, and confers no rights.
    >--------------------
    >>From: Phil H <>
    >>Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols
    >>Subject: Re: Gridview and Dropdown - binding?
    >>Date: Wed, 28 Nov 2007 10:10:10 -0800 (PST)
    >>
    >>On 28 Nov, 14:50, "Danny" <> wrote:
    >>> Hi Group,
    >>>
    >>> I am working on a simple user administration system based on VB ASP.NET

    >and
    >>> an Access database. The database contains the folowing data:
    >>>
    >>> Users:
    >>> UserID, f_UserTypeID, Name, Email
    >>> 1, 1, Pat,
    >>> 2, 1, Joe,
    >>> 3, 2, Tim,
    >>>
    >>> UserType:
    >>> UserTypeID, UserType
    >>> 1, Administrator
    >>> 2, User
    >>>
    >>> As seen here, key "UserTypeID" from table UserType is used as foreign

    key
    >>> "f_UserTypeID" in table Users. Being an administrator it should be

    >possible
    >>> to alter the data in table Users. All data are represented using a

    >GridView
    >>> and a SqlDataSource with the following SQL statement:
    >>>
    >>> SELECT Users.*, UserType.* FROM (Users INNER JOIN UserType ON
    >>> Users.f_UserTypeID = UserType.UserTypeID)
    >>>
    >>> When altering for example Tim's data, it should be possible to choose
    >>> whether he should be an Administrator or a User using a dropdown box.
    >>> Therefore, in the design, I have changed the column "UserType" to
    >>> TemplateField. In TemplateField -> EditItemTemplate I have placed the
    >>> dropdown box.
    >>>
    >>> To update Tim's data in the database, I have defined the following SQL
    >>> statement
    >>>
    >>> UPDATE [Users] SET [f_UserTypeID] = @UserType, [Name] = @Name, [/color][/color][/color]
    =[color=blue][color=green][color=darkred]
    >>> @Email WHERE [UserID] = @UserID
    >>>
    >>> My problem is that I cannot figure out the right way to bind the[/color][/color]
    >controls,[color=green][color=darkred]
    >>> so the data get updated. Using the dropdown box, where the Index field[/color][/color][/color]
    is[color=blue][color=green][color=darkred]
    >>> related to "UserTypeID" and the Value field is related to "UserType", I
    >>> would like to be able to update "F_UserTypeID" in the database.
    >>>
    >>> How am I supposed to do that?
    >>>
    >>> /Danny[/color]
    >>
    >>First thing I notice is that you cannot use an SqlDataSource with an
    >>Access database. You need an AccessDataSource.
    >>
    >>Second thing is that you cannot use the GridView Datasource to
    >>populate the items list of the DropDownList. You need to consider how
    >>that will be done. For the automatic update you could possibly bind
    >>the SelectedItemIndex or the SelectedValue to the f_UsertypeID column
    >>but that might throw up problems of event sequencing and the state of
    >>the control during the DataBind event.
    >>
    >>Best to do it all manually in code during the DataBound and
    >>ItemUpdating events. If you tie the @UserType parameter to a Session
    >>variable you can pass the SelectedValue that way as part of the
    >>ItemUpdating event code.
    >>[/color]
    >
    >[/color]
    Steven Cheng[MSFT], Dec 3, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. goodwill

    data binding and dropdown lsit

    goodwill, Oct 22, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    479
  2. vishnu
    Replies:
    1
    Views:
    1,191
    Patrick.O.Ige
    Mar 25, 2006
  3. Mike
    Replies:
    1
    Views:
    796
  4. Amit
    Replies:
    6
    Views:
    13,764
    Assimalyst
    Oct 24, 2006
  5. Replies:
    2
    Views:
    430
Loading...

Share This Page