CODEWITHSUNDEEP
c#asp.netsql-server-2008
user1374985
user1374985

Reputation:

error cannot convert from 'object' to 'string' when filling a asp:DropDownList dynamically

I am trying to fill my droplist with data from sql but when I try to add the list items I get the error in the title.

SqlCommand cmd = new SqlCommand(@"SELECT RLS.RoleName [RoleName], 
    URS.UserID [UserID], 
    USRS.UserName[UserName], 
    USRS.FirstName[FirstName], 
    USRS.LastName[LastName]
  FROM [Roles] RLS 
  Inner JOIN [Users] USRS 
  LEFT JOIN [UserRoles] URS ON USRS.[UserID] = URS.[UserID] ON RLS.[RoleID] = URS.[RoleID]                                     
  WHERE RLS.[RoleName] = 'Blog Editors'",conn);


conn.Open();
using (SqlDataReader reader1 = cmd.ExecuteReader())
{
    while (reader1.Read())
    {
        int numUserID = reader1.GetInt32(1);
        string strFirstName = reader1.GetString(3);
        string strLastName = reader1.GetString(4);
        string newUserName = strFirstName + " " + strLastName;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        foreach (DataRow row in dt.Rows)
        {
            ddlCreatedBy.Items.Add(new ListItem(row["newUserName"], row["numUserId"]));
        }
    }
}

Upvotes: 0

Views: 134

Answers (3)

Afnan Ahmad
Afnan Ahmad

Reputation: 2542

Please user Convert.ToString 

SqlCommand cmd = new SqlCommand(@"SELECT RLS.RoleName [RoleName], URS.UserID [UserID], USRS.UserName[UserName], USRS.FirstName[FirstName], USRS.LastName[LastName]
                                                FROM [Roles] RLS Inner JOIN [Users] USRS LEFT JOIN [UserRoles] URS 
                                                ON USRS.[UserID] = URS.[UserID] ON RLS.[RoleID] = URS.[RoleID] 
                                                WHERE RLS.[RoleName] = 'Blog Editors'",conn);
                conn.Open();
                using (SqlDataReader reader1 = cmd.ExecuteReader())
                {
                    while (reader1.Read())
                    {
                        int numUserID = reader1.GetInt32(1);
                        string strFirstName = reader1.GetString(3);
                        string strLastName = reader1.GetString(4);
                        string newUserName = strFirstName + " " + strLastName;

                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        foreach (DataRow row in dt.Rows)
                        {
                ddlCreatedBy.Items.Add(new ListItem(Convert.ToString(row["newUserName"]), Convert.ToString(row["numUserId"]));
                        }
                    }

OR Call ToString()

ddlCreatedBy.Items.Add(new ListItem(row["newUserName"].ToString(), row["numUserId"].ToString());

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Let me briefly dissect your code here:

using (SqlDataReader reader1 = cmd.ExecuteReader())
{
    while (reader1.Read())
    {
        int numUserID = reader1.GetInt32(1);
        string strFirstName = reader1.GetString(3);
        string strLastName = reader1.GetString(4);
        string newUserName = strFirstName + " " + strLastName;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        foreach (DataRow row in dt.Rows)
        {
            ddlCreatedBy.Items.Add(new ListItem(row["newUserName"], row["numUserId"]));
        }
    }
}

You read from the reader record by record just to execute the same statement over and over for each record to fill a data set? This means that for every record you fill a new dataset with all the records - that's pretty redundand, isn't it?

Also, your SQL statement never selects a newUserName or numUserId column, so you can't access either column in any row of the table.

Don't you actually want to do the following? This reads the user data line by line, assembles the new user name and then creates a new entry in the list.

using (SqlDataReader reader1 = cmd.ExecuteReader())
{
    while (reader1.Read())
    {
        int numUserID = reader1.GetInt32(1);
        string strFirstName = reader1.GetString(3);
        string strLastName = reader1.GetString(4);
        string newUserName = strFirstName + " " + strLastName;

        ddlCreatedBy.Items.Add(new ListItem(newUserName, numUserId.ToString()));
    }
}

Upvotes: 3

prograshid
prograshid

Reputation: 927

Do this

ddlCreatedBy.Items.Add(new ListItem(row["newUserName"].ToString(), row["numUserId"].ToString()));

Upvotes: 0

Related Questions