Brent Oliver Brent Oliver - 21 days ago 5
ASP.NET (C#) Question

Error after adding second datatable control

I use a datalist control populated from a DataTable to display images in an application. Now I need to modify this to show two lists of images that come from different DataTables. So added a second DataList and coded for a second DataTable.

I receive an error:


The Connection String Property has not been initialized.


Code for the DataTable controls, second one (
edt
) is generating the error:

DataTable dt = new DataTable();
using (Conn)
{
SqlDataAdapter ad = new SqlDataAdapter("SELECT QuestionID, Images2.ImageID, ImageFile, ImageContent, ImageName, SEQ_NUM from qimages join Images2 on qimages.imageid = images2.imageid where QuestionID = @QuestionID", Conn);
ad.SelectCommand.Parameters.Add("QuestionID", SqlDbType.BigInt).Value = Convert.ToInt32(Request["Id"]);
ad.Fill(dt);
}
dlImages.DataSource = dt;
dlImages.DataBind();

DataTable edt = new DataTable();
using (Conn)
{
SqlDataAdapter ed = new SqlDataAdapter("SELECT QuestionID, Images2.ImageID, ImageFile, ImageContent, ImageName, SEQ_NUM from eimages join EditedImages on eimages.imageid = editedimages.imageid where QuestionID = @QuestionID", Conn);
ed.SelectCommand.Parameters.Add("QuestionID", SqlDbType.BigInt).Value = Convert.ToInt32(Request["Id"]);
ed.Fill(edt);
}
EditImages.DataSource = edt;
EditImages.DataBind();


Tried changing the second control to use its own connection string as suggested. Below is the new code for the second datatable, which is still throwing the same not initialized error:

string Constr = ConfigurationManager.ConnectionStrings["CS1"].ConnectionString;
SqlConnection Conn2 = new SqlConnection(Constr);
DataTable edt = new DataTable();
using (Conn2)
{
SqlDataAdapter ed = new SqlDataAdapter("SELECT QuestionID, EditImages.ImageID, ImageFile, ImageContent, ImageName, SEQ_NUM from eimages join EditImages on eimages.imageid = editimages.imageid where QuestionID = @QuestionID", Conn);
ed.SelectCommand.Parameters.Add("QuestionID", SqlDbType.BigInt).Value = Convert.ToInt32(Request["Id"]);
ed.Fill(edt);
}
EditImages.DataSource = edt;
EditImages.DataBind();

Answer

You're disposing of the connection when you're done with it (that's what the using block is for). Either do all your database interaction inside one using block, or create a second connection with the same connection string. Here's an example of doing it within the same using block.

DataTable dt = new DataTable();
DataTable edt = new DataTable();

using (Conn)
{
    SqlDataAdapter ad = new SqlDataAdapter("SELECT QuestionID, Images2.ImageID, ImageFile, ImageContent, ImageName, SEQ_NUM from qimages join Images2 on qimages.imageid = images2.imageid where QuestionID = @QuestionID", Conn);
    ad.SelectCommand.Parameters.Add("QuestionID", SqlDbType.BigInt).Value = Convert.ToInt32(Request["Id"]);
    ad.Fill(dt);
    SqlDataAdapter ed = new SqlDataAdapter("SELECT QuestionID, Images2.ImageID, ImageFile, ImageContent, ImageName, SEQ_NUM from eimages join EditedImages on eimages.imageid = editedimages.imageid where QuestionID = @QuestionID", Conn);
    ed.SelectCommand.Parameters.Add("QuestionID", SqlDbType.BigInt).Value = Convert.ToInt32(Request["Id"]);
    ed.Fill(edt);
}

dlImages.DataSource = dt;
dlImages.DataBind();
EditImages.DataSource = edt;
EditImages.DataBind();

You've got a lot of repeated code here, you might think of refactoring it into a method. You've also got database access logic mix into your Web Forms code behind, which is very messy. All your database logic should be abstracted out into a separate data access layer.