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...

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

Comments