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.Aa ntal As Total, Sum
V_Transaksie.Pr ys As TCost, Sum V_Transaksie.Ur e As THrs,
V_Transaksie.Se ksie, V_LU_Aktiwiteit .Aktiwiteitsver slag FROM
V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Ak tiwiteit_ID =
V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Ak tiwiteit =
V_LU_Aktiwiteit .ID WHERE (V_LU_Aktiwitei t.Function1='Ex ternal') And
(V_Transaksie.A fdeling ='" & request.form("D ept") & "') AND
(V_Transaksie.I nvoice IS NOT NULL) AND (V_Transaksie.I nvoice = '1') AND
(V_Transaksie.M aand =" & request.form("M aand") & ") AND
(V_Transaksie.J aar =" & request.form("J aar") & ") Group By
V_Transaksie.Se ksie, V_LU_Aktiwiteit .Aktiwiteitsver slag"

line8:set rstMain = CreateObject("A DODB.Recordset" )
9: rstMain.Open sql, _
10: "Provider=SQLOL EDB.1;Persist Security Info=False;User
ID=sa;password= admin@sql;Initi al Catalog=GIS;Dat a
Source=172.16.4 .180",1,4

Any idea what might be cuasing the error???

[Mike Brind]

Re: Query


amatuer wrote:[color=blue]
> 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.Aa ntal As Total, Sum
> V_Transaksie.Pr ys As TCost, Sum V_Transaksie.Ur e As THrs,
> V_Transaksie.Se ksie, V_LU_Aktiwiteit .Aktiwiteitsver slag FROM
> V_Transaksie INNER JOIN V_Aktiwiteit ON V_Transaksie.Ak tiwiteit_ID =
> V_Aktiwiteit.ID INNER JOIN V_LU_Aktiwiteit ON V_Aktiwiteit.Ak tiwiteit =
> V_LU_Aktiwiteit .ID WHERE (V_LU_Aktiwitei t.Function1='Ex ternal') And
> (V_Transaksie.A fdeling ='" & request.form("D ept") & "') AND
> (V_Transaksie.I nvoice IS NOT NULL) AND (V_Transaksie.I nvoice = '1') AND
> (V_Transaksie.M aand =" & request.form("M aand") & ") AND
> (V_Transaksie.J aar =" & request.form("J aar") & ") Group By
> V_Transaksie.Se ksie, V_LU_Aktiwiteit .Aktiwiteitsver slag"
>
> line8:set rstMain = CreateObject("A DODB.Recordset" )
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOL EDB.1;Persist Security Info=False;User
> ID=sa;password= admin@sql;Initi al Catalog=GIS;Dat a
> Source=172.16.4 .180",1,4
>
> Any idea what might be cuasing the error???[/color]

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/microsoft.public.inetserver.asp.general/browse_frm/thread/d169779ea5821a2a/0a24f5a8af1e9f8f?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...

--
Mike Brind

[Bob Barrows [MVP]]

Re: Query

amatuer wrote:
<snip>[color=blue]
>
> line8:set rstMain = CreateObject("A DODB.Recordset" )
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOL EDB.1;Persist Security Info=False;User
> ID=sa;password= admin@sql;Initi al Catalog=GIS;Dat a
> Source=172.16.4 .180",1,4
>
> Any idea what might be cuasing the error???[/color]
<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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[amatuer]

Re: Query

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.



Bob Barrows [MVP] wrote:[color=blue]
> amatuer wrote:
> <snip>[color=green]
> >
> > line8:set rstMain = CreateObject("A DODB.Recordset" )
> > 9: rstMain.Open sql, _
> > 10: "Provider=SQLOL EDB.1;Persist Security Info=False;User
> > ID=sa;password= admin@sql;Initi al Catalog=GIS;Dat a
> > Source=172.16.4 .180",1,4
> >
> > Any idea what might be cuasing the error???[/color]
> <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/micro...9dc1701?hl=en&
>
>
> However, you can avoid dynamic sql without using stored procedures via
> the technique described here:
> http://groups-beta.google.com/group/...e36562fee7804e
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.[/color]

[Mike Brind]

Re: Query

So did you find the cause of the 80040e14 error :-)

--
Mike Brind


amatuer wrote:[color=blue]
> 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.
>
>
>
> Bob Barrows [MVP] wrote:[color=green]
> > amatuer wrote:
> > <snip>[color=darkred]
> > >
> > > line8:set rstMain = CreateObject("A DODB.Recordset" )
> > > 9: rstMain.Open sql, _
> > > 10: "Provider=SQLOL EDB.1;Persist Security Info=False;User
> > > ID=sa;password= admin@sql;Initi al Catalog=GIS;Dat a
> > > Source=172.16.4 .180",1,4
> > >
> > > Any idea what might be cuasing the error???[/color]
> > <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/micro...9dc1701?hl=en&
> >
> >
> > However, you can avoid dynamic sql without using stored procedures via
> > the technique described here:
> > http://groups-beta.google.com/group/...e36562fee7804e
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.[/color][/color]

[Bob Barrows [MVP]]

Re: Query

amatuer wrote:[color=blue]
>
> line8:set rstMain = CreateObject("A DODB.Recordset" )
> 9: rstMain.Open sql, _
> 10: "Provider=SQLOL EDB.1;Persist Security Info=False;User
> ID=sa;password= admin@sql;Initi al Catalog=GIS;Dat a
> Source=172.16.4 .180",1,4[/color]

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[amatuer]

Re: Query

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

Mike Brind wrote:[color=blue]
> So did you find the cause of the 80040e14 error :-)
>
> --
> Mike Brind
>
>
> amatuer wrote:[color=green]
> > 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.
> >
> >
> >
> > Bob Barrows [MVP] wrote:[color=darkred]
> > > amatuer wrote:
> > > <snip>
> > > >
> > > > line8:set rstMain = CreateObject("A DODB.Recordset" )
> > > > 9: rstMain.Open sql, _
> > > > 10: "Provider=SQLOL EDB.1;Persist Security Info=False;User
> > > > ID=sa;password= admin@sql;Initi al Catalog=GIS;Dat a
> > > > 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/micro...9dc1701?hl=en&
> > >
> > >
> > > However, you can avoid dynamic sql without using stored procedures via
> > > the technique described here:
> > > http://groups-beta.google.com/group/...e36562fee7804e
> > >
> > > --
> > > Microsoft MVP -- ASP/ASP.NET
> > > Please reply to the newsgroup. The email account listed in my From
> > > header is my spam trap, so I don't check it very often. You will get a
> > > quicker response by posting to the newsgroup.[/color][/color][/color]