Monday, March 12, 2007

a small mistake with datatables and datarow

I was trying to use a datatable to store some values and assign it to datagrid...but the values wouldnt show up...Now i know...we need to add the row explicitly else it does not work..

here is my code...

//prepare sql statements
DataTable dt = new DataTable("Permissions");
// Add two column objects to the table.
DataColumn userIDCol = new DataColumn();
userIDCol.DataType = Type.GetType("System.String");
userIDCol.ColumnName = "UserID";
dt.Columns.Add(userIDCol);
DataColumn PermissionsCol = new DataColumn();
PermissionsCol.DataType = Type.GetType("System.String");
PermissionsCol.ColumnName = "Permissions";
dt.Columns.Add(PermissionsCol);

SqlConnection myConnection = new SqlConnection(ClassicString);
string sqlQuery = "SELECT EDIT_USERID, CLEARANCE FROM [IAIClassic].[dbo].[Security]";
SqlCommand SourceCommand = new SqlCommand(sqlQuery, myConnection);
myConnection.Open();
SqlDataReader SourceDataReader = SourceCommand.ExecuteReader();
string variableReturned;
int lengthOfString;
int i;

while (SourceDataReader.Read())
{
variableReturned = SourceDataReader["CLEARANCE"].ToString();
lengthOfString = variableReturned.Length;
// 2. Now enter each column in the new DB...so there would be an insert query for the entire array.... since we need to break ABCDE# to A, B, C, D, E#
for (i = 0; i < lengthOfString; i++)
{
//first match the key to its respective keycode
//
DataRow dr;
dr = dt.NewRow();
dr["UserID"] = SourceDataReader["EDIT_USERID"].ToString();
dr["Permissions"] = variableReturned.Substring(i, 1);
dt.Rows.Add(dr);
}
}


myConnection.Close();
GridView3.DataSource = dt;
GridView3.DataBind();
GridView3.Visible = true;
GridView3.Width = 600;
Title.Text = "The existing permissions in the system";

No comments: