Ask a Question related to ASP Database, Design and Development.
-
Justin Gregory #1
Multiple Recordsets
OK, I know there has to be a better way to do this, hopefully someone
can shed some light on this subject.
Using SQLServer 2000 and VBscript ASP, I have a product page which is
pulling data from many different tables: Tbl_Summary, Tbl_features,
Tbl_Price, Tbl_Reviews, etc. using ProductID as the primary Key. I am
currently using a dozen recordsets to display the data on a page but I
have
read this is not good design as it puts a heavy load on the SQL Server.
The page is loading quickly even during heavy traffic periods, but
still, I want to redo this and make it right. As some tables are
One-to-One and others are One-to-Many I cannot use INNER or OUTER JOINS.
So, how can I do this more efficiently than having 12 recordsets on the
same page? I hope this made sense.
Thanks in advance for any insight.
Justin
*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!
Justin Gregory Guest
-
Multiple paged recordsets
Does anyone know how to place two or more INDEPENDENTLY PAGEABLE different sized recordsets on the same output page using DM8/ASP? I have created... -
Multiple Recordsets and Stored Procedure
I'm using a stored procedure that returns multiple recordsets (it contains multiple select statements) in a vb.net dev environment. The problem is... -
Multiple recordsets?
Hey all I'm not sure if there's anything that can be done about this or not. I'm displaying the contents of two different tables (using Access for... -
Getting Multiple Recordsets from an SP
I am using ASP 3.0/ADO to call an SP. The SP has multiple SELECT statements so I need to get the multiple return sets but I cannot find the right... -
Should open multiple recordsets, or just one?
Hi, I find myself once again returning to a problem project I have been so far unable to finish... I have a table of teaching session data and... -
McKirahan #2 Re: Multiple Recordsets
"Justin Gregory" <jgarbett_nospamplease@iomagic.com> wrote in message
news:uI$iwMvNEHA.3812@TK2MSFTNGP12.phx.gbl...> OK, I know there has to be a better way to do this, hopefully someone
> can shed some light on this subject.
>
> Using SQLServer 2000 and VBscript ASP, I have a product page which is
> pulling data from many different tables: Tbl_Summary, Tbl_features,
> Tbl_Price, Tbl_Reviews, etc. using ProductID as the primary Key. I am
> currently using a dozen recordsets to display the data on a page but I
> have
> read this is not good design as it puts a heavy load on the SQL Server.
> The page is loading quickly even during heavy traffic periods, but
> still, I want to redo this and make it right. As some tables are
> One-to-One and others are One-to-Many I cannot use INNER or OUTER JOINS.
> So, how can I do this more efficiently than having 12 recordsets on the
> same page? I hope this made sense.
> Thanks in advance for any insight.
> Justin
>
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!
How dynamic is the data?
Perhaps you could build an intermediate table periodically (nightly?).
McKirahan Guest
-
Ray at #3 Re: Multiple Recordsets
We'd have to see the queries and the DDL of your database
([url]http://www.aspfaq.com/etiquette.asp?id=5006[/url]).
Ray at home
"Justin Gregory" <jgarbett_nospamplease@iomagic.com> wrote in message
news:uI$iwMvNEHA.3812@TK2MSFTNGP12.phx.gbl...> OK, I know there has to be a better way to do this, hopefully someone
> can shed some light on this subject.
>
> Using SQLServer 2000 and VBscript ASP, I have a product page which is
> pulling data from many different tables: Tbl_Summary, Tbl_features,
> Tbl_Price, Tbl_Reviews, etc. using ProductID as the primary Key. I am
> currently using a dozen recordsets to display the data on a page but I
> have
> read this is not good design as it puts a heavy load on the SQL Server.
> The page is loading quickly even during heavy traffic periods, but
> still, I want to redo this and make it right. As some tables are
> One-to-One and others are One-to-Many I cannot use INNER or OUTER JOINS.
> So, how can I do this more efficiently than having 12 recordsets on the
> same page? I hope this made sense.
> Thanks in advance for any insight.
> Justin
>
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!
Ray at Guest
-
Justin Gregory #4 Re: Multiple Recordsets
Oops, I'm new. ok, here are the tables and queries, there are some
redundant tables in here that I had to include for legacy data,
eventually they will be phased out:
CREATE TABLE [dbo].[Category_Tbl] (
[CategoryID] [smallint] IDENTITY (1, 1) NOT NULL ,
[Prod_Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BrandID] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Op_System_Tbl] (
[Op_SysID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Op_System] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Addendum_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddendumID] [int] IDENTITY (1, 1) NOT NULL ,
[Addendum_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Addendum_Data] [char] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[PRdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Box_Contents] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contents] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContentID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Boxshots_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Drivers_Tbl] (
[DriverID] [int] IDENTITY (1, 1) NOT NULL ,
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Driver_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ChipsetID] [smallint] NULL ,
[Driver_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[File_Size] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Op_SysID] [tinyint] NULL ,
[PRdate] [datetime] NULL ,
[PRcounter] [numeric](10, 0) NULL ,
[FileType] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chipset] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Features] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FeatureID] [int] IDENTITY (1, 1) NOT NULL ,
[Feature] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Feature_Details] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_HighRes_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Image_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Prod_Image_Path] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Manuals_Tbl] (
[ManualID] [int] IDENTITY (1, 1) NOT NULL ,
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Manual_Title] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Manual_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[File_Size] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ChipsetID] [smallint] NULL ,
[PRdate] [datetime] NULL ,
[PRcounter] [numeric](10, 0) NULL ,
[File_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chipset2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_OpSys_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Op_SysID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Package_Contents] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContentsID] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Package_contents_Key] (
[ContentsID] [smallint] IDENTITY (1, 1) NOT NULL ,
[Contents] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CategoryID] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Reviews_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReviewID] [int] IDENTITY (1, 1) NOT NULL ,
[Review_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Review_Data] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Review_Link] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PRdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Specification_Key] (
[SpecificationID] [smallint] IDENTITY (1, 1) NOT NULL ,
[Specification] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CategoryID] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Specs] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Spec_Header] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Spec_Data] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Specs_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SpecificationID] [smallint] NULL ,
[Spec_Data] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Summary] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Prod_Summary] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_SysReq] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SysReqID] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_SysReq_Key] (
[SysReqID] [smallint] IDENTITY (1, 1) NOT NULL ,
[SysReq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CategoryID] [smallint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_SystemReq] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SysReq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProdName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BrandID] [smallint] NULL ,
[CategoryID] [smallint] NULL ,
[Date_Entered] [datetime] NULL ,
[short_description] [varchar] (4000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[showprod] [bit] NULL ,
[show_on_frontpage] [bit] NULL ,
[legacy] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_thumbnail_Tbl] (
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_thumbnails] (
[boxshotID] [int] IDENTITY (1, 1) NOT NULL ,
[ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[boxshot_path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Date_Added] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sfProducts] (
[prodID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[prodCategoryId] [int] NULL ,
[prodManufacturerId] [int] NULL ,
[prodVendorId] [int] NULL ,
[prodName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prodNamePlural] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[prodShortDescription] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[prodDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prodMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prodImageSmallPath] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[prodImageLargePath] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[prodLink] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prodPrice] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prodWeight] [float] NULL ,
[prodShip] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prodShipIsActive] [smallint] NULL ,
[prodCountryTaxIsActive] [smallint] NULL ,
[prodStateTaxIsActive] [smallint] NULL ,
[prodEnabledIsActive] [smallint] NULL ,
[prodAttrNum] [int] NULL ,
[prodSaleIsActive] [smallint] NULL ,
[prodSalePrice] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[prodDateAdded] [datetime] NULL ,
[prodDateModified] [datetime] NULL ,
[prodLength] [float] NULL ,
[prodWidth] [float] NULL ,
[prodHeight] [float] NULL ,
[prodFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[upsize_ts] [binary] (8) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Category_Tbl] WITH NOCHECK ADD
CONSTRAINT [PK_Category_Tbl] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Op_System_Tbl] WITH NOCHECK ADD
CONSTRAINT [PK_Op_System_Tbl] PRIMARY KEY CLUSTERED
(
[Op_SysID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Box_Contents] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Box_Contents] PRIMARY KEY CLUSTERED
(
[ContentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Drivers_Tbl] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Drivers_Tbl] PRIMARY KEY CLUSTERED
(
[DriverID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Features] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Features] PRIMARY KEY CLUSTERED
(
[FeatureID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Manuals_Tbl] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Manuals_Tbl] PRIMARY KEY CLUSTERED
(
[ManualID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Package_contents_Key] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Package_contents] PRIMARY KEY CLUSTERED
(
[ContentsID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Specification_Key] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Specification_Key] PRIMARY KEY CLUSTERED
(
[SpecificationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Summary] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Summary] PRIMARY KEY CLUSTERED
(
[ProdID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_SysReq_Key] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_SysReq] PRIMARY KEY CLUSTERED
(
[SysReqID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Tbl] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_Tbl] PRIMARY KEY CLUSTERED
(
[ProdID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_thumbnails] WITH NOCHECK ADD
CONSTRAINT [PK_Prod_thumbnails] PRIMARY KEY CLUSTERED
(
[ProdID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Prod_Addendum_Tbl] WITH NOCHECK ADD
CONSTRAINT [DF_Prod_Addendum_Tbl_PRdate] DEFAULT (getdate()) FOR
[PRdate]
GO
ALTER TABLE [dbo].[Prod_Drivers_Tbl] WITH NOCHECK ADD
CONSTRAINT [DF_Prod_Drivers_Tbl_PRdate] DEFAULT (getdate()) FOR
[PRdate],
CONSTRAINT [DF_Prod_Drivers_Tbl_PRcounter] DEFAULT (0) FOR [PRcounter]
GO
ALTER TABLE [dbo].[Prod_Manuals_Tbl] WITH NOCHECK ADD
CONSTRAINT [DF_Prod_Manuals_Tbl_PRdate] DEFAULT (getdate()) FOR
[PRdate],
CONSTRAINT [DF_Prod_Manuals_Tbl_PRcounter] DEFAULT (0) FOR [PRcounter]
GO
ALTER TABLE [dbo].[Prod_Reviews_Tbl] WITH NOCHECK ADD
CONSTRAINT [DF_Prod_Reviews_Tbl_PRdate] DEFAULT (getdate()) FOR
[PRdate]
GO
ALTER TABLE [dbo].[Prod_Tbl] WITH NOCHECK ADD
CONSTRAINT [DF_Prod_Tbl_DateEntered] DEFAULT (getdate()) FOR
[Date_Entered],
CONSTRAINT [DF_Prod_Tbl_showprod] DEFAULT (0) FOR [showprod],
CONSTRAINT [DF_Prod_Tbl_show_on_frontpage] DEFAULT (0) FOR
[show_on_frontpage],
CONSTRAINT [DF_Prod_Tbl_legacy] DEFAULT (0) FOR [legacy]
GO
ALTER TABLE [dbo].[Prod_thumbnails] WITH NOCHECK ADD
CONSTRAINT [DF_Index_Page_Products_Date_Added] DEFAULT (getdate()) FOR
[Date_Added]
GO
ALTER TABLE [dbo].[Prod_Addendum_Tbl] ADD
CONSTRAINT [FK_Prod_Addendum_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Box_Contents] ADD
CONSTRAINT [FK_Prod_Box_Contents_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
)
GO
ALTER TABLE [dbo].[Prod_Boxshots_Tbl] ADD
CONSTRAINT [FK_Prod_Boxshots_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Drivers_Tbl] ADD
CONSTRAINT [FK_Prod_Drivers_Tbl_Chipset_Tbl] FOREIGN KEY
(
[ChipsetID]
) REFERENCES [dbo].[Chipset_Tbl] (
[ChipsetID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Prod_Drivers_Tbl_Op_System_Tbl] FOREIGN KEY
(
[Op_SysID]
) REFERENCES [dbo].[Op_System_Tbl] (
[Op_SysID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Prod_Drivers_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Features] ADD
CONSTRAINT [FK_Prod_Features_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_HighRes_Tbl] ADD
CONSTRAINT [FK_Prod_HighRes_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Image_Tbl] ADD
CONSTRAINT [FK_Prod_Image_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Manuals_Tbl] ADD
CONSTRAINT [FK_Prod_Manuals_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_OpSys_Tbl] ADD
CONSTRAINT [FK_Prod_OpSys_Tbl_Op_System_Tbl] FOREIGN KEY
(
[Op_SysID]
) REFERENCES [dbo].[Op_System_Tbl] (
[Op_SysID]
),
CONSTRAINT [FK_Prod_OpSys_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Package_Contents] ADD
CONSTRAINT [FK_Prod_Package_Contents_Prod_Package_contents_Key] FOREIGN
KEY
(
[ContentsID]
) REFERENCES [dbo].[Prod_Package_contents_Key] (
[ContentsID]
),
CONSTRAINT [FK_Prod_Package_Contents_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Package_contents_Key] ADD
CONSTRAINT [FK_Prod_Package_contents_Key_Category_Tbl] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Category_Tbl] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[Prod_Reviews_Tbl] ADD
CONSTRAINT [FK_Prod_Reviews_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Specification_Key] ADD
CONSTRAINT [FK_Prod_Specification_Key_Category_Tbl] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Category_Tbl] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[Prod_Specs_Tbl] ADD
CONSTRAINT [FK_Prod_Specs_Tbl_Prod_Specification_Key] FOREIGN KEY
(
[SpecificationID]
) REFERENCES [dbo].[Prod_Specification_Key] (
[SpecificationID]
),
CONSTRAINT [FK_Prod_Specs_Tbl_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_Summary] ADD
CONSTRAINT [FK_Prod_Summary_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_SysReq] ADD
CONSTRAINT [FK_Prod_SysReq_Prod_SysReq_Key] FOREIGN KEY
(
[SysReqID]
) REFERENCES [dbo].[Prod_SysReq_Key] (
[SysReqID]
),
CONSTRAINT [FK_Prod_SysReq_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_SysReq_Key] ADD
CONSTRAINT [FK_Prod_SysReq_Key_Category_Tbl] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Category_Tbl] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[Prod_Tbl] ADD
CONSTRAINT [FK_Prod_Tbl_Brand_Tbl] FOREIGN KEY
(
[BrandID]
) REFERENCES [dbo].[Brand_Tbl] (
[BrandID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Prod_Tbl_Category_Tbl] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Category_Tbl] (
[CategoryID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Prod_thumbnails] ADD
CONSTRAINT [FK_Prod_thumbnails_Prod_Tbl] FOREIGN KEY
(
[ProdID]
) REFERENCES [dbo].[Prod_Tbl] (
[ProdID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
-------------------------------------------------------
The Queries (Macromedia code that I have tweaked a bit):
<%
Dim ProdID
ProdID = Trim(Request.Form("ProdID"))
Dim RSproducts__MMColParam
RSproducts__MMColParam = ProdID
set RSproducts = Server.CreateObject("ADODB.Recordset")
RSproducts.ActiveConnection = MM_Products_STRING
RSproducts.Source = "SELECT ProdID, ProdName, CategoryID,
short_description FROM dbo.Prod_Tbl WHERE ProdID = '" +
Replace(RSproducts__MMColParam, "'", "''") + "'"
RSproducts.CursorType = 0
RSproducts.CursorLocation = 2
RSproducts.LockType = 3
RSproducts.Open()
RSproducts_numRows = 0
Dim RSfeatures__MMColParam
RSfeatures__MMColParam = ProdID
set RSfeatures = Server.CreateObject("ADODB.Recordset")
RSfeatures.ActiveConnection = MM_Products_STRING
RSfeatures.Source = "SELECT * FROM dbo.Prod_Features WHERE ProdID = '" +
Replace(RSfeatures__MMColParam, "'", "''") + "'
ORDER BY Feature ASC"
RSfeatures.CursorType = 0
RSfeatures.CursorLocation = 2
RSfeatures.LockType = 3
RSfeatures.Open()
RSfeatures_numRows = 0
Dim RSrequirements__MMColParam
RSrequirements__MMColParam = ProdID
set RSrequirements = Server.CreateObject("ADODB.Recordset")
RSrequirements.ActiveConnection = MM_Products_STRING
RSrequirements.Source = "SELECT dbo.Prod_SysReq.ProdID,
dbo.Prod_SysReq_Key.SysReq FROM dbo.Prod_SysReq INNER JOIN
dbo.Prod_SysReq_Key ON dbo.Prod_SysReq.SysReqID =
dbo.Prod_SysReq_Key.SysReqID WHERE dbo.Prod_SysReq.ProdID
= '" + Replace(RSrequirements__MMColParam, "'", "''") + "'"
RSrequirements.CursorType = 0
RSrequirements.CursorLocation = 2
RSrequirements.LockType = 3
RSrequirements.Open()
RSrequirements_numRows = 0
Dim RScontents__MMColParam
RScontents__MMColParam = ProdID
set RScontents = Server.CreateObject("ADODB.Recordset")
RScontents.ActiveConnection = MM_Products_STRING
RScontents.Source = "SELECT dbo.Prod_Package_Contents.ProdID,
dbo.Prod_Package_contents_Key.Contents FROM
dbo.Prod_Package_Contents INNER JOIN
dbo.Prod_Package_contents_Key ON
dbo.Prod_Package_Contents.ContentsID =
dbo.Prod_Package_contents_Key.ContentsID WHERE
dbo.Prod_Package_Contents.ProdID =
'" + Replace(RScontents__MMColParam, "'", "''") + "'"
RScontents.CursorType = 0
RScontents.CursorLocation = 2
RScontents.LockType = 3
RScontents.Open()
RScontents_numRows = 0
Dim RSspecifications__MMColParam
RSspecifications__MMColParam = ProdID
set RSspecifications = Server.CreateObject("ADODB.Recordset")
RSspecifications.ActiveConnection = MM_Products_STRING
RSspecifications.Source = "SELECT dbo.Prod_Specs_Tbl.SpecificationID,
dbo.Prod_Specs_Tbl.Spec_Data,
dbo.Prod_Specification_Key.Specification FROM
dbo.Prod_Specification_Key INNER JOIN
dbo.Prod_Specs_Tbl ON dbo.Prod_Specification_Key.SpecificationID =
dbo.Prod_Specs_Tbl.SpecificationID WHERE ProdID = '" +
Replace(RSspecifications__MMColParam, "'", "''") + "'"
RSspecifications.CursorType = 0
RSspecifications.CursorLocation = 2
RSspecifications.LockType = 3
RSspecifications.Open()
RSspecifications_numRows = 0
Dim RSdrivers__MMColParam
RSdrivers__MMColParam = ProdID
set RSdrivers = Server.CreateObject("ADODB.Recordset")
RSdrivers.ActiveConnection = MM_Products_STRING
RSdrivers.Source = "SELECT ProdID FROM dbo.Prod_Drivers_Tbl WHERE
ProdID = '" + Replace(RSdrivers__MMColParam, "'", "''")
+ "'"
RSdrivers.CursorType = 0
RSdrivers.CursorLocation = 2
RSdrivers.LockType = 3
RSdrivers.Open()
RSdrivers_numRows = 0
Dim RSmanuals__MMColParam
RSmanuals__MMColParam = ProdID
set RSmanuals = Server.CreateObject("ADODB.Recordset")
RSmanuals.ActiveConnection = MM_Products_STRING
RSmanuals.Source = "SELECT ProdID FROM dbo.Prod_Manuals_Tbl WHERE
ProdID = '" + Replace(RSmanuals__MMColParam, "'", "''")
+ "'"
RSmanuals.CursorType = 0
RSmanuals.CursorLocation = 2
RSmanuals.LockType = 3
RSmanuals.Open()
RSmanuals_numRows = 0
Dim RSreviews__MMColParam
RSreviews__MMColParam = ProdID
set RSreviews = Server.CreateObject("ADODB.Recordset")
RSreviews.ActiveConnection = MM_Products_STRING
RSreviews.Source = "SELECT ProdID FROM dbo.Prod_Reviews_Tbl WHERE
ProdID = '" + Replace(RSreviews__MMColParam, "'", "''")
+ "'"
RSreviews.CursorType = 0
RSreviews.CursorLocation = 2
RSreviews.LockType = 3
RSreviews.Open()
RSreviews_numRows = 0
Dim RShighres__MMColParam
RShighres__MMColParam = ProdID
set RShighres = Server.CreateObject("ADODB.Recordset")
RShighres.ActiveConnection = MM_Products_STRING
RShighres.Source = "SELECT ProdID, Image_Path FROM dbo.Prod_HighRes_Tbl
WHERE ProdID = '" +
Replace(RShighres__MMColParam, "'", "''") + "'"
RShighres.CursorType = 0
RShighres.CursorLocation = 2
RShighres.LockType = 3
RShighres.Open()
RShighres_numRows = 0
Dim RSboxshot__MMColParam
RSboxshot__MMColParam = ProdID
set RSboxshot = Server.CreateObject("ADODB.Recordset")
RSboxshot.ActiveConnection = MM_Products_STRING
RSboxshot.Source = "SELECT ProdID, Image_Path FROM
dbo.Prod_Boxshots_Tbl WHERE ProdID = '" +
Replace(RSboxshot__MMColParam, "'", "''") + "'"
RSboxshot.CursorType = 0
RSboxshot.CursorLocation = 2
RSboxshot.LockType = 3
RSboxshot.Open()
RSboxshot_numRows = 0
Dim RSsummary__MMColParam
RSsummary__MMColParam = ProdID
set RSsummary = Server.CreateObject("ADODB.Recordset")
RSsummary.ActiveConnection = MM_Products_STRING
RSsummary.Source = "SELECT * FROM dbo.Prod_Summary WHERE ProdID = '" +
Replace(RSsummary__MMColParam, "'", "''") + "'"
RSsummary.CursorType = 0
RSsummary.CursorLocation = 2
RSsummary.LockType = 3
RSsummary.Open()
RSsummary_numRows = 0
Dim RSaddendum__MMColParam
RSaddendum__MMColParam = ProdID
set RSaddendum = Server.CreateObject("ADODB.Recordset")
RSaddendum.ActiveConnection = MM_Products_STRING
RSaddendum.Source = "SELECT * FROM dbo.Prod_Addendum_Tbl WHERE ProdID
= '" + Replace(RSaddendum__MMColParam, "'", "''") +
"'"
RSaddendum.CursorType = 0
RSaddendum.CursorLocation = 2
RSaddendum.LockType = 3
RSaddendum.Open()
RSaddendum_numRows = 0
Dim RSprod_image__MMColParam
RSprod_image__MMColParam = ProdID
set RSprod_image = Server.CreateObject("ADODB.Recordset")
RSprod_image.ActiveConnection = MM_Products_STRING
RSprod_image.Source = "SELECT * FROM dbo.Prod_Image_Tbl WHERE ProdID =
'" + Replace(RSprod_image__MMColParam, "'", "''")
+ "'"
RSprod_image.CursorType = 0
RSprod_image.CursorLocation = 2
RSprod_image.LockType = 3
RSprod_image.Open()
RSprod_image_numRows = 0
Dim RSopsys__MMColParam
RSopsys__MMColParam = ProdID
set RSopsys = Server.CreateObject("ADODB.Recordset")
RSopsys.ActiveConnection = MM_Products_STRING
RSopsys.Source = "SELECT dbo.Prod_OpSys_Tbl.ProdID,
dbo.Prod_OpSys_Tbl.Op_SysID, dbo.Op_System_Tbl.Op_System FROM
dbo.Prod_OpSys_Tbl INNER JOIN dbo.Op_System_Tbl
ON dbo.Prod_OpSys_Tbl.Op_SysID =
dbo.Op_System_Tbl.Op_SysID WHERE dbo.Prod_OpSys_Tbl.ProdID = '" +
Replace(RSopsys__MMColParam, "'", "''") + "'"
RSopsys.CursorType = 0
RSopsys.CursorLocation = 2
RSopsys.LockType = 3
RSopsys.Open()
RSopsys_numRows = 0
Dim RSstorefront__MMColParam
RSstorefront__MMColParam = ProdID
Dim RSstorefront
Dim RSstorefront_numRows
Set RSstorefront = Server.CreateObject("ADODB.Recordset")
RSstorefront.ActiveConnection = MM_SF_STRING
RSstorefront.Source = "SELECT prodID, prodPrice, prodEnabledIsActive,
prodSaleIsActive, prodSalePrice FROM dbo.sfProducts
WHERE prodID = '" + Replace(RSstorefront__MMColParam, "'", "''") + "'"
RSstorefront.CursorType = 0
RSstorefront.CursorLocation = 2
RSstorefront.LockType = 1
RSstorefront.Open()
RSstorefront_numRows = 0
Dim RScontents2__MMColParam
RScontents2__MMColParam = ProdID
Dim RScontents2
Dim RScontents2_numRows
Set RScontents2 = Server.CreateObject("ADODB.Recordset")
RScontents2.ActiveConnection = MM_Products_STRING
RScontents2.Source = "SELECT * FROM dbo.Prod_Box_Contents WHERE ProdID =
'" + Replace(RScontents2__MMColParam, "'", "''") +
"' ORDER BY Contents ASC"
RScontents2.CursorType = 0
RScontents2.CursorLocation = 2
RScontents2.LockType = 1
RScontents2.Open()
RScontents2_numRows = 0
Dim RSspecifications2__MMColParam
RSspecifications2__MMColParam = ProdID
Dim RSspecifications2
Dim RSspecifications2_numRows
Set RSspecifications2 = Server.CreateObject("ADODB.Recordset")
RSspecifications2.ActiveConnection = MM_Products_STRING
RSspecifications2.Source = "SELECT * FROM dbo.Prod_Specs WHERE ProdID =
'" + Replace(RSspecifications2__MMColParam, "'",
"''") + "' ORDER BY Spec_Header ASC"
RSspecifications2.CursorType = 0
RSspecifications2.CursorLocation = 2
RSspecifications2.LockType = 1
RSspecifications2.Open()
RSspecifications2_numRows = 0
Dim RSrequirements2__MMColParam
RSrequirements2__MMColParam = ProdID
Dim RSrequirements2
Dim RSrequirements2_numRows
Set RSrequirements2 = Server.CreateObject("ADODB.Recordset")
RSrequirements2.ActiveConnection = MM_Products_STRING
RSrequirements2.Source = "SELECT * FROM dbo.Prod_SystemReq WHERE ProdID
= '" + Replace(RSrequirements2__MMColParam, "'",
"''") + "'"
RSrequirements2.CursorType = 0
RSrequirements2.CursorLocation = 2
RSrequirements2.LockType = 1
RSrequirements2.Open()
RSrequirements2_numRows = 0
%>
It's a mess, I know. That's why I'm here. Any insight is greatly
appreciated.
Justin
*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!
Justin Gregory Guest
-
Aaron Bertrand - MVP #5 Re: Multiple Recordsets
I don't even know where to begin. To start, stop using Macromedia products
to write ASP code. Look at all that crap it shoves in there! 20 different
recordsets... what exactly are you trying to accomplish? Have you heard of
stored procedures?
--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]
"Justin Gregory" <justinomoralez@yahoo.com> wrote in message
news:#FB3We3NEHA.540@TK2MSFTNGP11.phx.gbl...> Oops, I'm new. ok, here are the tables and queries, there are some
> redundant tables in here that I had to include for legacy data,
> eventually they will be phased out:
>
> CREATE TABLE [dbo].[Category_Tbl] (
> [CategoryID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [Prod_Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [BrandID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Op_System_Tbl] (
> [Op_SysID] [tinyint] IDENTITY (1, 1) NOT NULL ,
> [Op_System] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Addendum_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [AddendumID] [int] IDENTITY (1, 1) NOT NULL ,
> [Addendum_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Addendum_Data] [char] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [PRdate] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Box_Contents] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Contents] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContentID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Boxshots_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Drivers_Tbl] (
> [DriverID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Driver_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ChipsetID] [smallint] NULL ,
> [Driver_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [File_Size] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Op_SysID] [tinyint] NULL ,
> [PRdate] [datetime] NULL ,
> [PRcounter] [numeric](10, 0) NULL ,
> [FileType] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Chipset] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Features] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FeatureID] [int] IDENTITY (1, 1) NOT NULL ,
> [Feature] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Feature_Details] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_HighRes_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Image_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Prod_Image_Path] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Manuals_Tbl] (
> [ManualID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Manual_Title] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Manual_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [File_Size] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ChipsetID] [smallint] NULL ,
> [PRdate] [datetime] NULL ,
> [PRcounter] [numeric](10, 0) NULL ,
> [File_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Chipset2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_OpSys_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Op_SysID] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Package_Contents] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ContentsID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Package_contents_Key] (
> [ContentsID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [Contents] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CategoryID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Reviews_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReviewID] [int] IDENTITY (1, 1) NOT NULL ,
> [Review_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Review_Data] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Review_Link] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [PRdate] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Specification_Key] (
> [SpecificationID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [Specification] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CategoryID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Specs] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Spec_Header] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Spec_Data] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Specs_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SpecificationID] [smallint] NULL ,
> [Spec_Data] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Summary] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Prod_Summary] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_SysReq] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SysReqID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_SysReq_Key] (
> [SysReqID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [SysReq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CategoryID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_SystemReq] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SysReq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProdName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BrandID] [smallint] NULL ,
> [CategoryID] [smallint] NULL ,
> [Date_Entered] [datetime] NULL ,
> [short_description] [varchar] (4000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [showprod] [bit] NULL ,
> [show_on_frontpage] [bit] NULL ,
> [legacy] [bit] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_thumbnail_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_thumbnails] (
> [boxshotID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boxshot_path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Date_Added] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[sfProducts] (
> [prodID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [prodCategoryId] [int] NULL ,
> [prodManufacturerId] [int] NULL ,
> [prodVendorId] [int] NULL ,
> [prodName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodNamePlural] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [prodShortDescription] [nvarchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodImageSmallPath] [nvarchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodImageLargePath] [nvarchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodLink] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodPrice] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodWeight] [float] NULL ,
> [prodShip] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodShipIsActive] [smallint] NULL ,
> [prodCountryTaxIsActive] [smallint] NULL ,
> [prodStateTaxIsActive] [smallint] NULL ,
> [prodEnabledIsActive] [smallint] NULL ,
> [prodAttrNum] [int] NULL ,
> [prodSaleIsActive] [smallint] NULL ,
> [prodSalePrice] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [prodDateAdded] [datetime] NULL ,
> [prodDateModified] [datetime] NULL ,
> [prodLength] [float] NULL ,
> [prodWidth] [float] NULL ,
> [prodHeight] [float] NULL ,
> [prodFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [upsize_ts] [binary] (8) NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Category_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Category_Tbl] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Op_System_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Op_System_Tbl] PRIMARY KEY CLUSTERED
> (
> [Op_SysID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Box_Contents] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Box_Contents] PRIMARY KEY CLUSTERED
> (
> [ContentID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Drivers_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Drivers_Tbl] PRIMARY KEY CLUSTERED
> (
> [DriverID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Features] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Features] PRIMARY KEY CLUSTERED
> (
> [FeatureID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Manuals_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Manuals_Tbl] PRIMARY KEY CLUSTERED
> (
> [ManualID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Package_contents_Key] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Package_contents] PRIMARY KEY CLUSTERED
> (
> [ContentsID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Specification_Key] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Specification_Key] PRIMARY KEY CLUSTERED
> (
> [SpecificationID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Summary] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Summary] PRIMARY KEY CLUSTERED
> (
> [ProdID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_SysReq_Key] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_SysReq] PRIMARY KEY CLUSTERED
> (
> [SysReqID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Tbl] PRIMARY KEY CLUSTERED
> (
> [ProdID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_thumbnails] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_thumbnails] PRIMARY KEY CLUSTERED
> (
> [ProdID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Addendum_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Addendum_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate]
> GO
>
> ALTER TABLE [dbo].[Prod_Drivers_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Drivers_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate],
> CONSTRAINT [DF_Prod_Drivers_Tbl_PRcounter] DEFAULT (0) FOR [PRcounter]
> GO
>
> ALTER TABLE [dbo].[Prod_Manuals_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Manuals_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate],
> CONSTRAINT [DF_Prod_Manuals_Tbl_PRcounter] DEFAULT (0) FOR [PRcounter]
> GO
>
> ALTER TABLE [dbo].[Prod_Reviews_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Reviews_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate]
> GO
>
> ALTER TABLE [dbo].[Prod_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Tbl_DateEntered] DEFAULT (getdate()) FOR
> [Date_Entered],
> CONSTRAINT [DF_Prod_Tbl_showprod] DEFAULT (0) FOR [showprod],
> CONSTRAINT [DF_Prod_Tbl_show_on_frontpage] DEFAULT (0) FOR
> [show_on_frontpage],
> CONSTRAINT [DF_Prod_Tbl_legacy] DEFAULT (0) FOR [legacy]
> GO
>
> ALTER TABLE [dbo].[Prod_thumbnails] WITH NOCHECK ADD
> CONSTRAINT [DF_Index_Page_Products_Date_Added] DEFAULT (getdate()) FOR
> [Date_Added]
> GO
>
> ALTER TABLE [dbo].[Prod_Addendum_Tbl] ADD
> CONSTRAINT [FK_Prod_Addendum_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Box_Contents] ADD
> CONSTRAINT [FK_Prod_Box_Contents_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Boxshots_Tbl] ADD
> CONSTRAINT [FK_Prod_Boxshots_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Drivers_Tbl] ADD
> CONSTRAINT [FK_Prod_Drivers_Tbl_Chipset_Tbl] FOREIGN KEY
> (
> [ChipsetID]
> ) REFERENCES [dbo].[Chipset_Tbl] (
> [ChipsetID]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Prod_Drivers_Tbl_Op_System_Tbl] FOREIGN KEY
> (
> [Op_SysID]
> ) REFERENCES [dbo].[Op_System_Tbl] (
> [Op_SysID]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Prod_Drivers_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Features] ADD
> CONSTRAINT [FK_Prod_Features_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_HighRes_Tbl] ADD
> CONSTRAINT [FK_Prod_HighRes_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Image_Tbl] ADD
> CONSTRAINT [FK_Prod_Image_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Manuals_Tbl] ADD
> CONSTRAINT [FK_Prod_Manuals_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_OpSys_Tbl] ADD
> CONSTRAINT [FK_Prod_OpSys_Tbl_Op_System_Tbl] FOREIGN KEY
> (
> [Op_SysID]
> ) REFERENCES [dbo].[Op_System_Tbl] (
> [Op_SysID]
> ),
> CONSTRAINT [FK_Prod_OpSys_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Package_Contents] ADD
> CONSTRAINT [FK_Prod_Package_Contents_Prod_Package_contents_Key] FOREIGN
> KEY
> (
> [ContentsID]
> ) REFERENCES [dbo].[Prod_Package_contents_Key] (
> [ContentsID]
> ),
> CONSTRAINT [FK_Prod_Package_Contents_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Package_contents_Key] ADD
> CONSTRAINT [FK_Prod_Package_contents_Key_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Reviews_Tbl] ADD
> CONSTRAINT [FK_Prod_Reviews_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Specification_Key] ADD
> CONSTRAINT [FK_Prod_Specification_Key_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Specs_Tbl] ADD
> CONSTRAINT [FK_Prod_Specs_Tbl_Prod_Specification_Key] FOREIGN KEY
> (
> [SpecificationID]
> ) REFERENCES [dbo].[Prod_Specification_Key] (
> [SpecificationID]
> ),
> CONSTRAINT [FK_Prod_Specs_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Summary] ADD
> CONSTRAINT [FK_Prod_Summary_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_SysReq] ADD
> CONSTRAINT [FK_Prod_SysReq_Prod_SysReq_Key] FOREIGN KEY
> (
> [SysReqID]
> ) REFERENCES [dbo].[Prod_SysReq_Key] (
> [SysReqID]
> ),
> CONSTRAINT [FK_Prod_SysReq_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_SysReq_Key] ADD
> CONSTRAINT [FK_Prod_SysReq_Key_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Tbl] ADD
> CONSTRAINT [FK_Prod_Tbl_Brand_Tbl] FOREIGN KEY
> (
> [BrandID]
> ) REFERENCES [dbo].[Brand_Tbl] (
> [BrandID]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Prod_Tbl_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_thumbnails] ADD
> CONSTRAINT [FK_Prod_thumbnails_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
> -------------------------------------------------------
> The Queries (Macromedia code that I have tweaked a bit):
>
> <%
> Dim ProdID
> ProdID = Trim(Request.Form("ProdID"))
>
> Dim RSproducts__MMColParam
> RSproducts__MMColParam = ProdID
>
> set RSproducts = Server.CreateObject("ADODB.Recordset")
> RSproducts.ActiveConnection = MM_Products_STRING
> RSproducts.Source = "SELECT ProdID, ProdName, CategoryID,
> short_description FROM dbo.Prod_Tbl WHERE ProdID = '" +
>
> Replace(RSproducts__MMColParam, "'", "''") + "'"
> RSproducts.CursorType = 0
> RSproducts.CursorLocation = 2
> RSproducts.LockType = 3
> RSproducts.Open()
> RSproducts_numRows = 0
>
> Dim RSfeatures__MMColParam
> RSfeatures__MMColParam = ProdID
>
> set RSfeatures = Server.CreateObject("ADODB.Recordset")
> RSfeatures.ActiveConnection = MM_Products_STRING
> RSfeatures.Source = "SELECT * FROM dbo.Prod_Features WHERE ProdID = '" +
> Replace(RSfeatures__MMColParam, "'", "''") + "'
>
> ORDER BY Feature ASC"
> RSfeatures.CursorType = 0
> RSfeatures.CursorLocation = 2
> RSfeatures.LockType = 3
> RSfeatures.Open()
> RSfeatures_numRows = 0
>
> Dim RSrequirements__MMColParam
> RSrequirements__MMColParam = ProdID
>
> set RSrequirements = Server.CreateObject("ADODB.Recordset")
> RSrequirements.ActiveConnection = MM_Products_STRING
> RSrequirements.Source = "SELECT dbo.Prod_SysReq.ProdID,
> dbo.Prod_SysReq_Key.SysReq FROM dbo.Prod_SysReq INNER JOIN
>
> dbo.Prod_SysReq_Key ON dbo.Prod_SysReq.SysReqID =
> dbo.Prod_SysReq_Key.SysReqID WHERE dbo.Prod_SysReq.ProdID
>
> = '" + Replace(RSrequirements__MMColParam, "'", "''") + "'"
> RSrequirements.CursorType = 0
> RSrequirements.CursorLocation = 2
> RSrequirements.LockType = 3
> RSrequirements.Open()
> RSrequirements_numRows = 0
>
> Dim RScontents__MMColParam
> RScontents__MMColParam = ProdID
>
> set RScontents = Server.CreateObject("ADODB.Recordset")
> RScontents.ActiveConnection = MM_Products_STRING
> RScontents.Source = "SELECT dbo.Prod_Package_Contents.ProdID,
> dbo.Prod_Package_contents_Key.Contents FROM
>
> dbo.Prod_Package_Contents INNER JOIN
> dbo.Prod_Package_contents_Key ON
>
> dbo.Prod_Package_Contents.ContentsID =
> dbo.Prod_Package_contents_Key.ContentsID WHERE
> dbo.Prod_Package_Contents.ProdID =
>
> '" + Replace(RScontents__MMColParam, "'", "''") + "'"
> RScontents.CursorType = 0
> RScontents.CursorLocation = 2
> RScontents.LockType = 3
> RScontents.Open()
> RScontents_numRows = 0
>
> Dim RSspecifications__MMColParam
> RSspecifications__MMColParam = ProdID
>
> set RSspecifications = Server.CreateObject("ADODB.Recordset")
> RSspecifications.ActiveConnection = MM_Products_STRING
> RSspecifications.Source = "SELECT dbo.Prod_Specs_Tbl.SpecificationID,
> dbo.Prod_Specs_Tbl.Spec_Data,
>
> dbo.Prod_Specification_Key.Specification FROM
> dbo.Prod_Specification_Key INNER JOIN
>
> dbo.Prod_Specs_Tbl ON dbo.Prod_Specification_Key.SpecificationID =
> dbo.Prod_Specs_Tbl.SpecificationID WHERE ProdID = '" +
>
> Replace(RSspecifications__MMColParam, "'", "''") + "'"
> RSspecifications.CursorType = 0
> RSspecifications.CursorLocation = 2
> RSspecifications.LockType = 3
> RSspecifications.Open()
> RSspecifications_numRows = 0
>
> Dim RSdrivers__MMColParam
> RSdrivers__MMColParam = ProdID
>
> set RSdrivers = Server.CreateObject("ADODB.Recordset")
> RSdrivers.ActiveConnection = MM_Products_STRING
> RSdrivers.Source = "SELECT ProdID FROM dbo.Prod_Drivers_Tbl WHERE
> ProdID = '" + Replace(RSdrivers__MMColParam, "'", "''")
>
> + "'"
> RSdrivers.CursorType = 0
> RSdrivers.CursorLocation = 2
> RSdrivers.LockType = 3
> RSdrivers.Open()
> RSdrivers_numRows = 0
>
> Dim RSmanuals__MMColParam
> RSmanuals__MMColParam = ProdID
>
> set RSmanuals = Server.CreateObject("ADODB.Recordset")
> RSmanuals.ActiveConnection = MM_Products_STRING
> RSmanuals.Source = "SELECT ProdID FROM dbo.Prod_Manuals_Tbl WHERE
> ProdID = '" + Replace(RSmanuals__MMColParam, "'", "''")
>
> + "'"
> RSmanuals.CursorType = 0
> RSmanuals.CursorLocation = 2
> RSmanuals.LockType = 3
> RSmanuals.Open()
> RSmanuals_numRows = 0
>
> Dim RSreviews__MMColParam
> RSreviews__MMColParam = ProdID
>
> set RSreviews = Server.CreateObject("ADODB.Recordset")
> RSreviews.ActiveConnection = MM_Products_STRING
> RSreviews.Source = "SELECT ProdID FROM dbo.Prod_Reviews_Tbl WHERE
> ProdID = '" + Replace(RSreviews__MMColParam, "'", "''")
>
> + "'"
> RSreviews.CursorType = 0
> RSreviews.CursorLocation = 2
> RSreviews.LockType = 3
> RSreviews.Open()
> RSreviews_numRows = 0
>
> Dim RShighres__MMColParam
> RShighres__MMColParam = ProdID
>
> set RShighres = Server.CreateObject("ADODB.Recordset")
> RShighres.ActiveConnection = MM_Products_STRING
> RShighres.Source = "SELECT ProdID, Image_Path FROM dbo.Prod_HighRes_Tbl
> WHERE ProdID = '" +
>
> Replace(RShighres__MMColParam, "'", "''") + "'"
> RShighres.CursorType = 0
> RShighres.CursorLocation = 2
> RShighres.LockType = 3
> RShighres.Open()
> RShighres_numRows = 0
>
> Dim RSboxshot__MMColParam
> RSboxshot__MMColParam = ProdID
>
> set RSboxshot = Server.CreateObject("ADODB.Recordset")
> RSboxshot.ActiveConnection = MM_Products_STRING
> RSboxshot.Source = "SELECT ProdID, Image_Path FROM
> dbo.Prod_Boxshots_Tbl WHERE ProdID = '" +
>
> Replace(RSboxshot__MMColParam, "'", "''") + "'"
> RSboxshot.CursorType = 0
> RSboxshot.CursorLocation = 2
> RSboxshot.LockType = 3
> RSboxshot.Open()
> RSboxshot_numRows = 0
>
> Dim RSsummary__MMColParam
> RSsummary__MMColParam = ProdID
>
> set RSsummary = Server.CreateObject("ADODB.Recordset")
> RSsummary.ActiveConnection = MM_Products_STRING
> RSsummary.Source = "SELECT * FROM dbo.Prod_Summary WHERE ProdID = '" +
> Replace(RSsummary__MMColParam, "'", "''") + "'"
> RSsummary.CursorType = 0
> RSsummary.CursorLocation = 2
> RSsummary.LockType = 3
> RSsummary.Open()
> RSsummary_numRows = 0
>
> Dim RSaddendum__MMColParam
> RSaddendum__MMColParam = ProdID
>
> set RSaddendum = Server.CreateObject("ADODB.Recordset")
> RSaddendum.ActiveConnection = MM_Products_STRING
> RSaddendum.Source = "SELECT * FROM dbo.Prod_Addendum_Tbl WHERE ProdID
> = '" + Replace(RSaddendum__MMColParam, "'", "''") +
>
> "'"
> RSaddendum.CursorType = 0
> RSaddendum.CursorLocation = 2
> RSaddendum.LockType = 3
> RSaddendum.Open()
> RSaddendum_numRows = 0
>
> Dim RSprod_image__MMColParam
> RSprod_image__MMColParam = ProdID
>
> set RSprod_image = Server.CreateObject("ADODB.Recordset")
> RSprod_image.ActiveConnection = MM_Products_STRING
> RSprod_image.Source = "SELECT * FROM dbo.Prod_Image_Tbl WHERE ProdID =
> '" + Replace(RSprod_image__MMColParam, "'", "''")
>
> + "'"
> RSprod_image.CursorType = 0
> RSprod_image.CursorLocation = 2
> RSprod_image.LockType = 3
> RSprod_image.Open()
> RSprod_image_numRows = 0
>
> Dim RSopsys__MMColParam
> RSopsys__MMColParam = ProdID
>
> set RSopsys = Server.CreateObject("ADODB.Recordset")
> RSopsys.ActiveConnection = MM_Products_STRING
> RSopsys.Source = "SELECT dbo.Prod_OpSys_Tbl.ProdID,
> dbo.Prod_OpSys_Tbl.Op_SysID, dbo.Op_System_Tbl.Op_System FROM
>
> dbo.Prod_OpSys_Tbl INNER JOIN dbo.Op_System_Tbl
> ON dbo.Prod_OpSys_Tbl.Op_SysID =
>
> dbo.Op_System_Tbl.Op_SysID WHERE dbo.Prod_OpSys_Tbl.ProdID = '" +
> Replace(RSopsys__MMColParam, "'", "''") + "'"
> RSopsys.CursorType = 0
> RSopsys.CursorLocation = 2
> RSopsys.LockType = 3
> RSopsys.Open()
> RSopsys_numRows = 0
>
> Dim RSstorefront__MMColParam
> RSstorefront__MMColParam = ProdID
>
> Dim RSstorefront
> Dim RSstorefront_numRows
> Set RSstorefront = Server.CreateObject("ADODB.Recordset")
> RSstorefront.ActiveConnection = MM_SF_STRING
> RSstorefront.Source = "SELECT prodID, prodPrice, prodEnabledIsActive,
> prodSaleIsActive, prodSalePrice FROM dbo.sfProducts
>
> WHERE prodID = '" + Replace(RSstorefront__MMColParam, "'", "''") + "'"
> RSstorefront.CursorType = 0
> RSstorefront.CursorLocation = 2
> RSstorefront.LockType = 1
> RSstorefront.Open()
> RSstorefront_numRows = 0
>
> Dim RScontents2__MMColParam
> RScontents2__MMColParam = ProdID
>
> Dim RScontents2
> Dim RScontents2_numRows
> Set RScontents2 = Server.CreateObject("ADODB.Recordset")
> RScontents2.ActiveConnection = MM_Products_STRING
> RScontents2.Source = "SELECT * FROM dbo.Prod_Box_Contents WHERE ProdID =
> '" + Replace(RScontents2__MMColParam, "'", "''") +
>
> "' ORDER BY Contents ASC"
> RScontents2.CursorType = 0
> RScontents2.CursorLocation = 2
> RScontents2.LockType = 1
> RScontents2.Open()
> RScontents2_numRows = 0
>
> Dim RSspecifications2__MMColParam
> RSspecifications2__MMColParam = ProdID
>
> Dim RSspecifications2
> Dim RSspecifications2_numRows
> Set RSspecifications2 = Server.CreateObject("ADODB.Recordset")
> RSspecifications2.ActiveConnection = MM_Products_STRING
> RSspecifications2.Source = "SELECT * FROM dbo.Prod_Specs WHERE ProdID =
> '" + Replace(RSspecifications2__MMColParam, "'",
>
> "''") + "' ORDER BY Spec_Header ASC"
> RSspecifications2.CursorType = 0
> RSspecifications2.CursorLocation = 2
> RSspecifications2.LockType = 1
> RSspecifications2.Open()
> RSspecifications2_numRows = 0
>
> Dim RSrequirements2__MMColParam
> RSrequirements2__MMColParam = ProdID
>
> Dim RSrequirements2
> Dim RSrequirements2_numRows
> Set RSrequirements2 = Server.CreateObject("ADODB.Recordset")
> RSrequirements2.ActiveConnection = MM_Products_STRING
> RSrequirements2.Source = "SELECT * FROM dbo.Prod_SystemReq WHERE ProdID
> = '" + Replace(RSrequirements2__MMColParam, "'",
>
> "''") + "'"
> RSrequirements2.CursorType = 0
> RSrequirements2.CursorLocation = 2
> RSrequirements2.LockType = 1
> RSrequirements2.Open()
> RSrequirements2_numRows = 0
> %>
>
> It's a mess, I know. That's why I'm here. Any insight is greatly
> appreciated.
> Justin
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!
Aaron Bertrand - MVP Guest
-
Justin Gregory #6 Re: Multiple Recordsets
I was just hoping someone would suggest a solid method for dealing with
many disparate tables and inconsistent data. In the past I have used
joins, but for this they don't work.
I use Stored Procedures for all my Inserts. I even have Henderson's
books on SPs and TSQL. Unfortunately, I've been thrown into a production
environment where time constraints have forced me to cut some corners;
Like actually reading those books. With that said, any suggestions for
how to bring all this data together using SPs?
I know it has not been done right. That's why I'm trying to fix it. I've
seen posts from other people with the same question remain unanswered,
so I know I'm not the only one. I plan to cut out the BS Macromedia code
as well.
Thanks for the suggestions so far,
Justin
*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!
Justin Gregory Guest
-
Ray at #7 Re: Multiple Recordsets
That's just too much for a person to process...
Ray at home
"Justin Gregory" <justinomoralez@yahoo.com> wrote in message
news:e5ibn17NEHA.556@tk2msftngp13.phx.gbl...> I was just hoping someone would suggest a solid method for dealing with
> many disparate tables and inconsistent data. In the past I have used
> joins, but for this they don't work.
>
> I use Stored Procedures for all my Inserts. I even have Henderson's
> books on SPs and TSQL. Unfortunately, I've been thrown into a production
> environment where time constraints have forced me to cut some corners;
> Like actually reading those books. With that said, any suggestions for
> how to bring all this data together using SPs?
>
> I know it has not been done right. That's why I'm trying to fix it. I've
> seen posts from other people with the same question remain unanswered,
> so I know I'm not the only one. I plan to cut out the BS Macromedia code
> as well.
> Thanks for the suggestions so far,
> Justin
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!
Ray at Guest
-
Aaron Bertrand - MVP #8 Re: Multiple Recordsets
I couldn't create your tables. When I tried running your script as a whole,
Query Analyzer returns:
Server: Msg 1767, Level 16, State 1, Line 2
Foreign key 'FK_Prod_Drivers_Tbl_Chipset_Tbl' references invalid table
'dbo.Chipset_Tbl'.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
Server: Msg 1767, Level 16, State 1, Line 2
Foreign key 'FK_Prod_Tbl_Brand_Tbl' references invalid table
'dbo.Brand_Tbl'.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
Aside from that, so many things to say...
(1) don't use + for string concatenation. It has a nasty habit of trying to
double as addition. Use & always.
(2) why don't you replace the ' with '' at the very beginning, instead of
running the same function against the same value every time you use it?
(3) why do you open all of these recordsets sequentially but not use any of
them? Can you give us some idea how you are planning to display the data?
Are you going to mix the results from all the recordsets, or display 18
different sections?
(4) never use SELECT * in production code ([url]http://www.aspfaq.com/2096[/url]).
(5) try to avoid ADODB.Recordset unless necessary
([url]http://www.aspfaq.com/2191[/url]).
(6) use aliases for table names, so you don't have to repeat dbo.tablename.
for every reference in a query.
(7) many of your queries ask for ProdID (either implicitly using *, or
explicitly) when the where clause already limits it to ONLY rows that have
the ProdID value you've already supplied. It is inefficient, of course, to
bring back data you already have. I removed it from all but the first query
(there is a chance you want to see the actual case as it is stored in the
database, as opposed to the case entered by the user... even if that is the
case, you only need to do that once).
(8) why on earth are you using a CHAR(2000) (e.g. Addendum_Data)? See
[url]http://www.aspfaq.com/2354[/url]
(9) your schema is quite confusing. You seem to be creating key tables to
only hold keys? Many tables seem to represent one-to-one relationships, why
do they need to be in separate tables? Is your choice of varchar(size)
appropriate (many seem quite large)? What is the difference between
prod_sysReq and prod_systemReq?
(10) use a stored procedure instead of putting all this SQL into your web
app. Here is my first stab, but it could probably be optimised depending on
how many joins you could make instead of individual queries (e.g. how many
of these queries return exactly one row? and do you really need ProdID,
FeatureID, Feature, and Feature_Details from the Prod_Features table?).
CREATE PROCEDURE dbo.giveMeData
@prodID VARCHAR(25)
AS
BEGIN
SET NOCOUNT ON
-- Query #1
SELECT ProdID, ProdName, CategoryID, short_description
FROM dbo.Prod_Tbl
WHERE ProdID = @prodID
-- Query #2
SELECT FeatureID, Feature, Feature_Details
FROM dbo.Prod_Features
WHERE ProdID = @prodID
-- Query #3
-- is this join really necessary? Isn't all the information you need in
Prod_SysReq only?
SELECT s.SysReq
FROM dbo.Prod_SysReq s
INNER JOIN dbo.Prod_SysReq_Key sk
ON s.SysReqID = sk.SysReqID
WHERE dbo.Prod_SysReq.ProdID = @prodID
-- Anytime you have an on and a where, consider changing where to and
-- to compare query plans. Sometimes the optimizer takes different
plans.
-- Query #4
SELECT pk.Contents
FROM dbo.Prod_Package_Contents p
INNER JOIN dbo.Prod_Package_contents_Key pk
ON p.ContentsID = pk.ContentsID
WHERE p.ProdID = @prodID
-- Query #5
SELECT ps.SpecificationID, ps.Spec_Data, pk.Specification
FROM dbo.Prod_Specification_Key pk
INNER JOIN dbo.Prod_Specs_Tbl ps
ON pk.SpecificationID = ps.SpecificationID
WHERE ProdID = @prodID
-- these three queries below seem absolutely useless to me.
-- you already know the productID! Why query two new
-- tables to get information you already know? I think #6-8
-- can be eliminated.
-- Query #6
SELECT ProdID
FROM dbo.Prod_Drivers_Tbl
WHERE ProdID = @prodID
-- Query #7
SELECT ProdID
FROM dbo.Prod_Manuals_Tbl
WHERE ProdID = @prodID
-- Query #8
SELECT ProdID
FROM dbo.Prod_Reviews_Tbl
WHERE ProdID = @prodID
-- Query #9
SELECT Image_Path
FROM dbo.Prod_HighRes_Tbl
WHERE ProdID = @prodID
-- Query #10
SELECT Image_Path
FROM dbo.Prod_Boxshots_Tbl
WHERE ProdID = @prodID
-- Query #11
SELECT Prod_Summary
FROM dbo.Prod_Summary
WHERE ProdID = @prodID
-- Query #12
SELECT AddendumID, Addendum_Title, Addendum_Data
FROM dbo.Prod_Addendum_Tbl
WHERE ProdID = @prodID
-- Query #13
SELECT Prod_Image_Path
FROM dbo.Prod_Image_Tbl
WHERE ProdID = @prodID
-- Query #14
SELECT o.Op_SysID, os.Op_System
FROM dbo.Prod_OpSys_Tbl o
INNER JOIN dbo.Op_System_Tbl os
ON o.Op_SysID = os.Op_SysID
WHERE o.ProdID = @prodID
-- Query #15
SELECT prodPrice, prodEnabledIsActive, prodSaleIsActive, prodSalePrice
FROM dbo.sfProducts
WHERE prodID = @prodID
-- Query #16
SELECT Contents
FROM dbo.Prod_Box_Contents
WHERE ProdID = @prodID
ORDER BY Contents ASC
-- Query #17
SELECT Spec_Header, Spec_Data
FROM dbo.Prod_Specs
WHERE ProdID = @prodID
ORDER BY Spec_Header ASC"
-- Query #18
SELECT SysReq
FROM dbo.Prod_SystemReq
WHERE ProdID = @prodID
END
GO
Then from ASP,
<%
ProdID = Replace(Trim(Request.Form("ProdID")), "'", "''")
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute("EXEC dbo.giveMeData '" & ProdID & "'")
do while not rs.eof
' deal with query #1
rs.movenext
loop
set rs = rs.nextrecordset()
' deal with query #2
set rs = rs.nextrecordset()
' deal with query #3
...
%>
I just changed ~870 lines of code to < 150, improved the performance a bit,
and gave you a lot to think about...
--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]
"Justin Gregory" <justinomoralez@yahoo.com> wrote in message
news:#FB3We3NEHA.540@TK2MSFTNGP11.phx.gbl...> Oops, I'm new. ok, here are the tables and queries, there are some
> redundant tables in here that I had to include for legacy data,
> eventually they will be phased out:
>
> CREATE TABLE [dbo].[Category_Tbl] (
> [CategoryID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [Prod_Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [BrandID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Op_System_Tbl] (
> [Op_SysID] [tinyint] IDENTITY (1, 1) NOT NULL ,
> [Op_System] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Addendum_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [AddendumID] [int] IDENTITY (1, 1) NOT NULL ,
> [Addendum_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Addendum_Data] [char] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [PRdate] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Box_Contents] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Contents] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContentID] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Boxshots_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Drivers_Tbl] (
> [DriverID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Driver_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ChipsetID] [smallint] NULL ,
> [Driver_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [File_Size] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Op_SysID] [tinyint] NULL ,
> [PRdate] [datetime] NULL ,
> [PRcounter] [numeric](10, 0) NULL ,
> [FileType] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Chipset] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Features] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FeatureID] [int] IDENTITY (1, 1) NOT NULL ,
> [Feature] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Feature_Details] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_HighRes_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Image_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Prod_Image_Path] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Manuals_Tbl] (
> [ManualID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Manual_Title] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Manual_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [File_Size] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ChipsetID] [smallint] NULL ,
> [PRdate] [datetime] NULL ,
> [PRcounter] [numeric](10, 0) NULL ,
> [File_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Chipset2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_OpSys_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Op_SysID] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Package_Contents] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ContentsID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Package_contents_Key] (
> [ContentsID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [Contents] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CategoryID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Reviews_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReviewID] [int] IDENTITY (1, 1) NOT NULL ,
> [Review_Title] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Review_Data] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Review_Link] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [PRdate] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Specification_Key] (
> [SpecificationID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [Specification] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [CategoryID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Specs] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Spec_Header] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Spec_Data] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Specs_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SpecificationID] [smallint] NULL ,
> [Spec_Data] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Summary] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Prod_Summary] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_SysReq] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SysReqID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_SysReq_Key] (
> [SysReqID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [SysReq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CategoryID] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_SystemReq] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SysReq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProdName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BrandID] [smallint] NULL ,
> [CategoryID] [smallint] NULL ,
> [Date_Entered] [datetime] NULL ,
> [short_description] [varchar] (4000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [showprod] [bit] NULL ,
> [show_on_frontpage] [bit] NULL ,
> [legacy] [bit] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_thumbnail_Tbl] (
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Image_Path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Prod_thumbnails] (
> [boxshotID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProdID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boxshot_path] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Date_Added] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[sfProducts] (
> [prodID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [prodCategoryId] [int] NULL ,
> [prodManufacturerId] [int] NULL ,
> [prodVendorId] [int] NULL ,
> [prodName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodNamePlural] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [prodShortDescription] [nvarchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodImageSmallPath] [nvarchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodImageLargePath] [nvarchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodLink] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodPrice] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodWeight] [float] NULL ,
> [prodShip] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [prodShipIsActive] [smallint] NULL ,
> [prodCountryTaxIsActive] [smallint] NULL ,
> [prodStateTaxIsActive] [smallint] NULL ,
> [prodEnabledIsActive] [smallint] NULL ,
> [prodAttrNum] [int] NULL ,
> [prodSaleIsActive] [smallint] NULL ,
> [prodSalePrice] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [prodDateAdded] [datetime] NULL ,
> [prodDateModified] [datetime] NULL ,
> [prodLength] [float] NULL ,
> [prodWidth] [float] NULL ,
> [prodHeight] [float] NULL ,
> [prodFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [upsize_ts] [binary] (8) NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Category_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Category_Tbl] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Op_System_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Op_System_Tbl] PRIMARY KEY CLUSTERED
> (
> [Op_SysID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Box_Contents] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Box_Contents] PRIMARY KEY CLUSTERED
> (
> [ContentID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Drivers_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Drivers_Tbl] PRIMARY KEY CLUSTERED
> (
> [DriverID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Features] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Features] PRIMARY KEY CLUSTERED
> (
> [FeatureID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Manuals_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Manuals_Tbl] PRIMARY KEY CLUSTERED
> (
> [ManualID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Package_contents_Key] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Package_contents] PRIMARY KEY CLUSTERED
> (
> [ContentsID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Specification_Key] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Specification_Key] PRIMARY KEY CLUSTERED
> (
> [SpecificationID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Summary] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Summary] PRIMARY KEY CLUSTERED
> (
> [ProdID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_SysReq_Key] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_SysReq] PRIMARY KEY CLUSTERED
> (
> [SysReqID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Tbl] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_Tbl] PRIMARY KEY CLUSTERED
> (
> [ProdID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_thumbnails] WITH NOCHECK ADD
> CONSTRAINT [PK_Prod_thumbnails] PRIMARY KEY CLUSTERED
> (
> [ProdID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Prod_Addendum_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Addendum_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate]
> GO
>
> ALTER TABLE [dbo].[Prod_Drivers_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Drivers_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate],
> CONSTRAINT [DF_Prod_Drivers_Tbl_PRcounter] DEFAULT (0) FOR [PRcounter]
> GO
>
> ALTER TABLE [dbo].[Prod_Manuals_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Manuals_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate],
> CONSTRAINT [DF_Prod_Manuals_Tbl_PRcounter] DEFAULT (0) FOR [PRcounter]
> GO
>
> ALTER TABLE [dbo].[Prod_Reviews_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Reviews_Tbl_PRdate] DEFAULT (getdate()) FOR
> [PRdate]
> GO
>
> ALTER TABLE [dbo].[Prod_Tbl] WITH NOCHECK ADD
> CONSTRAINT [DF_Prod_Tbl_DateEntered] DEFAULT (getdate()) FOR
> [Date_Entered],
> CONSTRAINT [DF_Prod_Tbl_showprod] DEFAULT (0) FOR [showprod],
> CONSTRAINT [DF_Prod_Tbl_show_on_frontpage] DEFAULT (0) FOR
> [show_on_frontpage],
> CONSTRAINT [DF_Prod_Tbl_legacy] DEFAULT (0) FOR [legacy]
> GO
>
> ALTER TABLE [dbo].[Prod_thumbnails] WITH NOCHECK ADD
> CONSTRAINT [DF_Index_Page_Products_Date_Added] DEFAULT (getdate()) FOR
> [Date_Added]
> GO
>
> ALTER TABLE [dbo].[Prod_Addendum_Tbl] ADD
> CONSTRAINT [FK_Prod_Addendum_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Box_Contents] ADD
> CONSTRAINT [FK_Prod_Box_Contents_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Boxshots_Tbl] ADD
> CONSTRAINT [FK_Prod_Boxshots_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Drivers_Tbl] ADD
> CONSTRAINT [FK_Prod_Drivers_Tbl_Chipset_Tbl] FOREIGN KEY
> (
> [ChipsetID]
> ) REFERENCES [dbo].[Chipset_Tbl] (
> [ChipsetID]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Prod_Drivers_Tbl_Op_System_Tbl] FOREIGN KEY
> (
> [Op_SysID]
> ) REFERENCES [dbo].[Op_System_Tbl] (
> [Op_SysID]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Prod_Drivers_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Features] ADD
> CONSTRAINT [FK_Prod_Features_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_HighRes_Tbl] ADD
> CONSTRAINT [FK_Prod_HighRes_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Image_Tbl] ADD
> CONSTRAINT [FK_Prod_Image_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Manuals_Tbl] ADD
> CONSTRAINT [FK_Prod_Manuals_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_OpSys_Tbl] ADD
> CONSTRAINT [FK_Prod_OpSys_Tbl_Op_System_Tbl] FOREIGN KEY
> (
> [Op_SysID]
> ) REFERENCES [dbo].[Op_System_Tbl] (
> [Op_SysID]
> ),
> CONSTRAINT [FK_Prod_OpSys_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Package_Contents] ADD
> CONSTRAINT [FK_Prod_Package_Contents_Prod_Package_contents_Key] FOREIGN
> KEY
> (
> [ContentsID]
> ) REFERENCES [dbo].[Prod_Package_contents_Key] (
> [ContentsID]
> ),
> CONSTRAINT [FK_Prod_Package_Contents_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Package_contents_Key] ADD
> CONSTRAINT [FK_Prod_Package_contents_Key_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Reviews_Tbl] ADD
> CONSTRAINT [FK_Prod_Reviews_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Specification_Key] ADD
> CONSTRAINT [FK_Prod_Specification_Key_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Specs_Tbl] ADD
> CONSTRAINT [FK_Prod_Specs_Tbl_Prod_Specification_Key] FOREIGN KEY
> (
> [SpecificationID]
> ) REFERENCES [dbo].[Prod_Specification_Key] (
> [SpecificationID]
> ),
> CONSTRAINT [FK_Prod_Specs_Tbl_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_Summary] ADD
> CONSTRAINT [FK_Prod_Summary_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_SysReq] ADD
> CONSTRAINT [FK_Prod_SysReq_Prod_SysReq_Key] FOREIGN KEY
> (
> [SysReqID]
> ) REFERENCES [dbo].[Prod_SysReq_Key] (
> [SysReqID]
> ),
> CONSTRAINT [FK_Prod_SysReq_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_SysReq_Key] ADD
> CONSTRAINT [FK_Prod_SysReq_Key_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> )
> GO
>
> ALTER TABLE [dbo].[Prod_Tbl] ADD
> CONSTRAINT [FK_Prod_Tbl_Brand_Tbl] FOREIGN KEY
> (
> [BrandID]
> ) REFERENCES [dbo].[Brand_Tbl] (
> [BrandID]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Prod_Tbl_Category_Tbl] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[Category_Tbl] (
> [CategoryID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
> ALTER TABLE [dbo].[Prod_thumbnails] ADD
> CONSTRAINT [FK_Prod_thumbnails_Prod_Tbl] FOREIGN KEY
> (
> [ProdID]
> ) REFERENCES [dbo].[Prod_Tbl] (
> [ProdID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
> -------------------------------------------------------
> The Queries (Macromedia code that I have tweaked a bit):
>
> <%
> Dim ProdID
> ProdID = Trim(Request.Form("ProdID"))
>
> Dim RSproducts__MMColParam
> RSproducts__MMColParam = ProdID
>
> set RSproducts = Server.CreateObject("ADODB.Recordset")
> RSproducts.ActiveConnection = MM_Products_STRING
> RSproducts.Source = "SELECT ProdID, ProdName, CategoryID,
> short_description FROM dbo.Prod_Tbl WHERE ProdID = '" +
>
> Replace(RSproducts__MMColParam, "'", "''") + "'"
> RSproducts.CursorType = 0
> RSproducts.CursorLocation = 2
> RSproducts.LockType = 3
> RSproducts.Open()
> RSproducts_numRows = 0
>
> Dim RSfeatures__MMColParam
> RSfeatures__MMColParam = ProdID
>
> set RSfeatures = Server.CreateObject("ADODB.Recordset")
> RSfeatures.ActiveConnection = MM_Products_STRING
> RSfeatures.Source = "SELECT * FROM dbo.Prod_Features WHERE ProdID = '" +
> Replace(RSfeatures__MMColParam, "'", "''") + "'
>
> ORDER BY Feature ASC"
> RSfeatures.CursorType = 0
> RSfeatures.CursorLocation = 2
> RSfeatures.LockType = 3
> RSfeatures.Open()
> RSfeatures_numRows = 0
>
> Dim RSrequirements__MMColParam
> RSrequirements__MMColParam = ProdID
>
> set RSrequirements = Server.CreateObject("ADODB.Recordset")
> RSrequirements.ActiveConnection = MM_Products_STRING
> RSrequirements.Source = "SELECT dbo.Prod_SysReq.ProdID,
> dbo.Prod_SysReq_Key.SysReq FROM dbo.Prod_SysReq INNER JOIN
>
> dbo.Prod_SysReq_Key ON dbo.Prod_SysReq.SysReqID =
> dbo.Prod_SysReq_Key.SysReqID WHERE dbo.Prod_SysReq.ProdID
>
> = '" + Replace(RSrequirements__MMColParam, "'", "''") + "'"
> RSrequirements.CursorType = 0
> RSrequirements.CursorLocation = 2
> RSrequirements.LockType = 3
> RSrequirements.Open()
> RSrequirements_numRows = 0
>
> Dim RScontents__MMColParam
> RScontents__MMColParam = ProdID
>
> set RScontents = Server.CreateObject("ADODB.Recordset")
> RScontents.ActiveConnection = MM_Products_STRING
> RScontents.Source = "SELECT dbo.Prod_Package_Contents.ProdID,
> dbo.Prod_Package_contents_Key.Contents FROM
>
> dbo.Prod_Package_Contents INNER JOIN
> dbo.Prod_Package_contents_Key ON
>
> dbo.Prod_Package_Contents.ContentsID =
> dbo.Prod_Package_contents_Key.ContentsID WHERE
> dbo.Prod_Package_Contents.ProdID =
>
> '" + Replace(RScontents__MMColParam, "'", "''") + "'"
> RScontents.CursorType = 0
> RScontents.CursorLocation = 2
> RScontents.LockType = 3
> RScontents.Open()
> RScontents_numRows = 0
>
> Dim RSspecifications__MMColParam
> RSspecifications__MMColParam = ProdID
>
> set RSspecifications = Server.CreateObject("ADODB.Recordset")
> RSspecifications.ActiveConnection = MM_Products_STRING
> RSspecifications.Source = "SELECT dbo.Prod_Specs_Tbl.SpecificationID,
> dbo.Prod_Specs_Tbl.Spec_Data,
>
> dbo.Prod_Specification_Key.Specification FROM
> dbo.Prod_Specification_Key INNER JOIN
>
> dbo.Prod_Specs_Tbl ON dbo.Prod_Specification_Key.SpecificationID =
> dbo.Prod_Specs_Tbl.SpecificationID WHERE ProdID = '" +
>
> Replace(RSspecifications__MMColParam, "'", "''") + "'"
> RSspecifications.CursorType = 0
> RSspecifications.CursorLocation = 2
> RSspecifications.LockType = 3
> RSspecifications.Open()
> RSspecifications_numRows = 0
>
> Dim RSdrivers__MMColParam
> RSdrivers__MMColParam = ProdID
>
> set RSdrivers = Server.CreateObject("ADODB.Recordset")
> RSdrivers.ActiveConnection = MM_Products_STRING
> RSdrivers.Source = "SELECT ProdID FROM dbo.Prod_Drivers_Tbl WHERE
> ProdID = '" + Replace(RSdrivers__MMColParam, "'", "''")
>
> + "'"
> RSdrivers.CursorType = 0
> RSdrivers.CursorLocation = 2
> RSdrivers.LockType = 3
> RSdrivers.Open()
> RSdrivers_numRows = 0
>
> Dim RSmanuals__MMColParam
> RSmanuals__MMColParam = ProdID
>
> set RSmanuals = Server.CreateObject("ADODB.Recordset")
> RSmanuals.ActiveConnection = MM_Products_STRING
> RSmanuals.Source = "SELECT ProdID FROM dbo.Prod_Manuals_Tbl WHERE
> ProdID = '" + Replace(RSmanuals__MMColParam, "'", "''")
>
> + "'"
> RSmanuals.CursorType = 0
> RSmanuals.CursorLocation = 2
> RSmanuals.LockType = 3
> RSmanuals.Open()
> RSmanuals_numRows = 0
>
> Dim RSreviews__MMColParam
> RSreviews__MMColParam = ProdID
>
> set RSreviews = Server.CreateObject("ADODB.Recordset")
> RSreviews.ActiveConnection = MM_Products_STRING
> RSreviews.Source = "SELECT ProdID FROM dbo.Prod_Reviews_Tbl WHERE
> ProdID = '" + Replace(RSreviews__MMColParam, "'", "''")
>
> + "'"
> RSreviews.CursorType = 0
> RSreviews.CursorLocation = 2
> RSreviews.LockType = 3
> RSreviews.Open()
> RSreviews_numRows = 0
>
> Dim RShighres__MMColParam
> RShighres__MMColParam = ProdID
>
> set RShighres = Server.CreateObject("ADODB.Recordset")
> RShighres.ActiveConnection = MM_Products_STRING
> RShighres.Source = "SELECT ProdID, Image_Path FROM dbo.Prod_HighRes_Tbl
> WHERE ProdID = '" +
>
> Replace(RShighres__MMColParam, "'", "''") + "'"
> RShighres.CursorType = 0
> RShighres.CursorLocation = 2
> RShighres.LockType = 3
> RShighres.Open()
> RShighres_numRows = 0
>
> Dim RSboxshot__MMColParam
> RSboxshot__MMColParam = ProdID
>
> set RSboxshot = Server.CreateObject("ADODB.Recordset")
> RSboxshot.ActiveConnection = MM_Products_STRING
> RSboxshot.Source = "SELECT ProdID, Image_Path FROM
> dbo.Prod_Boxshots_Tbl WHERE ProdID = '" +
>
> Replace(RSboxshot__MMColParam, "'", "''") + "'"
> RSboxshot.CursorType = 0
> RSboxshot.CursorLocation = 2
> RSboxshot.LockType = 3
> RSboxshot.Open()
> RSboxshot_numRows = 0
>
> Dim RSsummary__MMColParam
> RSsummary__MMColParam = ProdID
>
> set RSsummary = Server.CreateObject("ADODB.Recordset")
> RSsummary.ActiveConnection = MM_Products_STRING
> RSsummary.Source = "SELECT * FROM dbo.Prod_Summary WHERE ProdID = '" +
> Replace(RSsummary__MMColParam, "'", "''") + "'"
> RSsummary.CursorType = 0
> RSsummary.CursorLocation = 2
> RSsummary.LockType = 3
> RSsummary.Open()
> RSsummary_numRows = 0
>
> Dim RSaddendum__MMColParam
> RSaddendum__MMColParam = ProdID
>
> set RSaddendum = Server.CreateObject("ADODB.Recordset")
> RSaddendum.ActiveConnection = MM_Products_STRING
> RSaddendum.Source = "SELECT * FROM dbo.Prod_Addendum_Tbl WHERE ProdID
> = '" + Replace(RSaddendum__MMColParam, "'", "''") +
>
> "'"
> RSaddendum.CursorType = 0
> RSaddendum.CursorLocation = 2
> RSaddendum.LockType = 3
> RSaddendum.Open()
> RSaddendum_numRows = 0
>
> Dim RSprod_image__MMColParam
> RSprod_image__MMColParam = ProdID
>
> set RSprod_image = Server.CreateObject("ADODB.Recordset")
> RSprod_image.ActiveConnection = MM_Products_STRING
> RSprod_image.Source = "SELECT * FROM dbo.Prod_Image_Tbl WHERE ProdID =
> '" + Replace(RSprod_image__MMColParam, "'", "''")
>
> + "'"
> RSprod_image.CursorType = 0
> RSprod_image.CursorLocation = 2
> RSprod_image.LockType = 3
> RSprod_image.Open()
> RSprod_image_numRows = 0
>
> Dim RSopsys__MMColParam
> RSopsys__MMColParam = ProdID
>
> set RSopsys = Server.CreateObject("ADODB.Recordset")
> RSopsys.ActiveConnection = MM_Products_STRING
> RSopsys.Source = "SELECT dbo.Prod_OpSys_Tbl.ProdID,
> dbo.Prod_OpSys_Tbl.Op_SysID, dbo.Op_System_Tbl.Op_System FROM
>
> dbo.Prod_OpSys_Tbl INNER JOIN dbo.Op_System_Tbl
> ON dbo.Prod_OpSys_Tbl.Op_SysID =
>
> dbo.Op_System_Tbl.Op_SysID WHERE dbo.Prod_OpSys_Tbl.ProdID = '" +
> Replace(RSopsys__MMColParam, "'", "''") + "'"
> RSopsys.CursorType = 0
> RSopsys.CursorLocation = 2
> RSopsys.LockType = 3
> RSopsys.Open()
> RSopsys_numRows = 0
>
> Dim RSstorefront__MMColParam
> RSstorefront__MMColParam = ProdID
>
> Dim RSstorefront
> Dim RSstorefront_numRows
> Set RSstorefront = Server.CreateObject("ADODB.Recordset")
> RSstorefront.ActiveConnection = MM_SF_STRING
> RSstorefront.Source = "SELECT prodID, prodPrice, prodEnabledIsActive,
> prodSaleIsActive, prodSalePrice FROM dbo.sfProducts
>
> WHERE prodID = '" + Replace(RSstorefront__MMColParam, "'", "''") + "'"
> RSstorefront.CursorType = 0
> RSstorefront.CursorLocation = 2
> RSstorefront.LockType = 1
> RSstorefront.Open()
> RSstorefront_numRows = 0
>
> Dim RScontents2__MMColParam
> RScontents2__MMColParam = ProdID
>
> Dim RScontents2
> Dim RScontents2_numRows
> Set RScontents2 = Server.CreateObject("ADODB.Recordset")
> RScontents2.ActiveConnection = MM_Products_STRING
> RScontents2.Source = "SELECT * FROM dbo.Prod_Box_Contents WHERE ProdID =
> '" + Replace(RScontents2__MMColParam, "'", "''") +
>
> "' ORDER BY Contents ASC"
> RScontents2.CursorType = 0
> RScontents2.CursorLocation = 2
> RScontents2.LockType = 1
> RScontents2.Open()
> RScontents2_numRows = 0
>
> Dim RSspecifications2__MMColParam
> RSspecifications2__MMColParam = ProdID
>
> Dim RSspecifications2
> Dim RSspecifications2_numRows
> Set RSspecifications2 = Server.CreateObject("ADODB.Recordset")
> RSspecifications2.ActiveConnection = MM_Products_STRING
> RSspecifications2.Source = "SELECT * FROM dbo.Prod_Specs WHERE ProdID =
> '" + Replace(RSspecifications2__MMColParam, "'",
>
> "''") + "' ORDER BY Spec_Header ASC"
> RSspecifications2.CursorType = 0
> RSspecifications2.CursorLocation = 2
> RSspecifications2.LockType = 1
> RSspecifications2.Open()
> RSspecifications2_numRows = 0
>
> Dim RSrequirements2__MMColParam
> RSrequirements2__MMColParam = ProdID
>
> Dim RSrequirements2
> Dim RSrequirements2_numRows
> Set RSrequirements2 = Server.CreateObject("ADODB.Recordset")
> RSrequirements2.ActiveConnection = MM_Products_STRING
> RSrequirements2.Source = "SELECT * FROM dbo.Prod_SystemReq WHERE ProdID
> = '" + Replace(RSrequirements2__MMColParam, "'",
>
> "''") + "'"
> RSrequirements2.CursorType = 0
> RSrequirements2.CursorLocation = 2
> RSrequirements2.LockType = 1
> RSrequirements2.Open()
> RSrequirements2_numRows = 0
> %>
>
> It's a mess, I know. That's why I'm here. Any insight is greatly
> appreciated.
> Justin
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!
Aaron Bertrand - MVP Guest
-
cstarr #9 Multiple Recordsets
I'm having problems diplaying recordsets. I'm using MS Access and I'm
connected to a remote server.
This is what I did:
I went to the "Applications" panel. Then to the "Databases" tab. Then went
to "+ Custom Connection String."
I named my connection and entered: "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\STORAGE_K5\K5\(myserver.com)\newland_tou rs.mdb;" in the Connection
String box. I have "Using driver on testing server."
I proceded to the "Bindings" tab and created 1 recordset and displayed it. It
worked fine. But when I created another recordset and displayed it, it
wouldn't work. It keeps taking me to an error page.
Any ideas on what I'm doing wrong?
cstarr Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
