Hi,

I was wondering if someone could help me with this. I've been using the TableEditor control (available at http://www.tripleasp.net/tableeditor.aspx?NavID=31 - thanks to Scott Watermasysk for coming up with it) - its basically an extended DataGrid which can edit any table in the database.

I need to get it to display dropdowns for all hte foreign key links to other tables so I've edited the BuildColumns() [basically the bind function]. But I'm stuck at the stage where I'm getting the dropdown to display. Code is as follows:


Code:
protectedvoid BuildColumns()
 
{
 
TemplateColumn gc;
 
//start new column
 
gc = new TemplateColumn();
 
 
 
//go once for PK field
 
string editcolumn = columninfo.Rows[0]["COLUMN_NAME"].ToString();
 
gc.HeaderText = editcolumn; //columns[0].ToString();
 
gc.ItemTemplate = new GenericItemTemplateColumn(editcolumn,_maxstringlen    gth);
 
 
 
if(_allowediting)
 
{
 
gc.EditItemTemplate = new DataEntryFormItemTemplate(columninfo, primarykey);
 
}
 
base.Columns.Add(gc);
 
//loop through the rest of the columns
 
for(int i = 1; i < columninfo.Rows.Count; i++)
 
{
 
string column = columninfo.Rows[i]["COLUMN_NAME"].ToString();
 
bool foundColumn = false;
 
gc = new TemplateColumn(); 
 
//check for foreign key links
 
SqlDataReader pk = GetForeignKeyReader();
 
int j = 0;
 
while(pk.Read())
 
{
 
//if current column links to another table (compares the headers)
 
if (pk["fkcolumn_name"].ToString()==column) 
 
{
 
//populate the dropdown
 
string query = "SELECT " + pk["pkcolumn_name"] + ", description FROM " + pk["pktable_name"];
 
AddError(query);
 
 
 
SqlDataReader getDD;
 
try 
 
{
 
getDD = GetDropdownReader(query);
 
} 
 
catch (Exception e) {
 
AddError("Exception: " + e.Message);
 
throw;
 
}
 
System.Web.UI.WebControls.DropDownList d = new System.Web.UI.WebControls.DropDownList();
 
d.ID = "list" + j;
 
d.DataSource = getDD;
 
d.DataTextField = "description";
 
d.DataValueField = "object_id";
 
d.DataBind();
 
foundColumn = true;
 
gc.ItemTemplate = new GenericItemTemplateColumn(column,_maxstringlength)    ; 
 
}
 
j += 1;
 
}
 
pk.Close();
 
 
 
//if foundColumn = false, then no columns were found, so must be a text column
 
if (!foundColumn) 
 
{
 
//else create normal text columnn
 
gc.ItemTemplate = new GenericItemTemplateColumn(column,_maxstringlength)    ;
 
}
 
gc.HeaderText = column;
 
base.Columns.Add(gc);
 
}
 
}
 

 
/*check for fk constraints*/
 
private SqlDataReader GetForeignKeyReader()
 
{
 
if(_connectionstring != null && _table != null)
 
{
 
//this command gets all tables (primary keys) which are related to the current table (with foreign keys)
 
SqlCommand cmd = new SqlCommand("sp_fkeys @fktable_name = '" + _table + "'", new SqlConnection(_connectionstring));
 
cmd.Connection.Open();
 
return cmd.ExecuteReader(CommandBehavior.CloseConnection)    ;
 
} else {
 
thrownew Exception("ConnectionString(or ConfigConnectionString) and/or Table value was not set");
 
}
 
}
Basically, it checks the sp_fkeys view for any foreign keys. It then loops through each column in the actual table and checks if that column is a FK - if it is, then it selects the object_id, description from the related FK table. (All concerned tables have to have both these fields).

I'm stuck at the stage where I get the dropdown to display. I presume I need a new Template Class which creates a dropdown instead of a label (the default one is GenericItemTemplateColumn and I include it's code below). How do I go about making that new class - do I just need to change any reference to Label to Dropdown? How would I reference that dropdown from the TableEditor.cs class to bind the data to it?

Code:
using System;
 
using System.Data;
 
using System.Data.SqlClient;
 
using System.Web;
 
using System.Web.UI;
 
using System.Web.UI.WebControls;
 
namespace TripleASP.SiteAdmin.TableEditorControl
 
{
 
///<summary>
 
/// Summary description for GenericItemTemplateColumn.
 
///</summary>
 
publicclass GenericItemTemplateColumn : ITemplate
 
{
 
privatestring column;
 
privateint maxlengthstring = -1;
 
public GenericItemTemplateColumn(string column, int maxlengthstring)
 
{
 
this.column = column;
 
this.maxlengthstring = maxlengthstring;
 
}
 
public GenericItemTemplateColumn(string column)
 
{
 
this.column = column;
 
}
 
publicvoid InstantiateIn(Control container)
 
{
 
Label l = new Label();
 
 
 
l.DataBinding += new EventHandler(this.BindData);
 
container.Controls.Add(l);
 
 
 
}
 
publicvoid BindData(object sender, EventArgs e)
 
{
 
Label l = (Label) sender;
 
DataGridItem container = (DataGridItem) l.NamingContainer;
 
string s = HttpContext.Current.Server.HtmlEncode(((DataRowVie    w) container.DataItem)[column].ToString());
 
if(maxlengthstring > -1 && s.Length > maxlengthstring)
 
{
 
l.Text = s.Substring(0, maxlengthstring) + "...";
 
}
 
else
 
{
 
l.Text = s;
 
}
 
}
 
}
 
}
Hope someone can help,

Many thanks,
Stuart