return single value in asp/sql

0 views
Skip to first unread message

WC Justice

unread,
Aug 12, 2004, 12:25:09 AM8/12/04
to
I'm assuming this can be done, but I can't seem to get it to work... I'd
like to easily return a single value from a sql statement, something like:

"intNewItems = conn.execute "SELECT COUNT (ItemID) WHERE ItemDate = Date()",
where conn is the connection object, etc. What am I getting wrong?


User Settings

unread,
Aug 12, 2004, 12:37:35 AM8/12/04
to
You didn't say what is going wrong. Are you getting no results, 10 results,
wrong results

"WC Justice" <WCJ...@bellsouth.net> wrote in message
news:10hlsa1...@corp.supernews.com...

WC Justice

unread,
Aug 12, 2004, 1:03:06 AM8/12/04
to
Sorry about that. I've copied the actual code and error below

<%

SET conn = server.createobject ("adodb.connection")
conn.open "DSN=" & Session("DSN")

intBrokerCount = conn.execute "SELECT COUNT (BrokerID) FROM tblBrokers"

response.write intBrokerCount & " Brokers"

%>

****************************************************************************

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/sqltest.asp, line 19

intBrokerCount = conn.execute "SELECT COUNT (BrokerID) FROM tblBrokers"
------------------------------^


"User Settings" <Racr...@entelifanio.net> wrote in message
news:qZmdnX11Ham...@comcast.com...

Evertjan.

unread,
Aug 12, 2004, 5:18:25 AM8/12/04
to
WC Justice wrote on 12 aug 2004 in microsoft.public.inetserver.asp.general:
> intBrokerCount = conn.execute "SELECT COUNT (BrokerID) FROM tblBrokers"

I would clear the space between COUNT and (BrokerID)

> response.write intBrokerCount & " Brokers"

I would write:

response.write intBrokerCount(0) & " Brokers"

Will this help?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

WC Justice

unread,
Aug 12, 2004, 9:45:13 AM8/12/04
to
This worked:

intBrokerCount = conn.execute("SELECT COUNT (BrokerID) FROM tblBrokers")

response.write intBrokerCount(0) & " Brokers"

Thanks for your help

"Evertjan." <exjxw.ha...@interxnl.net> wrote in message
news:Xns95437311...@194.109.133.29...

Bob Barrows [MVP]

unread,
Aug 12, 2004, 10:16:05 AM8/12/04
to
This goes back to the point I'm always trying to hammer home in these
newsgroups:
Do not try to create and test queries from ASP. Always create and test them
using the query execution tool provided by your database (Query Analyzer for
SQL Server). Then, if you must use dynamic sql (not recommended), you can
simply copy/paste a working sql statement from QA into your vbscript code
and modify it as needed.

Bob Barrows

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


Dan Nash

unread,
Aug 12, 2004, 1:33:02 PM8/12/04
to
Bob,

Just interested.. why are dynamic queries not recommended? Performance
issues, or something more sinister?

Cheers


Dan

Bob Barrows [MVP]

unread,
Aug 12, 2004, 3:31:58 PM8/12/04
to
Both.

Performance suffers due to:
- vbscript's deficiencies at string concatenation
- the extra processing caused by the need to deal with dealing with
delimiter characters (e.g., using Replace to escape literal quotes)
This also makes dynamic sql harder to use
- the lack of strong data typing
- extra network traffic due to sending long query strings across the wire
- the lack of encapsulation due to the failure to use stored procedures -
E.G., you need to create a parent record and a child record. Using
dynamic sql, you have to run the query to create the parent record,
retrieve the new ID, then run another query to insert the child record:
2-3 trips to the database. With a stored procedure, only a single trip
to the database is needed
- the need for SQL Server to compile query plans on demand
- there's more but I'm running out of time

The sinister aspect is the fact that SQL Injection is only possible when
dynamic sql is used. Here is some information about sql injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

Bob Barrows

Evertjan.

unread,
Aug 12, 2004, 3:39:43 PM8/12/04
to
Bob Barrows [MVP] wrote on 12 aug 2004 in
microsoft.public.inetserver.asp.general:

> - vbscript's deficiencies at string concatenation

And jscript?

Bob Barrows [MVP]

unread,
Aug 12, 2004, 4:09:05 PM8/12/04
to
Evertjan. wrote:
> Bob Barrows [MVP] wrote on 12 aug 2004 in
> microsoft.public.inetserver.asp.general:
>> - vbscript's deficiencies at string concatenation
>
> And jscript?
Does jscript have the same string-handling problems that vb/vba/vbscript
has?

Evertjan.

unread,
Aug 12, 2004, 4:17:09 PM8/12/04
to
Bob Barrows [MVP] wrote on 12 aug 2004 in
microsoft.public.inetserver.asp.general:

