C# shopping basket, trying to remove an item...

Darragh29 · 05-05-2009 3:12pm

Ginger wrote: »

Good to hear, Dublin is wickedly cheaper than here anyways... is it coming along ok?

I left it on the long finger over the long weekend... Ok, here we go...

I'm going to read over your advice on page 8 of the thread again, but from looking at what I've to go now, I think all I need to do is just get one of my Dropdown variables to get posted into my DataTable on a Button_Click event trigger. I think once I get this done, then I can just expand my code to allow for more variables to be added into the DataTable at the same time... I'll say no more until I read over your last suggestions so you're not repeating yourself!

Darragh29 · 05-05-2009 10:22pm

Right, I'm a bit confused here, because I have a page with 3 linked dropdown lists, and I've put a button under the list...

What I need to happen is when an item is selected using the dropdown list, and the button under the drop down list is clicked, that the three variables that hold the items selected in the 3 layer dropdown list, appear in my shopping cart. This means that I'm not using an object that is bound to my database to do this doesn't it, but instead I'm working with 3 variables??? If I can even get one variable to be added to my shopping cart, I can expand the solution given to suit my own needs, but I'm stuck at how to work with a variable, with respect to the current solution, as it seems to be bound to a database, which I can't use as my dropdown list works with an XML file but ultimately is just three variables that I have to use because the whole solution at my end is based around comparing these three variables to three fields in a DB and where they match, pulling back an additional number of fields (for example, info on price, stock code, blah blah blah)...

On the face of it, it sounds like the easiest thing in the world, but how it links in with the current solution, I'm not sure!

Ginger · 06-05-2009 9:42am

In this post there is a new solution that includes a dropdown menu and shows how I get Items from each one.

Also since you are using the out of the box Ajax template from the Toolkit there is a sample in that toolkit how to use a database rather than XML

http://www.asp.net/AJAX/AjaxControlToolkit/Samples/Walkthrough/CCDWithDB.aspx

Darragh29 · 06-05-2009 6:26pm

The only thing I'm having a problem with on your new solution is that when I run the SQL script, it doesn't create a stored procedure called usp_select_category, but when I run the DropdownDemo.aspx file, it looks for this particular SP. I've since created it in the script but I don't think I've done it correctly as I'm getting the error attached...

Ginger · 06-05-2009 6:44pm

Crud sorry mate, I created them on a different server, and must have run the creating script from the wrong database.

each proc returns an int and name from the database to match the structure of the class. You will notice that I bind datacolumn to the ID which is an INT rather than probably a string which are using. I will see if I can recreate the SPs for you

Darragh29 · 06-05-2009 7:04pm

This is my Stored Procedure for usp_select_category

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_select_category]
AS
SELECT Id,[Name],[Description],Cost FROM Product

Ginger · 06-05-2009 7:20pm

My Category table is just ID and Name and is an auto incrementing integer and text fields

Ok I will create them quickly for you and post back the SQL

Darragh29 · 06-05-2009 7:22pm

Ginger wrote: »

My Category table is just ID and Name and is an auto incrementing integer and text fields

Ok I will create them quickly for you and post back the SQL

I think I can create that using my SQL Server Management Studio Express... Does my Stored Proc above OK???

Darragh29 · 06-05-2009 7:42pm

I've created the table Category as suggested and also sorted out the Stored Proc to be:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_select_category]
AS
SELECT Id,[Name] FROM Category

And I've fired that SP and all looks good but I'm still getting the last error...

Ginger · 06-05-2009 9:14pm

last error because you need to create 2 more stored procs

Darragh29 · 07-05-2009 11:53am

What do the two other stored procs do?

Ginger · 07-05-2009 1:06pm

CREATE PROCEDURE dbo.usp_select_subcat
(
@CatID INT
)
SELECT ID,[Name] FROM SubCat WHERE CatID = @CatID

CREATE PROCEDURE dbo.usp_select_spec
(
@CatID INT
)
SELECT ID,[Name] FROM Spec WHERE SubCatID = @CatID

