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"]));
}
}
}
Pravesh Singh
23-Dec-2014Let me briefly dissect your code here:
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.