Column does not exists in datasouruce problem when binding SubSonic collection to dropdown.

Question

I've a table with Primary Key with _ (underscore) in its name like: User_Id. When SubSonic generates DAL it removes underscores. Now I'm binding the object collection to DropDownList like follows:

private void LoadCbo()
{
    UserCollection users=(new UserCollection()).Load();
    User u=new User(){
        UserId=-1,
        Name="[Select]"};
   users.Insert(0,u);

   ddlUsers.DataSource=users;
   ddlUsers.DataValueField=User.Columns.UserId;
   ddlUsers.DataTextField=User.Columns.Name;
   ddUsers.DataBind();    
}

On running it tells me that object does not contains column with name "User_Id".

PS:- using "UserId" works fine. I just want to know if this is a bug in SubSonic (2.1) or I'm doing something wrong?

Answer 1

The Columns collection is made up of the column names in the database, not the property names of the object. This isn't a bug it's an essential piece of functionality otherwise SubSonic would have no knowledge of how to query the actual database.

The following line is specifying what property to use when populating the value of the dropdown:

ddlUsers.DataValueField=User.Columns.UserId;

The value of User.Columns.UserId will be "User_Id", it is the name of the column in your database table, not the name of the property. However when ddlUsers is databinding it cannot find a property of the User object with the name User_Id because when SubSonic generates your DAL it removes the underscore from the property name. The best fix is (as pointed out by ranomore):

ddlUsers.DataValueField = User.UserIdColumn.PropertyName;

Answer 2

In SubSonic 2.2, you can also do this:

ddlUsers.DataValueField = User.UserIdColumn.PropertyName;

This way you can avoid hardcoding column names in your code.