I am fairly sure that is what they look like

Darragh29 · 07-05-2009 1:25pm

Do I also need to create a new table called categoryB1 or Subcategory?

Ginger · 07-05-2009 1:30pm

Create 2 tables

1 is SubCat and is ID,Name, CatID (INT,VarChar,INT) CatID is foreign key to Cat
2 is Spec and is ID,Name, SubCatID (INT,VarChar,INT) SubCatID is a foreign key to SubCat

Finally a link Table

ProductSpec, ID, ProductID, SpecID (INT,INT,INT) and they are Foreign Key constraints to the other tables

This allows products to be linked to multiple specs

Darragh29 · 07-05-2009 2:19pm

I've created the tables and the procs but I'm still getting the same error which is weird...

Ginger · 07-05-2009 2:20pm

Can you show me your BindCat function or did you change any of the dropdowns???

Darragh29 · 07-05-2009 2:29pm

Ginger wrote: »

Can you show me your BindCat function or did you change any of the dropdowns???

No I haven't gone near any of the code yet because I want to get it to compile/build before I start rooting around with it....

#region

using System;
using System.Web.UI;
using ShoppingCart.Business;

#endregion

namespace ShoppingCart.Web
{
public partial class DropdownDemo : Page
{
protected override void OnInit(EventArgs e)
{
Category.SelectedIndexChanged += Category_SelectedIndexChanged;
SubCategory.SelectedIndexChanged += SubCategory_SelectedIndexChanged;

base.OnInit(e);
}

private void SubCategory_SelectedIndexChanged(object sender, EventArgs e)
{
BindSpec();
}

private void Category_SelectedIndexChanged(object sender, EventArgs e)
{
BindSubCat();
BindSpec();
}

protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack) return;
BindCategory();
BindSubCat();
BindSpec();
}

private void BindCategory()
{
Category.DataSource = CategoryBl.GetAllCategories();
Category.DataValueField = "Id";
Category.DataTextField = "Name";
Category.DataBind();
}

private void BindSubCat()
{
SubCategory.DataSource = CategoryBl.GetAllSubCats(Int32.Parse(Category.SelectedValue));
SubCategory.DataValueField = "Id";
SubCategory.DataTextField = "Name";
SubCategory.DataBind();
}

private void BindSpec()
{
Specialisation.DataSource = CategoryBl.GetAllSpecs(Int32.Parse(SubCategory.SelectedValue));
Specialisation.DataValueField = "Id";
Specialisation.DataTextField = "Name";
Specialisation.DataBind();
}
}
}

Darragh29 · 07-05-2009 2:30pm

This is my error here...

Darragh29 · 07-05-2009 2:48pm

I think the prob is with my tables and the FK's...

Darragh29 · 07-05-2009 3:54pm

Yeah the prob I'm having is defo related to the data in the tables, I have it running now and only throwing an error if I select certain items....

Darragh29 · 07-05-2009 4:44pm

Whatever way I've set up the relationships, I've the first two layers of the list working but the bottom one is not working right...

Ginger · 07-05-2009 6:34pm

Can you put the data up?

Darragh29 · 07-05-2009 6:49pm

Ginger wrote: »

Can you put the data up?

Would it be handier if I PM'd you my SQL Server login info for you to have a quick look??? I haven't worked with Foreign keys before and I was just reading up on them here and see that it has to point to a Primary Key. Should the primary key for every table be the ID column?

Ginger · 07-05-2009 6:52pm

If you wait a second i will recreate the tables with the foreign key constraints and data

Ginger · 07-05-2009 7:09pm

Righty

SQL Script attached. You may need to drop your existing tables if they are named Category, SubCat or Spec

Also there is a png file that shows how they relate

In essence, its a way to keep your data clean. All your sub categories need to have a Category ID that is in the table Category. This is where the foreign key comes in. It tells the database that the data is only valid if the catid is in Category. Same applies with Spec and the SubCat table.