>> And jscript?


> Does jscript have the same string-handling problems that vb/vba/vbscript
> has?
>

That was the question.

Bob Barrows [MVP]

unread,
Aug 12, 2004, 4:23:15 PM8/12/04
to
Evertjan. wrote:
> Bob Barrows [MVP] wrote on 12 aug 2004 in
> microsoft.public.inetserver.asp.general:
>
>>> And jscript?
>> Does jscript have the same string-handling problems that
>> vb/vba/vbscript has?
>>
>
> That was the question.
Guess we'll have to wait for Dave Anderson to chime in to get the answer to
that one ...

Bob Barrows

Chris Hohmann

unread,
Aug 17, 2004, 8:35:22 PM8/17/04
to
"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:eWLc6oK...@TK2MSFTNGP11.phx.gbl...

> Evertjan. wrote:
> > Bob Barrows [MVP] wrote on 12 aug 2004 in
> > microsoft.public.inetserver.asp.general:
> >
> >>> And jscript?
> >> Does jscript have the same string-handling problems that
> >> vb/vba/vbscript has?
> >>
> >
> > That was the question.
> Guess we'll have to wait for Dave Anderson to chime in to get the answer
to
> that one ...

I'm not Dave, but JScript does suffer from the same string handling problems
as VBScript. I believe it related to the way buffer allocation is handled.
Worse yet, the Response object in JScript is late bound which is a whole
other can of worms. Here's a thread:

http://groups.google.com/groups?threadm=%23LWyF4qLDHA.2244%40TK2MSFTNGP11.phx.gbl


Evertjan.

unread,
Aug 18, 2004, 4:29:41 AM8/18/04
to
Chris Hohmann wrote on 18 aug 2004 in
microsoft.public.inetserver.asp.general:
> I'm not Dave, but JScript does suffer from the same string handling
> problems as VBScript. I believe it related to the way buffer
> allocation is handled. Worse yet, the Response object in JScript is
> late bound which is a whole other can of worms. Here's a thread:
>
> http://groups.google.com/groups?threadm=%23LWyF4qLDHA.2244%40TK2MSFTNGP
> 11.phx.gbl

Though I have no proof, I could imagine that both in ASP and in IE the
kernel of the jscript and vbscript engines would be one and the same
"physical" compiled code, only the interpreting/parsing interface being
[partly?] different.

If so,

z += "..more"

and

z = z & "..more"

would use the same code and roughly be equally fast. True?

=============

Probably more to the point
[in the sense of processing efficiency]
are questions like this:

Would join operations be significantly quicker
than string concatenation? Say:

z = ["blah","blah","blah","blah","blip"].join("-")

or

z = "blah"+"-"+"blah"+"-"+"blah"+"-"+"blah"+"-"+"blip"

[or equivalent vbscript]

Chris Hohmann

unread,
Aug 18, 2004, 12:53:27 PM8/18/04
to
"Evertjan." <exjxw.ha...@interxnl.net> wrote in message
news:Xns95496AC7...@194.109.133.29...

> Chris Hohmann wrote on 18 aug 2004 in
> microsoft.public.inetserver.asp.general:
> > I'm not Dave, but JScript does suffer from the same string handling
> > problems as VBScript. I believe it related to the way buffer
> > allocation is handled. Worse yet, the Response object in JScript is
> > late bound which is a whole other can of worms. Here's a thread:
> >
> > http://groups.google.com/groups?threadm=%23LWyF4qLDHA.2244%40TK2MSFTNGP
> > 11.phx.gbl
>
> Though I have no proof, I could imagine that both in ASP and in IE the
> kernel of the jscript and vbscript engines would be one and the same
> "physical" compiled code, only the interpreting/parsing interface being
> [partly?] different.
>
> If so,
>
> z += "..more"
>
> and
>
> z = z & "..more"
>
> would use the same code and roughly be equally fast. True?

True.


> =============
>
> Probably more to the point
> [in the sense of processing efficiency]
> are questions like this:
>
> Would join operations be significantly quicker
> than string concatenation? Say:
>
> z = ["blah","blah","blah","blah","blip"].join("-")
>
> or
>
> z = "blah"+"-"+"blah"+"-"+"blah"+"-"+"blah"+"-"+"blip"
>
> [or equivalent vbscript]

Yes, the join operation is faster than string concatenation. You could also
create a custom class/component. I think string concatenation would qualify
as "highly reentrant" which is usually the yardstick to measure the benefit
of using a component vs. straight ASP. Here's a thread:
http://groups.google.com/groups?threadm=3e3d01c0ec78%2477101ff0%249ae62ecf%40tkmsftngxa02


Reply all
Reply to author
Forward
0 new messages