access sql to mysql (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

crosscreek -> 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.





Spooky -> RE: access sql to mysql (3/4/2008 0:25:43)

Do you get an error when using that SQL? The error message would guide us to the error.
Should tbldogID be a variable?



crosscreek -> 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.







Spooky -> RE: access sql to mysql (3/4/2008 2:00:32)

Try this first ?

SQL = "SELECT tblDOG.NAME AS tbldogname, tblDOG.ID AS cdogid, WHERE tblDOG.ID="& clng(Request.querystring("ID"))



crosscreek -> RE: access sql to mysql (3/4/2008 2:26:04)

Spooky...I tried the above.

I found the answer.... (I made the sql very simple to figure out the problem) The " needed to be before the &cdogid.

Dim cdogid
cdogid = Trim( Request("id") )

Dim strSQL
strSQL = "SELECT Name, id FROM tblDOG where id = " & cdogid

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open strSQL, myConn

Response.Write "name: "
Response.Write objRS("name")

Picky code....[:'(]

Now I can go to bed and not think!!!!




crosscreek -> RE: access sql to mysql (3/4/2008 2:31:56)

Your way worked also[:D]....I just forgot the " again after the = sign...

It's late.



Spooky -> RE: access sql to mysql (3/4/2008 2:49:15)

[;)]

Also - try and do it my way with the cLng so that only a number can be inputted



crosscreek -> 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 -> 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....



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
9.423828E-02