473,320 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Workaround for SQL Server Views in MySQL?

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.
Jul 19 '05 #1
0 1440

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: hera | last post by:
I am new at php and MySQL and have some (ugly, I'm sure) code to populate form select fields with data from a MySQL database, so a user can use those to sort/filter and view the 3,000 records. No...
2
by: Becoming Digital | last post by:
I've read through the archives and spent hours on Google but I still can't figure this out. I must extract the data from a SQL Server *.DB file. Viewing the raw text, I can see that there views,...
0
by: Lloyd Dobbler | last post by:
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...
2
by: TimMcConechy | last post by:
Hi, I am having a problem with a few "key words" on mysql. LEFT,DATABASE,FIELDS,LOAD,SEPARATOR ,SQL,KEY These are currently column names in our application tables. On other systems (oracle,sql...
9
by: Carter Smith | last post by:
http://www.icarusindie.com/wiki/index.php/Server-Side_Javascript_Check Sample source included This method requires that your pages are PHP enabled and you have mySQL. Although I suppose you...
3
by: raj | last post by:
whats the difference betwwen mysql and sql server2000 in terms of using storedprocedures,stored functions and views.
3
by: nark | last post by:
I just downloaded ODBC drivers from the mySQL web site but whenever I try to create a DSN I get a system 1157 error The driver does show up ok in the windows control panel I cant find any...
1
pbmods
by: pbmods | last post by:
Today I'd like to talk about a new feature introduced in MySQL 5: Views. Views are like snapshots of queries. You can save any query as a view, even complex queries with joins and subqueries,...
0
Coldfire
by: Coldfire | last post by:
Since i cannot show the differences in a two-column like table. I am first putting MS SQL Server 2005 and then MySQL 5.x. MS SQL Server 2005 Brief Overview - SQL Server is a full-fledged...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.