Gridview and Dropdown

Gridview and Dropdown - binding?

microsoft.public.dotnet.framework.aspnet.webcontrols

28 Nov 2007 2:50 PM

Danny

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, p**@someunknownemail.com
2, 1, Joe, j**@someunknownemail.com
3, 2, Tim, t**@someunknownemail.com

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] =
@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

28 Nov 2007 6:10 PM

Phil H

Show quote

On 28 Nov, 14:50, "Danny" <n***@none.com> 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, p***@someunknownemail.com
> 2, 1, Joe, j***@someunknownemail.com
> 3, 2, Tim, t***@someunknownemail.com
>
> 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] =
> @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

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.

29 Nov 2007 3:31 AM

Steven Cheng[MSFT]

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.
--------------------
Show quote

>From: Phil H <goo***@philphall.me.uk>
>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" <n***@none.com> 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, p***@someunknownemail.com
>> 2, 1, Joe, j***@someunknownemail.com
>> 3, 2, Tim, t***@someunknownemail.com
>>
>> 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] =
>> @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
>
>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.
>