|
| |
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
 |
access sql to mysql - 3/3/2008 12:11:52
How do I convert the WHERE clause from access to mysql?
SELECT tblDOG.ID AS ID, tblDOG.name,
FROM (((tblGEN RIGHT JOIN (tblColor RIGHT JOIN tblDOG ON tblColor.ColorID = tblDOG.ColorID) ON tblGEN.GenID = tblDOG.genID)
WHERE (((tblDOG.ID)=[tbldogID])); <--- This is the access version. I need the same statement in mysql
Thanks --- this forum has been very helpful to me with this project of mine.
|
|
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
|
RE: access sql to mysql - 3/4/2008 1:18:59
Spooky, thanks for answering....I've made a little progress since I posted...I do get a syntax error with the above. It doesn't like the brackets or quotes.
My goal is to make a page detail page with each dog's infomation & have hyperlinks to other dogs (via pedigree)
Basically if you are viewing the page details.asp?ID=1 (for dog indexed to 1) and you changed the number 1 in the url address to lets say 5 it will bring up the info to the dog indexed to 5, and so on (details.asp?ID=5)
I have created the list of dogs that will hyperlink to the details.asp page.
LIST.ASP page
Dim strSQL
strSQL = "SELECT name, ID AS cdogid FROM tblDOG"
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, myConn
Response.Write "name: "
Do While not objRS.EOF
Response.Write ("<tr><td><a href=""detail.asp?id="&objRS("cdogid")&""">"&objRS("name")&"</a></td></tr> <BR>")
objRS.MoveNext
Loop
objRS.Close
the URL will show detail.asp?ID=1 (or whatever the index number is for that particular dog)
But when the details.asp?ID= page loads, I get either a sql syntax error or it give's me a eof error (sql works, but no records returned)
My details.asp page is
Dim SQL
SQL = "SELECT tblDOG.NAME AS tbldogname, tblDOG.ID AS cdogid, WHERE tblDOG.ID='request(cdogid)'
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open SQL, myConn
Response.Write "name: "
Do While not objRS.EOF
Response.Write objRS("tbldogname")
objRS.Close
Set objRS = Nothing
At the moment I tried using a request code, but not sure if I'm missing a DIM statement. I've used 'cdogid', ( '&"cdogid"&'), etc
Currently the above SQL statement gives me the folllowing error (I don't have anything for objrs.eof, but if I did it would just give me name:)
name:
ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/det.asp, line 0
Basically my question is how do I get the WHERE tblDOG.ID='request(cdogid)' from above to reconize the dog's index number in mysql? I've looked everywhere with no luck yet or the examples use msaccess
Thanks again. One day I will be able to use your spooky login progam with this database....I'm getting closer & learning a something new.
| |
|
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
|
RE: access sql to mysql - 3/4/2008 16:19:02
I used your method, but when I included the full query with the LEFT & RIGHT joins the
I received [TCX][MyODBC]Column 'ID' in where clause is ambiguous
Basically I think the ID can related to a few ID's in the query (because it's a pedigree based pedigree.) So ID can be the ID of dog or sire.ID or dam.ID in the joins.
The full query is below (I'm still working on it in CONCAT),
Dim strSQL
strSQL = "SELECT tblDOG.ID AS cdogid, tblDOG.NAME AS name, tblDOG.PreTi, tblDOG.SuffTi, tblDOG.callname, tblDOG.DOB, tblGEN.Gender, tblColor.Color, tblDOG.Owner, tblDOG.Breeder, tblDOG.DOD, tblDOG.website, tblDOG.Hips, tblDOG.Eyes, tblDOG.elbow, tblDOG.CNM, tblDOG.Honors, tblDOG.UKC, tblDOG.AKC, tblDOG.sireID, tblDOG.damID, CONCAT(tbldog.PreTi, ' ', tbldog.NAME, ' ', tbldog.suffti) AS Fullname, CONCAT(sire.PreTi, ' ', sire.NAME, ' ', sire.suffti, ' (', scolor.color, ')') AS sirefull, CONCAT(dam.PreTi, ' ', dam.NAME, ' ', dam.suffti, ' (', dcolor.color, ')') AS damfull, CONCAT(ssire.PreTi, ' ', ssire.NAME, ' ', ssire.suffti, '(', SScolor.color, ')') AS Ssfull, CONCAT(Sdam.PreTi, ' ', Sdam.NAME, ' ', Sdam.suffti, '(', SDcolor.color, ')') AS Sdfull
FROM ((((((((((((((((((((((((((((tblGEN RIGHT JOIN (tblColor RIGHT JOIN tblDOG ON tblColor.ColorID = tblDOG.ColorID) ON tblGEN.GenID = tblDOG.genID) LEFT JOIN tblDOG AS sire ON tblDOG.sireID = sire.ID) LEFT JOIN tblDOG AS dam ON tblDOG.damID = dam.ID) LEFT JOIN tblDOG AS Ssire ON sire.sireID = Ssire.ID) LEFT JOIN tblDOG AS Sdam ON sire.damID = Sdam.ID) LEFT JOIN tblDOG AS Dsire ON dam.sireID = Dsire.ID) LEFT JOIN tblDOG AS Ddam ON dam.damID = Ddam.ID) LEFT JOIN tblDOG AS SSsire ON Ssire.sireID = SSsire.ID) LEFT JOIN tblDOG AS SSdam ON Ssire.damID = SSdam.ID) LEFT JOIN tblDOG AS SDsire ON Sdam.sireID = SDsire.ID) LEFT JOIN tblDOG AS SDdam ON Sdam.damID = SDdam.ID) LEFT JOIN tblDOG AS DSsire ON Dsire.sireID = DSsire.ID) LEFT JOIN tblDOG AS DSdam ON Dsire.damID = DSdam.ID) LEFT JOIN tblDOG AS DDsire ON Ddam.sireID = DDsire.ID) LEFT JOIN tblDOG AS DDdam ON Ddam.damID = DDdam.ID) LEFT JOIN tblColor AS Scolor ON sire.ColorID = Scolor.ColorID) LEFT JOIN tblColor AS Dcolor ON dam.ColorID = Dcolor.ColorID) LEFT JOIN tblColor AS SScolor ON Ssire.ColorID = SScolor.ColorID) LEFT JOIN tblColor AS SDcolor ON Sdam.ColorID = SDcolor.ColorID) LEFT JOIN tblColor AS DScolor ON Dsire.ColorID = DScolor.ColorID) LEFT JOIN tblColor AS DDcolor ON Ddam.ColorID = DDcolor.ColorID) LEFT JOIN tblColor AS SSScolor ON SSsire.ColorID = SSScolor.ColorID) LEFT JOIN tblColor AS SSDcolor ON SSdam.ColorID = SSDcolor.ColorID) LEFT JOIN tblColor AS SDScolor ON SDsire.ColorID = SDScolor.ColorID) LEFT JOIN tblColor AS SDDcolor ON SDdam.ColorID = SDDcolor.ColorID) LEFT JOIN tblColor AS DSScolor ON DSsire.ColorID = DSScolor.ColorID) LEFT JOIN tblColor AS DSDcolor ON DSdam.ColorID = DSDcolor.ColorID) LEFT JOIN tblColor AS DDScolor ON DDsire.ColorID = DDScolor.ColorID) LEFT JOIN tblColor AS DDDcolor ON DDdam.ColorID = DDDcolor.ColorID
WHERE ID="& clng(Request.querystring("ID"))
I put cdogID in the request.querysting...no luck...
| |
|
|
|
crosscreek
Posts: 107
Joined: 2/5/2008
Status: offline
|
RE: access sql to mysql - 3/4/2008 20:06:57
quote:
ORIGINAL: Spooky
Try this first ?
SQL = "SELECT tblDOG.NAME AS tbldogname, tblDOG.ID AS cdogid, WHERE tblDOG.ID="& clng(Request.querystring("ID"))
quote:
WHERE tblDOG.ID="& clng(Request.querystring("ID"))
Spooky got it to work...didn't realize that you had tbldog.ID=
Thanks for your help & Forum....
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
....I just forgot the " again after the = sign... 
