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>        
}

score 2 · Accepted Answer · answered Sep 17 '12 at 06:46

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 '12 at 06:46

GmG

1,372
1
9
10

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". – user1455103 Sep 20 '12 at 10:51
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. – GmG Sep 21 '12 at 16:30

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

1 Answers1