CODEWITHSUNDEEP
c#asp.netsqlms-access-2007
user3793996
user3793996

Reputation: 3

Getting error using access database asp.net c#

Code modification SQL to MS access database. This is my working code using SQL database and want to change ms access database but getting an error. Below working code using sql database:

protected void Page_Load(object sender, EventArgs e)
{
 BindData();
}


private void BindData()
{
    SqlConnection myConnection = new SqlConnection("Data Source=Mazhar-PC;Initial Catalog=MKE;user id=sa;password=ok; ");
    SqlCommand myCommand = new SqlCommand("usp_GetProductsForCategories", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter ad = new SqlDataAdapter(myCommand);
    DataSet ds = new DataSet();
    ad.Fill(ds);
    // Attach the relationship to the dataSet
    ds.Relations.Add(new DataRelation("CategoriesRelation", ds.Tables[0].Columns["CategoryID"],
    ds.Tables[1].Columns["CategoryID"]));
    outerRep.DataSource = ds.Tables[0];
    outerRep.DataBind();

}


protected void outerRep_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item ||
    e.Item.ItemType == ListItemType.AlternatingItem)
    {
        DataRowView drv = e.Item.DataItem as DataRowView;
        Repeater innerRep = e.Item.FindControl("innerRep") as Repeater;
        innerRep.DataSource = drv.CreateChildView("CategoriesRelation");
        innerRep.DataBind();

    }
}

I used two tables:

Category
Product

Category table has been following columns:

CategoryID CategoryName

Product table has been following columns:

PID ImageName ProductName Price CategoryID

I have a stored procedure in sql to get results according to category and their products.

ALTER PROCEDURE [dbo].[usp_GetProductsForCategories]
AS
    SELECT * 
    FROM Category 
    WHERE CategoryID IN (SELECT CategoryID FROM Product) SELECT p.PID, p.ImageName, p.ProductName, p.Price, p.CategoryID FROM Product p

Below not working code using ms access database:

protected void Page_Load(object sender, EventArgs e)
{
 BindData();
}


private void BindData()
{
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\LKKT1\My Documents\Downloads\examples\demo1\kkk.accdb";
    conn.Open();
    string sql = "create procedure usp_GetProductsForCategories as select * from Category3 where CategoryID IN (select CategoryID from Product3) SELECT p.PID, p.ImageName, p.ProductName, p.Price, p.CategoryID FROM Product3 p
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    OleDbDataAdapter ad=new OleDbDataAdapter(cmd);
    DataSet ds=new DataSet();
    ad.Fill(ds);
    ds.Relations.Add(new DataRelation("CategoriesRelation",ds.Tables[0].Columns["CategoryID"],
    ds.Tables[1].Columns["CategoryID"]));
    outerRep.DataSource=ds.Tables[0];
    outerRep.DataBind();
 }


protected void outerRep_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item ||
    e.Item.ItemType == ListItemType.AlternatingItem)
    {
        DataRowView drv = e.Item.DataItem as DataRowView;
        Repeater innerRep = e.Item.FindControl("innerRep") as Repeater;
        innerRep.DataSource = drv.CreateChildView("CategoriesRelation");
        innerRep.DataBind();

    }
}

I am using a nested repeater to display product according to category. Its giving error on dataset filling time.

Upvotes: 0

Views: 197

Answers (1)

Kinexus
Kinexus

Reputation: 12904

string sql = "select * from Category3 
where CategoryID IN (select CategoryID from Product3) SELECT p.PID, p.ImageName, 
p.ProductName, p.Price, p.CategoryID FROM Product3 p";
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        OleDbDataAdapter ad=new OleDbDataAdapter(cmd);
        DataSet ds=new DataSet();
        ad.Fill(ds);

You appear to be creating a stored procedure and trying to fill a dataset with it each time you bind the data.

Upvotes: 1

Related Questions