Query

[amatuer]

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???

 

[Mike Brind]

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???

http://www.aspfaq.com/show.asp?id=2400

You probably haven't delimited one or more of your datatypes correctly.
Response.write sql to see what it gives you.

But that's the least of your worries...

1. You have just posted the ip address, username, password and name of
your database to usenet. You may as well hand out invitations to all
and sundry to come on in. CHANGE YOUR PASSWORD RIGHT NOW! Then read
the link under the next point.

2. You are using the sa account on your database
http://groups.google.co.uk/group/mi...af1e9f8f?q=sa+account&rnum=6#0a24f5a8af1e9f8f

2. You appear to have performed no server-side validation on the form
inputs you are attempting to insert into your database which makes your
database ripe for sql injection attacks

3. You are using dynamic sql, which makes your database ripe for sql
injection attacks. Use stored procedures or parameters and the command
object. Google this group for stored procedures and look for posts
made by Bob Barrows - that's unless he doesn't suffer apoplexy and
responds to your post himself...

 

[Bob Barrows [MVP]]

amatuer wrote:

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???

<gasp><cough>><cough>><cough>
Whew!
Thanks, Mike. I think I'm recovered now.
Here are the links Mike mentioned:
My preference is to use stored procedures via the technique described
here:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&


However, you can avoid dynamic sql without using stored procedures via
the technique described here:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

 

[amatuer]

Thanx for all the advice,but not to worry.the Id & password i put in r
not the originals ones used on the server,the IP address as well is
some random nos i put in.

 

[Mike Brind]

So did you find the cause of the 80040e14 error

 

[Bob Barrows [MVP]]

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

Oops, I forgot to mention what a bad idea it is to use a connection
string rather than an explicit connection object in your recordset's
Open method.
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/06e6d6fc9b854b54?hl=en

 

[amatuer]

yes.
shouldv been: Select Sum(V_Transaksie.Aantal) As Total, Instead of:
Select Sum V_Transaksie.Aantal As Total