Too complicated query.....

  • Thread starter Thread starter Maciej Paras
  • Start date Start date
M

Maciej Paras

Hello everybody! I couldn't find any Microsoft newsgroup regardnig SQL queries in ASP pages, so I decided to turn to you guys...
I've got a database, which contains 8 tables, but I want to connect two of them ("categories" and "categoriesproducts") on particular records.
This is the ASP statement I've written so far:

<%

Dim mySQL 'SQL command to execute

Dim objPagingConn 'The ADODB connection object

Dim objPagingRS 'The ADODB recordset object

Set objPagingConn = Server.CreateObject("ADODB.Connection")

objPagingConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../fpdb/MyDatabase.mdb") & ";"

Set objPagingRS = Server.CreateObject("ADODB.Recordset")

mySQL="SELECT idCategory.categories, categoryDesc.categories, idParentCategory.categories, idProduct.categoriesproducts, idCategory.categoriesproducts FROM categories, categoriesproducts INNER JOIN categoriesproducts ON (idProduct.categories = idProduct.categoriesproducts) WHERE ((idParentCategory.categories=1) AND (idCategory.categories<>1)) ORDER BY categoryDesc.categories"

objPagingRS.Open mySQL, objPagingConn, 1,1

Do While Not objPagingRS.EOF

response.write objPagingRS("idCategory.categories") %>blah blah blah blah...<BR>

<%

objPagingRS.MoveNext

Loop

objPagingRS.Close

Set objPagingRS = Nothing

objPagingConn.Close

Set objPagingConn = Nothing

%>

The error message I receive in my browser tells me that there is a "syntax error in JOIN operation" (Microsoft JET Database Engine (0x80040E14)).

Operating system is Windows2000 Server, with Service Pack 4.0 installed on it, but without MS .Net Framework. The IIS on this machine has no restrictions regarding ASP functions.

Please, can anybody tell me where did I write wrong code? I guess this must be some error within the SQL statement, but I can't really find it :-( Or please give me some address to those who can help me.

Best regards, Maciej
 
I haven't worked with ASP, but your SQL statement appears to have the
references to tables and fields reversed.

The syntax is
[TableName].[FieldName]

Your statement appears to be
[FieldName].[TableName]

Reverse all these references and see if the problem goes away. If not, post
back.

--
Ken Snell
<MS ACCESS MVP>

Hello everybody! I couldn't find any Microsoft newsgroup regardnig SQL
queries in ASP pages, so I decided to turn to you guys...
I've got a database, which contains 8 tables, but I want to connect two of
them ("categories" and "categoriesproducts") on particular records.
This is the ASP statement I've written so far:

<%

Dim mySQL 'SQL command to execute

Dim objPagingConn 'The ADODB connection object

Dim objPagingRS 'The ADODB recordset object

Set objPagingConn = Server.CreateObject("ADODB.Connection")

objPagingConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("../fpdb/MyDatabase.mdb") & ";"

Set objPagingRS = Server.CreateObject("ADODB.Recordset")

mySQL="SELECT idCategory.categories, categoryDesc.categories,
idParentCategory.categories, idProduct.categoriesproducts,
idCategory.categoriesproducts FROM categories, categoriesproducts INNER JOIN
categoriesproducts ON (idProduct.categories = idProduct.categoriesproducts)
WHERE ((idParentCategory.categories=1) AND (idCategory.categories<>1)) ORDER
BY categoryDesc.categories"

objPagingRS.Open mySQL, objPagingConn, 1,1

Do While Not objPagingRS.EOF

response.write objPagingRS("idCategory.categories") %>blah blah blah
blah...<BR>

<%

objPagingRS.MoveNext

Loop

objPagingRS.Close

Set objPagingRS = Nothing

objPagingConn.Close

Set objPagingConn = Nothing

%>

The error message I receive in my browser tells me that there is a "syntax
error in JOIN operation" (Microsoft JET Database Engine (0x80040E14)).

Operating system is Windows2000 Server, with Service Pack 4.0 installed on
it, but without MS .Net Framework. The IIS on this machine has no
restrictions regarding ASP functions.

Please, can anybody tell me where did I write wrong code? I guess this must
be some error within the SQL statement, but I can't really find it :-( Or
please give me some address to those who can help me.

Best regards, Maciej
 
Hi Maciej,

Try to make the query work in MSAccess through the query designview, then switch to SQL and cut and past the statement in your ASP script.

Sid.
"Maciej Paras" <[email protected]> schreef in bericht Hello everybody! I couldn't find any Microsoft newsgroup regardnig SQL queries in ASP pages, so I decided to turn to you guys...
I've got a database, which contains 8 tables, but I want to connect two of them ("categories" and "categoriesproducts") on particular records.
This is the ASP statement I've written so far:

<%

Dim mySQL 'SQL command to execute

Dim objPagingConn 'The ADODB connection object

Dim objPagingRS 'The ADODB recordset object

Set objPagingConn = Server.CreateObject("ADODB.Connection")

objPagingConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../fpdb/MyDatabase.mdb") & ";"

Set objPagingRS = Server.CreateObject("ADODB.Recordset")

mySQL="SELECT idCategory.categories, categoryDesc.categories, idParentCategory.categories, idProduct.categoriesproducts, idCategory.categoriesproducts FROM categories, categoriesproducts INNER JOIN categoriesproducts ON (idProduct.categories = idProduct.categoriesproducts) WHERE ((idParentCategory.categories=1) AND (idCategory.categories<>1)) ORDER BY categoryDesc.categories"

objPagingRS.Open mySQL, objPagingConn, 1,1

Do While Not objPagingRS.EOF

response.write objPagingRS("idCategory.categories") %>blah blah blah blah...<BR>

<%

objPagingRS.MoveNext

Loop

objPagingRS.Close

Set objPagingRS = Nothing

objPagingConn.Close

Set objPagingConn = Nothing

%>

The error message I receive in my browser tells me that there is a "syntax error in JOIN operation" (Microsoft JET Database Engine (0x80040E14)).

Operating system is Windows2000 Server, with Service Pack 4.0 installed on it, but without MS .Net Framework. The IIS on this machine has no restrictions regarding ASP functions.

Please, can anybody tell me where did I write wrong code? I guess this must be some error within the SQL statement, but I can't really find it :-( Or please give me some address to those who can help me.

Best regards, Maciej
 
Back
Top