Because you retrieve the information by number, you can always change the names of the categories or sub categories or specs for example without having to worry about the links between them.

Darragh29 · 07-05-2009 7:23pm

Thanks so much for that Ginger. I've run the script and before I did I dropped the tables that the script will create. The menu now has your data in it but the last layer of the menu is still blank. I'm going to check my stored procs again because I didn't touch your solution code but I was fiddling around with the stored procs earlier...

Ginger · 07-05-2009 7:32pm

The last stored proc shoudl be something like

SELECT Id,[Name] FROM Spec WHERE SubCatID = @CatID

Darragh29 · 07-05-2009 7:37pm

These are my SP's...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_select_spec]
(
@CatID INT
)
AS
SELECT ID,[Name] FROM Spec WHERE SubCatID = @CatID

***************************************************

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_select_subcat]
(
@CatID INT
)
AS
SELECT ID,[Name] FROM SubCat WHERE CatID = @CatID

***************************************************

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_select_category]
AS
SELECT Id,[Name] FROM Category

***************************************************

Ginger · 07-05-2009 7:39pm

Are you getting data in all three dropdowns?

Darragh29 · 07-05-2009 7:44pm

2/3 are fine, working away grand, just the third/last one isn't showing any data, but the data is in the table and the FK is also there when I check it...

Darragh29 · 07-05-2009 7:57pm

I copied over the DropDownDemo.aspx and cs files just in case I changed something there by mistake and its still missing the last dropdown data so it isn't the code. It's probably something stupid I've done with the database table or SP's...

Ginger · 07-05-2009 8:08pm

Its an ID-Ten-T error on my part

Darragh29 · 07-05-2009 8:11pm

I can't believe it wasn't something I did! ;-)

Ginger · 07-05-2009 8:25pm

Its a bit of column A and a bit of Column B

ID != Id

So in the select SubCat stored proc change the ID to Id to make it work...

Darragh29 · 07-05-2009 9:05pm

Ginger wrote: »

Its a bit of column A and a bit of Column B

ID != Id

So in the select SubCat stored proc change the ID to Id to make it work...

That sorted it out fair play to ya!

Darragh29 · 08-05-2009 11:24am

Now for the hard part! Is getting a selection from the DD list to appear in the shopping cart just a matter of adding a button to the DropDownDemo.aspx page and binding the DD List to the Cart on the Button_Click event?

Ginger · 08-05-2009 11:39am

You see the way that I bring back the info for all items. Create something similar to just bring back the items you want..

So i created a link table

Id, SpecId, ProductId
int, int, int

What this is you can have say 1 product that falls into many specs and this allows you to set that up.

So when you have your Id of your spec you get the products that are associated with that, similar to get all products execpt you get only the ones that are belonging to that spec.

Bind that list of products to a repeater similar to how i have done it in Catalog.aspx and then you are laughing because everything else works the same once you have the basic list.

Originally I was going to put that in the same page but I wanted to show how its done first so people can see it working in its own way.

You will need to set the Primary Key on Products to the ID field before running this script

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SpecProduct](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SpecID] [int] NOT NULL,
	[ProductID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[usp_select_specProduct]    Script Date: 05/08/2009 12:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_select_specProduct]
