Query

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '.'.
/verslag/MIncSum4.asp, line 9
This is the error i get when running a query.shown below:
line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID = V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit = V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
(V_Transaksie.Maand =" & request.form("Maand") & ") AND
(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
line8:set rstMain = CreateObject("ADODB.Recordset")
9: rstMain.Open sql, _
10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
Source=172.16.4.180",1,4
Any idea what might be cuasing the error?One thing you could try is : extract the sql statement , hard code in some
some values and try to execute in SQL Query Analyzer
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"amatuer" <njoosub@.gmail.com> wrote in message
news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Line 1: Incorrect syntax near '.'.
> /verslag/MIncSum4.asp, line 9
> This is the error i get when running a query.shown below:
> line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> line8:set rstMain = CreateObject("ADODB.Recordset")
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> Source=172.16.4.180",1,4
> Any idea what might be cuasing the error?
>|||I tried that,it gives the same error in sql server as well.
any other ideas?
Jack Vamvas wrote:
> One thing you could try is : extract the sql statement , hard code in some
> some values and try to execute in SQL Query Analyzer
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1149668899.685610.274150@.i39g2000cwa.googlegroups.com...
> > Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> > Line 1: Incorrect syntax near '.'.
> >
> > /verslag/MIncSum4.asp, line 9
> >
> > This is the error i get when running a query.shown below:
> >
> > line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> > V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> > V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> > V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => > V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => > V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> > (V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> > (V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> > (V_Transaksie.Maand =" & request.form("Maand") & ") AND
> > (V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> > V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> >
> > line8:set rstMain = CreateObject("ADODB.Recordset")
> > 9: rstMain.Open sql, _
> > 10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> > ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> > Source=172.16.4.180",1,4
> >
> > Any idea what might be cuasing the error?
> >|||On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
>Microsoft OLE DB Provider for SQL Server error '80040e14'
>Line 1: Incorrect syntax near '.'.
>/verslag/MIncSum4.asp, line 9
>This is the error i get when running a query.shown below:
>line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
>V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
>V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID =>V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit =>V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
>(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
>(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
>(V_Transaksie.Maand =" & request.form("Maand") & ") AND
>(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
>V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
>line8:set rstMain = CreateObject("ADODB.Recordset")
>9: rstMain.Open sql, _
>10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
>ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
>Source=172.16.4.180",1,4
>Any idea what might be cuasing the error?
Hi amatuer,
The SUM function needs parentheses. So for instance, change
> SELECT Sum V_Transaksie.Aantal As Total,
to
SELECT Sum(V_Transaksie.Aantal) As Total,
And so on for the other totals in the select list.
--
Hugo Kornelis, SQL Server MVP|||that helped. thanx Hugo.
Although my query still nt working the way i want it to...lol
Hugo Kornelis wrote:
> On 7 Jun 2006 01:28:19 -0700, amatuer wrote:
> >Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> >Line 1: Incorrect syntax near '.'.
> >
> >/verslag/MIncSum4.asp, line 9
> >
> >This is the error i get when running a query.shown below:
> >
> >line 5:sql="SELECT Sum V_Transaksie.Aantal As Total, Sum
> >V_Transaksie.Prys As TCost, Sum V_Transaksie.Ure As THrs,
> >V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag FROM
> >V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Aktiwiteit_ID => >V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Aktiwiteit => >V_LU_Aktiwiteit.ID WHERE (V_LU_Aktiwiteit.Function1='External') And
> >(V_Transaksie.Afdeling ='" & request.form("Dept") & "') AND
> >(V_Transaksie.Invoice IS NOT NULL) AND (V_Transaksie.Invoice = '1') AND
> >(V_Transaksie.Maand =" & request.form("Maand") & ") AND
> >(V_Transaksie.Jaar =" & request.form("Jaar") & ") Group By
> >V_Transaksie.Seksie, V_LU_Aktiwiteit.Aktiwiteitsverslag"
> >
> >line8:set rstMain = CreateObject("ADODB.Recordset")
> >9: rstMain.Open sql, _
> >10: "Provider=SQLOLEDB.1;Persist Security Info=False;User
> >ID=sa;password=admin@.sql;Initial Catalog=GIS;Data
> >Source=172.16.4.180",1,4
> >
> >Any idea what might be cuasing the error?
> Hi amatuer,
> The SUM function needs parentheses. So for instance, change
> > SELECT Sum V_Transaksie.Aantal As Total,
> to
> SELECT Sum(V_Transaksie.Aantal) As Total,
> And so on for the other totals in the select list.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
>that helped. thanx Hugo.
>Although my query still nt working the way i want it to...lol
Hi amatuer,
I'll be happy to help you further (as I'm sure several others are as
well). But to do that, we do need some more information:
- how do your tables look (please post CREATE TABLE statements,
including all constraints, properties, and indexes. You may omit
irrelevant columns);
- what does your data look like (please post INSERT statements with a
few well-chosen rows of sample data the nicely illustrate your problem);
- how should the results look like (please post expected results, for
example in tabular format - for readability, I suggest using a
fixed-size font and using spaces rather than tabs to layout the table).
Also see www.aspfaq.com/5006 for more tips on information to provide
when asking for help.
--
Hugo Kornelis, SQL Server MVP|||Hugo, i appreciate your willingness to help,but unfortunately i am nt
permitted to give out valuable info.so it seems i will hav to figure it
out by myself.
If i hav any othr questions i will let u know.
Thank you for ur time,it is highly appreciated
Hugo Kornelis wrote:
> On 7 Jun 2006 02:36:57 -0700, amatuer wrote:
> >that helped. thanx Hugo.
> >Although my query still nt working the way i want it to...lol
> Hi amatuer,
> I'll be happy to help you further (as I'm sure several others are as
> well). But to do that, we do need some more information:
> - how do your tables look (please post CREATE TABLE statements,
> including all constraints, properties, and indexes. You may omit
> irrelevant columns);
> - what does your data look like (please post INSERT statements with a
> few well-chosen rows of sample data the nicely illustrate your problem);
> - how should the results look like (please post expected results, for
> example in tabular format - for readability, I suggest using a
> fixed-size font and using spaces rather than tabs to layout the table).
> Also see www.aspfaq.com/5006 for more tips on information to provide
> when asking for help.
> --
> Hugo Kornelis, SQL Server MVP|||On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
>Hugo, i appreciate your willingness to help,but unfortunately i am nt
>permitted to give out valuable info.so it seems i will hav to figure it
>out by myself.
>If i hav any othr questions i will let u know.
>Thank you for ur time,it is highly appreciated
Hi amatuer,
That's a common requirement. One way to work aroound that is to change
the data (and possibly even the table and column names), but keep the
structure intact. That is a bit more work, of course, but it can be
worth it if you're really stuck.
On the other hand, you learn more by figuring it out for yourself :-))
--
Hugo Kornelis, SQL Server MVP|||thanx Hugo, but my prob sorted.took me a while bt i gt it to do wat i
wanted it to do.
Hugo Kornelis wrote:
> On 7 Jun 2006 04:42:01 -0700, amatuer wrote:
> >Hugo, i appreciate your willingness to help,but unfortunately i am nt
> >permitted to give out valuable info.so it seems i will hav to figure it
> >out by myself.
> >If i hav any othr questions i will let u know.
> >Thank you for ur time,it is highly appreciated
> Hi amatuer,
> That's a common requirement. One way to work aroound that is to change
> the data (and possibly even the table and column names), but keep the
> structure intact. That is a bit more work, of course, but it can be
> worth it if you're really stuck.
> On the other hand, you learn more by figuring it out for yourself :-))
> --
> Hugo Kornelis, SQL Server MVP