how to select products by category

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

how to select products by category

Author

Topic

joanne
Starting Member

46 Posts

Posted - 2005-12-08 : 20:30:41

Hi,

I need your help, please!
I my aspx page I want to retrieve information from a sql server database and i don’t know how to select products by category
I want it to be something like that:

Category 1
Product 1
Product 2
Product 3
Product 4

Category 2
Product 1
Product 2

Category 3
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-08 : 20:39:47

can you post your table structure with sample data and expected result ?

-----------------
[KH]
Guys, where are we right now ?

joanne
Starting Member

46 Posts

Posted - 2005-12-08 : 21:04:10

Hi,

Thanks for your prompted answer!
I want to have something similarly with tables products and category from Northwind db.

Product tbl:

ProductID, ProductName, CategoryId

Category tbl:
CategoryId, CategoryName, description.

In my aspx page:
Category 1
ProductName 1, description

Category 2
ProductName 1, description
ProductName 2, description

Category 3
ProductName 1, description
ProductName 2, description
ProductName 3, description

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-08 : 21:09:24

is this what you want ?

select ProductID, ProductName
from   Product
where  CategoryID = @CategoryID

-----------------
[KH]
Guys, where are we right now ?

joanne
Starting Member

46 Posts

Posted - 2005-12-08 : 23:36:26

Ho is @CategoryID?
I will take this variable from?
This is my reel example:
Product_ID Sub_classes Classe_name Classe_ID
1 All Purpose Cleaners Industrial Products 1
2 Car & Truck Washing Industrial Products 1
3 Degreasing & Pressure Washing Industrial Products 1
4 Disinfectant Cleaners Industrial Products 1
5 Plant Cleaners & Sanitizers Industrial Products 1
6 Hand Soap & Hand Cleaners Industrial Products 1
7 Metal Surface Cleaners Industrial Products 1
8 HVAC Piping Industrial Products 1
9 Aluminum Based Coagulants Water treatment 5
10 Iron Based Coagulants Water treatment 5
11 Custom made products Water treatment 5
12 Bathroom & Tile Cleaners Janitorial and maintenace 2
13 Carpet Care Janitorial and maintenace 2
14 Disinfectant Cleaners Janitorial and maintenace 2

So, for industrial product I have 6 classes.
I want to add data in my aspx page like that

Industrial product

All Purpose Cleaners
Car & Truck Washing
Degreasing & Pressure Washing
Disinfectant Cleaners
Plant Cleaners & Sanitizers
Hand Soap & Hand Cleaners
Metal Surface Cleaners
HVAC Piping

Water treatment

Aluminum Based Coagulants
Iron Based Coagulants
Custom made products

And so one

jhermiz

3564 Posts

Posted - 2005-12-09 : 11:56:36

So you want to see the products based a a Category ?
What he / she posted is what you need.

If you want to see all water treatment products you would have
SELECT Product, ProductName FROM yourTableNamehere WHERE CategoryID = 5
This will return those 3.

If you want to display info from two tables you will want to use a join:

SELECT p.ProductName, p.ProductID, c.CategoryName, c.CategoryID FROM Products p
INNER JOIN Categories c ON c.CategoryID = p.CategoryID

Finally in the previous example @ProductID is a variable in a stored procedure, which means whatever value is snatched it is placed in that variable @ProductID, and used in the SQL to obtain the data. @ProductID could contain any allowable value from your application.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

joanne
Starting Member

46 Posts

Posted - 2005-12-09 : 13:52:43

Hi,
I will tray to explain again.
I want to send in my web page information from the database.
With my code, I see the category for every product but I what to see just one time the category for all product.

This is my code:

Sub Page_Load(Sender As Object, E As EventArgs)
dim dbread
Dim MyConnection
MyConnection = New SqlConnection("server=royal;database=aqua;UID=sa;password=xxxx")
MyConnection.Open()
If Not (Page.IsPostBack)
Dim DS As DataSet = New DataSet()

Dim myCommand as New OleDbCommand ("select * from Products order by Classe_name,Sub_classes ", MyConnection)
Dim myDataAdapter as New OleDbDataAdapter(myCommand)

myDataAdapter.Fill(DS, "Products")
MyDataList.DataSource = DS.Tables("Products").DefaultView
MyDataList.DataBind()
End If
End Sub
</script>

<body topmargin="0" leftmargin="0" marginwidth="0" marginheight="0">

<ASP:DataList id="MyDataList" RepeatColumns="3" RepeatDirection="Horizontal" runat="server">

<ItemTemplate>

<div style="padding:15,15,15,15;font-size:10pt;font-family:Verdana">

<div style="font:12pt verdana;color:darkred">
<i><b><%# DataBinder.Eval(Container.DataItem, "Classe_name") %></i></b>
</div>

<br>

<b>Subclasses: </b><%# DataBinder.Eval(Container.DataItem, "Sub_classes") %><br>

</div>

</ItemTemplate>

</ASP:DataList>

And this is my web page:

Industrial Products
Subclasses: All Purpose Cleaners

Industrial Products
Subclasses: Disinfectant Cleaners

Industrial Products
Subclasses: Metal Surface Cleaners

Janitorial and maintenace
Subclasses: Carpet Care

Janitorial and maintenace
Subclasses: Disinfectant

Janitorial and maintenace
Subclasses: Bathroom & Tile Cleaners

Water treatment
Subclasses: Custom made products

For example, I want to see Industrial Product oen time and all products for this,one time janitorial and all product for this and so onw.
Thanks very much and sorry for my english

jhermiz

3564 Posts

Posted - 2005-12-09 : 13:55:43

Sorry I am not understanding the issue at hand, maybe someone else can help?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources