Home
>
WinDev Forum
>
Topic
WD12 - mssql query error
Posted by Arie
|
Arie
WD12 - mssql query error February 21, 2009 05:04PM |
Hi,
I used the query editor to design a query, with one base-table and 2 detail-tables. Both detail-tables are outer joined, to include base-records without detailrecords.
When executing this query it gives me an error.
OLE DB access error.
Error Number = 170129
Native Error Number = 102
Description = Incorrect syntax near '='.
Why can't I desing such a query with WD, just using the designer?
I'm using MSSQL via OLEDB.
It looks like WD does not make the proper translation to the mssql syntax. (because the error is in the oledb-layer I'm not able to see the actual query-text).
I tried using hQueryWithoutCorrection, but then I get another other. In this case the query as-is (see below) will be send to mssql-engine, which indeed is wrong then.
(Error: Tbl_Base AND Tbl_Base in the FROM clause have the same exposed names. Use correlation names to distinguish them.)
I expect WD, being database independed, to do the proper translation/conversion for me!
Looking at the query-code in WD:
SELECT b_field1, b_field2, d1_field, d2_field FROM
Tbl_Detail1 RIGHT OUTER JOIN Tbl_Base ON Tbl_Detail1ID.MyID = Tbl_Base.Detail1ID,
Tbl_Detail2 RIGHT OUTER JOIN Tbl_Base ON Tbl_Detail2ID.MyID = Tbl_Base.Detail2ID,
WHERE Tbl_Base.BaseID = {ParamaBaseID}
Arie
I used the query editor to design a query, with one base-table and 2 detail-tables. Both detail-tables are outer joined, to include base-records without detailrecords.
When executing this query it gives me an error.
OLE DB access error.
Error Number = 170129
Native Error Number = 102
Description = Incorrect syntax near '='.
Why can't I desing such a query with WD, just using the designer?
I'm using MSSQL via OLEDB.
It looks like WD does not make the proper translation to the mssql syntax. (because the error is in the oledb-layer I'm not able to see the actual query-text).
I tried using hQueryWithoutCorrection, but then I get another other. In this case the query as-is (see below) will be send to mssql-engine, which indeed is wrong then.
(Error: Tbl_Base AND Tbl_Base in the FROM clause have the same exposed names. Use correlation names to distinguish them.)
I expect WD, being database independed, to do the proper translation/conversion for me!
Looking at the query-code in WD:
SELECT b_field1, b_field2, d1_field, d2_field FROM
Tbl_Detail1 RIGHT OUTER JOIN Tbl_Base ON Tbl_Detail1ID.MyID = Tbl_Base.Detail1ID,
Tbl_Detail2 RIGHT OUTER JOIN Tbl_Base ON Tbl_Detail2ID.MyID = Tbl_Base.Detail2ID,
WHERE Tbl_Base.BaseID = {ParamaBaseID}
Arie
|
Arie
Re: WD12 - mssql query error February 21, 2009 05:14PM |
BTW.
It's working if I :
1. change the query-code by hand (thus skipping the visual editor) to match the mssql syntax
2. use HExecuteQuery(MyQuery,gctMyConnection,hQueryWithoutCorrection,nID)
But as I said: I don't want to do this extra work, especially on simple one's. I'm using WD to develop on full speed. And this way it's not database independend anymore. Right?
Arie
It's working if I :
1. change the query-code by hand (thus skipping the visual editor) to match the mssql syntax
2. use HExecuteQuery(MyQuery,gctMyConnection,hQueryWithoutCorrection,nID)
But as I said: I don't want to do this extra work, especially on simple one's. I'm using WD to develop on full speed. And this way it's not database independend anymore. Right?
Arie
|
fireto
Re: WD12 - mssql query error February 21, 2009 05:54PM |
hello
you have in your query the table Tbl_Base is set 2 time with the same name
you must have an alias for one of them or do you query like this :
SELECT b_field1, b_field2, d1_field, d2_field
FROM Tbl_Base
LEFT OUTER JOIN Tbl_Detail1 ON Tbl_Detail1ID.MyID = Tbl_Base.Detail1ID
LEFT OUTER JOIN Tbl_Detail2 ON Tbl_Detail2ID.MyID = Tbl_Base.Detail2ID,
WHERE Tbl_Base.BaseID = {ParamaBaseID}
regards
you have in your query the table Tbl_Base is set 2 time with the same name
you must have an alias for one of them or do you query like this :
SELECT b_field1, b_field2, d1_field, d2_field
FROM Tbl_Base
LEFT OUTER JOIN Tbl_Detail1 ON Tbl_Detail1ID.MyID = Tbl_Base.Detail1ID
LEFT OUTER JOIN Tbl_Detail2 ON Tbl_Detail2ID.MyID = Tbl_Base.Detail2ID,
WHERE Tbl_Base.BaseID = {ParamaBaseID}
regards
|
FireTox
Re: WD12 - mssql query error February 21, 2009 06:02PM |
hello
>But as I said: I don't want to do this extra work, especially on simple one's. I'm using WD to >develop on full speed. And this way it's not database independend anymore. Right?
true if you use H.. order
but with SQLsyntax you must have the right syntax for the SQL server. especially for complexe query. or use only SQL word which exist in all SQL Server (and it's not simple)
>But as I said: I don't want to do this extra work, especially on simple one's. I'm using WD to >develop on full speed. And this way it's not database independend anymore. Right?
true if you use H.. order
but with SQLsyntax you must have the right syntax for the SQL server. especially for complexe query. or use only SQL word which exist in all SQL Server (and it's not simple)