(
@SpecID INT
)
AS
SELECT Product.Id, Product.Name, Product.Description, Product.Cost
FROM Product INNER JOIN SpecProduct ON Product.Id = SpecProduct.ProductID
WHERE SpecProduct.SpecID = @SpecID
GO
/****** Object:  ForeignKey [FK_SpecProduct_Product]    Script Date: 05/08/2009 12:39:00 ******/
ALTER TABLE [dbo].[SpecProduct]  WITH CHECK ADD  CONSTRAINT [FK_SpecProduct_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([Id])
GO
ALTER TABLE [dbo].[SpecProduct] CHECK CONSTRAINT [FK_SpecProduct_Product]
GO
/****** Object:  ForeignKey [FK_SpecProduct_Spec]    Script Date: 05/08/2009 12:39:00 ******/
ALTER TABLE [dbo].[SpecProduct]  WITH CHECK ADD  CONSTRAINT [FK_SpecProduct_Spec] FOREIGN KEY([SpecID])
REFERENCES [dbo].[Spec] ([ID])
GO
ALTER TABLE [dbo].[SpecProduct] CHECK CONSTRAINT [FK_SpecProduct_Spec]
GO

You will then need to make sure that all your products have a spec (and its just numbers)

Next thing is you will need to create a method in the data access layer that accepts an int and returns a list of products

Same in the business layer

Once you have that, you can copy the repeater and its code to get yourself up and running

Darragh29 · 08-05-2009 12:10pm

I've a table I created yesterday called ProductSpec (ID, ProductID, SpecID, int, int,int), should I just drop this table and run the new SQL script above?

Ginger · 08-05-2009 12:51pm

Same same, just remember to add the keys and also change the stored proc...

Darragh29 · 08-05-2009 12:56pm

But they are different names, the table I made yesterday is ProductSpec and in your script the table is called SpecProduct. I'd just want to make sure any other references to the table are valid. I'm going to drop the table and use your script as I don't trust myself with the keys...

Darragh29 · 08-05-2009 12:57pm

Right, that's done, so I've a table called SpecProduct and I've a SP created to Select from that table...

Darragh29 · 08-05-2009 1:03pm

The only thing I'm not sure about is that in my head I see this as being an "Event" based thing. Like you click a button and something happens on that event, am I right in looking at it like that?

Ginger · 08-05-2009 2:11pm

make sure you look at how tho join is done as i did it in previous script. I thought i included a stored proc as well. The event idea is simply that. You tell the said that you are doing something ie an event. Most things are events such as the index changing or a button press or a post back for example. I will be offline tho for the next few days but i will probably have all this written for another blog post by monday

Darragh29 · 08-05-2009 2:16pm

Ginger wrote: »

make sure you look at how tho join is done as i did it in previous script. I thought i included a stored proc as well. The event idea is simply that. You tell the said that you are doing something ie an event. Most things are events such as the index changing or a button press or a post back for example. I will be offline tho for the next few days but i will probably have all this written for another blog post by monday

I'll see if I can get it up and running over the weekend. I've added a button to he DropDownDemo.aspx page, so just wondering do I put the databind code into the codebehind function for the button_click event handler?

Ginger · 08-05-2009 6:09pm

see thats the thing you dont need a button you just need to bind the data to a repeater just like how catalog does it. In fact just copy out the repeater and related events to the dropdown file and databind it in the selectedindex changed event of the spec dropdown using the selected value in spec to get the correct products. Because the repeater is set up to add to the shopping cart and its in session it will carry across pages easily as long as you add cart items to it. Make sense

Darragh29 · 08-05-2009 7:19pm

I'm almost there with it now...

Ginger · 08-05-2009 8:38pm

Ehhh sounds like you copied in namespaces and everything

Copy in the repeater first from the source of Catalog.aspx

then copy in the in the bits such as RepeaterEvent

then add the Event handler in the init part...

Darragh29 · 08-05-2009 9:02pm

Ginger wrote: »

Ehhh sounds like you copied in namespaces and everything

Copy in the repeater first from the source of Catalog.aspx

then copy in the in the bits such as RepeaterEvent

then add the Event handler in the init part...

Yeah I got that sorted out, I'm just trying to hook everything up now. It's all on the same page now which is progress!

Ginger · 09-05-2009 10:47am

Ok

I just finished adding the Dropdowns and all that to the Catalog page and showing how to bind the information correctly etc.

I will be putting that up later today if I get a chance along with a post explaining how it all works

Ginger · 09-05-2009 11:54am

Oki doki the updated solution file and explanation of how it hangs together is online here

C# shopping basket, trying to remove an item...

Comments