Hi everyone. I'm currently a CIS student working on a project for my final class. I'm developing a website
using .asp and am having difficulty with a certain page on my site. This page handles retrieving product information from my database and posting it. This is the code I am using currently and it works fine but its basically a scale down version of what I want
it to do.
' Get the Product ID
productID = TRIM( Request( "pid" ) )
' Get product category
category = TRIM( Request("cat") )
' Open the Database Connection
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=SQLOLEDB; DATASOURCE=; DATABASE=; USER ID=; PASSWORD=;"
' Get the Product Information
sqlString = "SELECT * FROM Product "
sqlString = sqlString & "WHERE product_id=" & productID
Set RS = Server.CreateObject( "ADODB.Recordset" )
RS.ActiveConnection = Con
RS.Open sqlString
' Get Current Category
cat = RS( "product_type" )
The area I am having issue with is in Getting the product information. What I am trying to do is get product information off of the product table and other tables that would correspond to the product_type(category) . Currently the Product types are Book, DVD,
Game. For example if cat = 1 (DVD) i want to post Title, studio, director and if cat=2(book) i want to post the ISBN, author, etc.
What I think I should do is use an IF statment here like this
IF cat = 1
begin
select ___, ___, __
from product join dvd __
__
End
IF cat = 2
begin
select __, __, __
from ____ ___ __
First off is this the right approach to solving this issue? Second and the part that I am stuck on is how I would implement this. What I mean by this is Im not sure how exactly to code this in asp. Particularly how I would reference what Category is being selected.
My database is also in MS SQL 2008
I think the best plan is for you to learn how to select data from more than one table. I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
From your description, you could use Dynamic SQL in your project. Get your categary_type and select columns from sql query , then set them to temp parameters, execute your dynamic SQL return the final result.
If the problem still here, please post your table structure. It will provide us more information about your problem.
msharia
0 Points
1 Post
Help with a SQL statement *Novice User*
Feb 29, 2012 03:46 PM|LINK
Hi everyone. I'm currently a CIS student working on a project for my final class. I'm developing a website using .asp and am having difficulty with a certain page on my site. This page handles retrieving product information from my database and posting it. This is the code I am using currently and it works fine but its basically a scale down version of what I want it to do.
' Get the Product ID
productID = TRIM( Request( "pid" ) )
' Get product category
category = TRIM( Request("cat") )
' Open the Database Connection
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "PROVIDER=SQLOLEDB; DATA SOURCE=; DATABASE=; USER ID=; PASSWORD=;"
' Get the Product Information
sqlString = "SELECT * FROM Product "
sqlString = sqlString & "WHERE product_id=" & productID
Set RS = Server.CreateObject( "ADODB.Recordset" )
RS.ActiveConnection = Con
RS.Open sqlString
' Get Current Category
cat = RS( "product_type" )
The area I am having issue with is in Getting the product information. What I am trying to do is get product information off of the product table and other tables that would correspond to the product_type(category) . Currently the Product types are Book, DVD, Game. For example if cat = 1 (DVD) i want to post Title, studio, director and if cat=2(book) i want to post the ISBN, author, etc.
What I think I should do is use an IF statment here like this
IF cat = 1
begin
select ___, ___, __
from product join dvd __
__
End
IF cat = 2
begin
select __, __, __
from ____ ___ __
First off is this the right approach to solving this issue? Second and the part that I am stuck on is how I would implement this. What I mean by this is Im not sure how exactly to code this in asp. Particularly how I would reference what Category is being selected. My database is also in MS SQL 2008
Thanks in advance for any and all pointers
I know these forums usually have set procedures in posting code etiquette, excuse me if I posted this incorrectly.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Help with a SQL statement *Novice User*
Feb 29, 2012 04:00 PM|LINK
I think the best plan is for you to learn how to select data from more than one table. I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
Chen Yu - MS...
All-Star
21598 Points
2493 Posts
Microsoft
Re: Help with a SQL statement *Novice User*
Mar 06, 2012 07:54 AM|LINK
Hi mssharia,
From your description, you could use Dynamic SQL in your project. Get your categary_type and select columns from sql query , then set them to temp parameters, execute your dynamic SQL return the final result.
If the problem still here, please post your table structure. It will provide us more information about your problem.
Here is blog about Dynamic SQL : http://www.benkotips.com/pages/DynamicSQL.aspx
Thanks.
Feedback to us
Develop and promote your apps in Windows Store