Hi,
I'm not that up on SQL as a whole as I've concentrated mainly on
frontend and most of the DB backend work I've done has been
with "Access" as that's all the site needed. I have however worked
with SQL Server 2000 a little and now I'm trying to migrate to
MySQL. I understand that views are not supported in MySQL as yet and
was curious to know if there is a workaround this using VBScript?
My VBScript coding is a bit of a mish-mash of Ultradev and hand
coding. I prefer to hand code now but learned mostly from pulling
apart Ultradev code in the beginning. Now, that's the background
sorted so you know what kind of level I'm working at.
I had a SQL Server DB set up with a view like this:
CREATE VIEW dbo.pageQ
AS
SELECT DDCat.ProdID, DDCat.Title, DDCat.Artist, DDCat.Image,
DDCat.Tracks, DDCat.Cass_Price, DDCat.CD_Price,
DDCat.Vid_Price, DDCat.Book_Price, DDCat.Cass_CatNo,
DDCat.CD_CatNo, DDCat.Vid_CatNo, DDCat.Book_CatNo,
DDCat.Comments, DDCat.Weight, DDCat.Category,
Categories.CatName, Categories.CatBlurb,
Categories.CatLabel, Categories.CatHeading,
DDCat.Cass_Price / 0.787564 AS Cass_EPrice,
DDCat.CD_Price / 0.787564 AS CD_EPrice,
DDCat.Vid_Price / 0.787564 AS Vid_EPrice,
DDCat.Book_Price / 0.787564 AS Book_EPrice
FROM Categories INNER JOIN
DDCat ON Categories.CatID = DDCat.Category
I was trying to use that SQL string in a connection to access the
inner join info to no avail - like this:
<%
Dim rsPage__MMColParam
rsPage__MMColParam = "11"
if (Request.QueryString("Category") <> "") then rsPage__MMColParam =
Request.QueryString("Category")
%>
<%
set rsPage = Server.CreateObject("ADODB.Recordset")
rsPage.ActiveConnection = MM_ddcat_STRING
sqlString = "SELECT DDCat.ProdID, DDCat.Title, DDCat.Artist,
DDCat.Image, "
sqlString = sqlString & "DDCat.Tracks, DDCat.Cass_Price,
DDCat.CD_Price, "
sqlString = sqlString & "DDCat.Vid_Price, DDCat.Book_Price,
DDCat.Cass_CatNo, "
sqlString = sqlString & "DDCat.CD_CatNo, DDCat.Vid_CatNo,
DDCat.Book_CatNo, "
sqlString = sqlString & "DDCat.Comments, DDCat.Weight,
DDCat.Category, "
sqlString = sqlString & "Categories.CatName, Categories.CatBlurb, "
sqlString = sqlString & "Categories.CatLabel,
Categories.CatHeading, "
sqlString = sqlString & "DDCat.Cass_Price / 0.787564 AS
Cass_EPrice, "
sqlString = sqlString & "DDCat.CD_Price / 0.787564 AS CD_EPrice, "
sqlString = sqlString & "DDCat.Vid_Price / 0.787564 AS Vid_EPrice, "
sqlString = sqlString & "DDCat.Book_Price / 0.787564 AS Book_EPrice"
sqlString = sqlString & "FROM Categories INNER JOIN "
sqlString = sqlString & "DDCat ON Categories.CatID = DDCat.Category"
rsPage.Source = sqlString & " WHERE DDCat.Category = " + Replace
(rsPage__MMColParam, "'", "''") + ""
rsPage.CursorType = 0
rsPage.CursorLocation = 2
rsPage.LockType = 3
rsPage.Open()
rsPage_numRows = 0
%>
I'm getting a SQL syntax error which is:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.16]You have an error in your
SQL syntax. Check the manual that corresponds to your MySQL server
version for the right syntax to use near 'Categories INNER JOIN
DDCat ON Categories.CatID = DDCat.Categor
/dd/prodii.asp, line 67
Can anyone help me? Is it clear what I'm trying to achieve? Any help
on this would be greatly appreciated as I'm pretty much stuck as to
what to do.
Many thanks in advance,
Lloyd.
