Getting error using access database asp.net c#

Question

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.

@user3793996 please look at the inner exception, what does it say? — 3dd
– 3dd, Commented Jul 7, 2014 at 10:51
Yes, I am confused whether written correct stored procedure or not. And I don't have idea of inner exception. Can you write code for BindData method. 3dd — user3793996
– user3793996, Commented Jul 7, 2014 at 10:56
On the image you sent, when the error occurs, click the view details button. Then view the exception and then the inner exception. The reason for the error should be in there — 3dd
– 3dd, Commented Jul 7, 2014 at 11:02
Yea, I am displaying product images with the price in my app but still confused, can you write the code for BindData. — user3793996
– user3793996, Commented Jul 7, 2014 at 11:06

Kinexus · Accepted Answer · 2014-07-07 11:14:56Z

1

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.

edited Jul 7, 2014 at 11:14

answered Jul 7, 2014 at 10:52

Kinexus

12.9k6 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user3793996 Over a year ago

This code is same as my above code, then what is my mistake.

Kinexus Over a year ago

Yes, that is my point. Why are you creating a stored procedure each time?

user3793996 Over a year ago

I want to display product with images according to category wise and I used nested repeater for display product and category. And admin can add many categories and their products. The above SQL code is working perfectly, but the requirement is access database.

Kinexus Over a year ago

remove the "create procedure usp_GetProductsForCategories as" code and it might work. My point is that you do not want to create a stored procedure in this code.

user3793996 Over a year ago

I had implemented code according to this example: c-sharpcorner.com/UploadFile/rohatash/…

|

Collectives™ on Stack Overflow

Getting error using access database asp.net c#

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related