Help with sql

[colinod]

I have the following sql that get information from my database fine

DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketynew.mdb") & ";"
yaketyConnection.Open DatabaseDetails
yaketyQuery="SELECT * FROM celebs INNER JOIN mp3 ON celebs.idnumber = mp3.celebid where idnumber =" &actorid& " order by mp3.orderno"
Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)

i have added another field in the database to give the mp3 an accent type and called the field voicetypea,voicetypeb ect upto voicetyped these are in the mp3 table, i have tried to change my sql to get this information as follows

DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketynew.mdb") & ";"
yaketyConnection.Open DatabaseDetails
yaketyQuery="SELECT * FROM celebs INNER JOIN mp3 ON celebs.idnumber = mp3.celebid where idnumber =" &actorid& " and voicetypea = " &voiceid& " order by mp3.orderno"
Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)

and get the following error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'idnumber =1 and mp3voicetypea = All'.

/pages/girls2.asp, line 10

line 10 is

Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)

any help would be appreciated

[colinod]

Played about with the sql statement and sorted it my sql statement now reads

"SELECT * FROM celebs INNER JOIN mp3 ON celebs.idnumber = mp3.celebid where celebs.idnumber =" &actorid& " and mp3.voicetypea = '" &voiceid& "' order by mp3.orderno"

all to do with inverted commas round the right bits

all sorted

[jhardman]

Right, please note that we have a SQL forum, and since the question barely concerns ASP, I would have suggested you post in the SQL forum. but this is pretty standard, SQL uses inverted commas to show that you are talking about a string: voicetype = 'all'. 'Voicetype = all' looks like you have another field named 'all' and you only want to show records that have the same value in 'voicetype' and 'all'.

Jared

[CroCrew]

Hello colinod,

I would change it to:

yaketyQuery = "SELECT a.* FROM celebs a "
yaketyQuery &= "INNER JOIN mp3 b ON (a.idnumber = b.celebid) "
yaketyQuery &= "WHERE ((a.idnumber = '" & actorid & "') AND (b.voicetypea = '" &voiceid& "')) "
yaketyQuery &= "ORDER BY b.orderno"
 

Happy Coding,
CroCrew~

[jhardman]

showoff !