Select dropdown list value from joined table in razor web pages (using WebMatrix)

Question

I'm learning ASP.Net web pages programming using Webmatrix (with razor syntax).

Goal is to have dropdown list boxes populated by values stored in a table and the selected value coming from another joined table.

The selected value should be shown before and after a post.

I also want that if there is no UserTypeId in the UserProfiles table (UserTypeId == NULL), a default "Please select" would be shown.

Table 1: UserTypes with UserTypeId and Name fields Table 2: UserProfiles with UsertypeId as foreign key

I have the following error message and don't find how to solve that issue: Operator '==' cannot be applied to operands of type 'int' and 'WebMatrix.Data.DynamicRecord'

CODE section:

var db = Database.Open("MyDatabase");

To get the UserTypeId stored in the UserProfiles table for the connected user

var selectedUserTypeId = db.QuerySingle("SELECT UserTypeId FROM UserProfiles INNER JOIN Users ON UserName=@0",authenticatedUser);

To get the list of the UserTypes

var sqlUserTypeData = "SELECT userTypeId, Name FROM UserTypes";
var userTypeData = db.Query(sqlUserTypeData);

var userTypeDataList = userTypeData.Select(userTypeDataListItem => new SelectListItem {
    Value = userTypeDataListItem.UserTypeId.ToString(),
    Text = userTypeDataListItem.Name,
    Selected = userTypeDataListItem.UserTypeId == selectedUserTypeId ? true : false
});

Last line is where the issue occurs: userTypeDataListItem.UserTypeId == selectedUserTypeId. These types cannot apparently be compared.

if(!IsPost){
    var sqlUserData = @"SELECT *,  UserTypes.Name AS UserType
                        FROM UserProfiles
                        INNER JOIN Users ON Users.UserId = UserProfiles.UserId 
                        INNER JOIN UserTypes ON UserTypes.UserTypeId = UserProfiles.UserTypeId 
                        WHERE Users.UserName=@0";

    var userData = db.QuerySingle(sqlUserData,authenticatedUser);

    userType = userData.UserType;
    }

if(IsPost){
    Validation.RequireField("userTypeCombo", "Please select");

    userId = Request.Form["userId"];
    userTypeId = Request.Form["userTypeCombo"];

if(Validation.IsValid()) {
        var updateCommand = @"UPDATE UserProfiles
                            SET UserTypeId=@0
                            WHERE UserId=@1";
        db.Execute(updateCommand, userTypeId, userId);
        Response.Redirect("~/authcontent/user");
    }

HTML section:

@if (isAuthenticated) {
    <form action="" method="post">
        @Html.ValidationSummary()
        <section class="infoblock">
            <fieldset>
                <legend>Mon profil</legend>
                <ul>
                    <li><label for="userType">Profil</label>
                        @Html.DropDownList("userTypeCombo", "Please select", userTypeDataList)</li>
                </ul>
            </fieldset>
        </section>
    </form>        
}

GmG · Accepted Answer · 2012-09-17 06:46:36Z

2

You should resolve the problem highlighted by this error message replacing

var selectedUserTypeId = db.QuerySingle(...)

with

var selectedUserTypeId = db.QueryValue(...)

or specifying the record field:

Selected = userTypeDataListItem.UserTypeId == selectedUserTypeId.UserTypeId ? true : false

answered Sep 17, 2012 at 6:46

GmG

1,3721 gold badge9 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1455103 Over a year ago

Thanks for your solution(s). I have however an additional issue: selectedUserTypeId can (this is normal) be NULL. When it is the case , the following error is thrown: "Operator '==' cannot be applied to operands of type 'int' and 'System.DBNull'". What is the best way to handle this ? Ideally, if the value is null, I would like the dropdown to display a default "Please select".

GmG Over a year ago

If selectUserTypeId is null, assign to it an int value that can't exist in UserTypeId (0 or a negative value), something like selectUserTypeId = selectUserTypeId == null ? 0 : selectUserTypeId;. That way the default choice will be displayed.

Collectives™ on Stack Overflow

Select dropdown list value from joined table in razor web pages (using WebMatrix)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related