Question:
a) How to populate the data in ListBox from the database.
b) How to display the relevant Details in the Textboxes for selected item.
Solution:
Solution a)
To display the data in the Listbox from the Database is simple binding. (In our sample we are populating the ListBox with the EmployeeId's.)
Step 1:
SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds;
String strConn;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
strConn="Data Source=localhost;uid=sa;pwd=;Initial Catalog=northwind";
mycn = new SqlConnection(strConn);
myda = new SqlDataAdapter ("Select * FROM Employees", mycn);
ds = new DataSet();
myda.Fill (ds,"Table");
if (!Page.IsPostBack )
{
for(int i=0;i<ds.Tables[0].Rows.Count-1;i++)
{
ListBox1.Items.Add(new ListItem( ds.Tables [0].Rows[i]["Employeeid"].ToString() , i.ToString ()));
}
}
}
Note: Refer * for Basic UI Solution b)
To display the details for the ListItem Selected in the Textboxes. (i.e Show Details of the EmployeeID selected)
This can be done by designing a page with n number of Textboxes based on the columns in the Database Table. Or the other solution is to create these Textboxes dynamically.
Lets take a look on how to create the Controls Dynamically. The sample shows the data from Employees Table (Northwind DataBase).
This Table has Columns having DataType as string, DateTime, Byte[]. So as we go through the process of dynamically adding the controls we'll also see how to handle these DataTypes and display the data accordingly.
Step 1:
We'll drag and drop PlaceHolder ( One to display the data in the Text Format and other to display the image i.e to handle the System.Byte[] DataType)
Step 2:
private
void ListBox1_SelectedIndexChanged(object sender, System.EventArgs e)
{
//Controls to be dynamically created
TextBox tb; //TextBox
Label lb; //Label
System.Web.UI.WebControls.Image img1 ; //Image
//Create a DataView to Filter the DataBased on the Employeeid Selected
DataView dvEmployees =new DataView(ds.Tables["Table"] );
dvEmployees.RowFilter = "Employeeid=" + ListBox1.SelectedItem.Text;
Label1.Text = "Details of Employee ID: " + ListBox1.SelectedItem.Text;
string strData;
//Set the Rowindex
int rowindex = Convert.ToInt32(ListBox1.SelectedItem.Value) ;
foreach(DataColumn dc in dvEmployees.Table.Columns )
{
//Set Id,Width,Font for Label and TextBox Control
lb= new Label ();
lb.ID = lb + dc.ColumnName ;
tb = new TextBox();
tb.ID = tb + dc.ColumnName ;
lb.Width =Unit.Percentage (30);
tb.Width =Unit.Percentage (50) ;
lb.Font.Name ="verdana";
lb.Font.Size =FontUnit.XSmall;
lb.Font.Bold= true ;
tb.Font.Name ="Verdana";
tb.Font.Size =FontUnit.XSmall;
img1= new System.Web.UI.WebControls.Image ();
//Set Text Property of Label Control
lb.Text = dc.ColumnName ;
//Set Text Property of TextBox Control
strData = dvEmployees.Table.Rows[rowindex][dc.ColumnName ].ToString ();
//If DataType is System.Byte[]
if (dc.DataType.ToString () =="System.Byte[]")
{
//To display image of Employee
if (strData!=DBNull.Value.ToString ())
{
Byte[] imgByte = (Byte[])dvEmployees.Table.Rows[rowindex][dc.ColumnName];
int offset = 78;
MemoryStream ms = new MemoryStream();
ms.Write(imgByte, offset, imgByte.Length - offset);
Bitmap bmp = new Bitmap(ms);
//To do the following give the Write Permissions for ASPNET user on respective folder
bmp.Save(Server.MapPath ("sample.jpeg"), System.Drawing.Imaging.ImageFormat.Jpeg );
img1.ImageUrl = (Server.MapPath("sample.jpeg"));
img1.ID="img" + dc.ColumnName;
img1.ToolTip = dvEmployees.Table.Rows[rowindex]["Notes"].ToString();
//To set height and width of image
//img1.Height =Unit.Pixel(222);
//img1.Width =Unit.Pixel(191);
PlaceHolder1.Controls.Add(img1);
}
//If no image is available
else
{
Label lbNoImg = new Label();
lbNoImg.Text ="No image Available";
lbNoImg.Font.Name ="Verdana";
lbNoImg.Font.Size =FontUnit.Small;
PlaceHolder1.Controls.Add (lbNoImg );
}
}
else
{
//If DataType is DateTime (Format Date as yyyy-dd-mm)
if (dc.DataType.ToString()== "System.DateTime")
{
if (strData!=DBNull.Value.ToString())
{
DateTime dt = DateTime.Parse(strData) ;
tb.Text = dt.ToString ("MMMM yyyy, dd");
}
}
//For the rest of the fields
else
{
if (strData.Length >=40)
{tb.Text = strData.Substring(0,40) + "...";
tb.ToolTip = strData;
}
else
{
tb.Text = strData;
}
}
/Add the dynamically created controls to the PlaceHolder
PlaceHolder2.Controls.Add (lb);
PlaceHolder2.Controls.Add (tb);
}
}
}
* Not to forget how the UI looks like <
TABLE id="Table1" cellSpacing="1" cellPadding="1" width="100%" border="0">
<TR>
<TD>
<TABLE id="Table2" cellSpacing="1" cellPadding="1" width="100%" border="0">
<TR>
<TD width="25%" vAlign="top">
<asp:Label id="Label1" runat="server" Font-Names="Verdana" Font-Size="X-Small" Font-Bold="True">Select Employee Id</asp:Label>
</TD>
<TD width="20%" vAlign="top">
<asp:listbox id="ListBox1" runat="server" AutoPostBack="True"></asp:listbox>
</TD>
<TD width="50%" vAlign="top">
<asp:placeholder id="PlaceHolder1" runat="server"></asp:placeholder>
</TD>
</TR>
</TABLE>
</TD>
</TR>
<TR>
<TD>
<asp:PlaceHolder id="PlaceHolder2" runat="server"></asp:PlaceHolder>
</TD>
</TR>
</TABLE>
Final Output is as below: 