Query errors Square Brackets

[Yardyy]

Hi, I have query that i have setup in ms access and it works fine, pulls out the data that we require.

SELECT VEDEPTS.NAME, VESTOCK.NAME, VEBARCODES.BARCODE, VESUPPSTOCK.CATNO, VEBRANCHSTOCK.QUANTITY, VESUPPS.NAME, [RETAIL_NET]+[RETAIL_VAT] AS RRP, VESTOCK.QTY_ONORDER
FROM (((VEDEPTS INNER JOIN (VEBARCODES INNER JOIN VESTOCK ON VEBARCODES.STOCKID = VESTOCK.STOCKID) ON VEDEPTS.DEPTID = VESTOCK.DEPTID) INNER JOIN VEBRANCHSTOCK ON VESTOCK.STOCKID = VEBRANCHSTOCK.STOCKID) INNER JOIN VESUPPSTOCK ON VESTOCK.STOCKID = VESUPPSTOCK.STOCKID) INNER JOIN VESUPPS ON VESUPPSTOCK.SUPPID = VESUPPS.SUPPID
WHERE (((VEDEPTS.NAME) Like "001*" Or (VEDEPTS.NAME) Like "005*" Or (VEDEPTS.NAME) Like "006*" Or (VEDEPTS.NAME) Like "007*" Or (VEDEPTS.NAME) Like "008*" Or (VEDEPTS.NAME) Like "012*" Or (VEDEPTS.NAME) Like "024*" Or (VEDEPTS.NAME) Like "025*" Or (VEDEPTS.NAME) Like "018*" Or (VEDEPTS.NAME) Like "019*") AND ((VEBRANCHSTOCK.QUANTITY)<0) AND ((VEBRANCHSTOCK.BRANCHID)=1))
ORDER BY VEDEPTS.NAME;


when i try use the same bit of code from access in an asp i get the following error message:
Server error: Unable to retrieve schema information from the query:

SELECT VEDEPTS.NAME, VESTOCK.NAME, VEBARCODES.BARCODE, VESUPPSTOCK.CATNO, VEBRANCHSTOCK.QUANTITY, VESUPPS.NAME, [RETAIL_NET]+[RETAIL_VAT] AS RRP, VESTOCK.QTY_ONORDER FROM (((VEDEPTS INNER JOIN (VEBARCODES INNER JOIN VESTOCK ON VEBARCODES.STOCKID = VESTOCK.STOCKID) ON VEDEPTS.DEPTID = VESTOCK.DEPTID) INNER JOIN VEBRANCHSTOCK ON VESTOCK.STOCKID

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 113
[

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


It seems that square bracket is the isse "[" but am not sure what or how to get the results without it. Again it works fine in access with the square brakcets.

What i am doing to get round this now is to generate snp reports and the users are then printing those of from a network share. i would idealy like to publish to asp page and then let users generate up to date data when required.

Any help most appreciated.

Many Thanks
Yurov Ardyy

 

[ggriffit]

the square brackets are not valid SQL characters for field names, see :

http://techblog.julianhidalgo.com/2...nd-square-brackets-in-sql-server-identifiers/

http://www.bearriverwebdesign.com/blog/index.php?m=05&y=09&entry=entry090528-093109

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[Yardyy]

ggriffit, thanks for the links, but am lost, what should i be looking at.. should i be changing the [ to another character.. not sure..

Many Thanks
Yurov Ardyy

 

[ggriffit]

yes, the square brackets has a special meaning in SQL, so I would avoid using them, see

http://msdn.microsoft.com/en-us/library/aa224033(SQL.80).aspx

and

http://msdn.microsoft.com/en-us/library/ms177563.aspx

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[BigRed1212]

Square brackets are useful for reserved words and the like in Access.

Your "trial" query is against an Access database. Is the ASP page also querying an Access database? If so I would just try leaving the brackets off. I would think RETAIL_NET + RETAIL_VAT AS RRP would work just fine.

 

[Yardyy]

Hi, Sorry fro the long delay. The oroginal query is done from an access query that is linked to a firebird database through odbc. The ASP page will also be querying the same Firebird database. I have just tried the query without the square brackets with no joy, the error message that i get i . Too Few Parameters expected 8.

Many Thanks
Yurov Ardyy

 

[wvdba]

Some things behave differently in access and in asp. for extample, the LIKE statement in access will work this way:

SELECT * FROM my_db WHERE some_field LIKE "*string*";

asterisk here acts as a wild card (fuzzy search).
but, in asp you have to code:

SELECT * FROM my_db WHERE some_field LIKE "%string%";

the percent sing here acts as wild card (fuzzy search).