473,378 Members | 1,388 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

return single value in asp/sql

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?
Jul 19 '05 #1
14 8953
You didn't say what is going wrong. Are you getting no results, 10 results,
wrong results

"WC Justice" <WC*****@bellsouth.net> wrote in message
news:10*************@corp.supernews.com...
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?

Jul 19 '05 #2
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" <Ra*******@entelifanio.net> wrote in message
news:qZ********************@comcast.com...
You didn't say what is going wrong. Are you getting no results, 10 results, wrong results

"WC Justice" <WC*****@bellsouth.net> wrote in message
news:10*************@corp.supernews.com...
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?


Jul 19 '05 #3
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)
Jul 19 '05 #4
This worked:

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

response.write intBrokerCount(0) & " Brokers"

Thanks for your help

"Evertjan." <ex**************@interxnl.net> wrote in message
news:Xn********************@194.109.133.29...
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)

Jul 19 '05 #5
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

WC Justice wrote:
This worked:

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

response.write intBrokerCount(0) & " Brokers"

Thanks for your help

"Evertjan." <ex**************@interxnl.net> wrote in message
news:Xn********************@194.109.133.29...
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)


--
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.
Jul 19 '05 #6
Bob,

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

Cheers
Dan

"Bob Barrows [MVP]" wrote:
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

WC Justice wrote:
This worked:

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

response.write intBrokerCount(0) & " Brokers"

Thanks for your help

"Evertjan." <ex**************@interxnl.net> wrote in message
news:Xn********************@194.109.133.29...
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)


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

Jul 19 '05 #7
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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._injection.pdf
http://www.spidynamics.com/papers/SQ...WhitePaper.pdf

Bob Barrows

Dan Nash wrote:
Bob,

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

Cheers
Dan

"Bob Barrows [MVP]" wrote:
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

WC Justice wrote:
This worked:

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

response.write intBrokerCount(0) & " Brokers"

Thanks for your help

"Evertjan." <ex**************@interxnl.net> wrote in message
news:Xn********************@194.109.133.29...
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)


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


--
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.
Jul 19 '05 #8
Bob Barrows [MVP] wrote on 12 aug 2004 in
microsoft.public.inetserver.asp.general:
- vbscript's deficiencies at string concatenation


And jscript?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Jul 19 '05 #9
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?
--
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.
Jul 19 '05 #10
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.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Jul 19 '05 #11
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
--
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.
Jul 19 '05 #12
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eW*************@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?thre...TNGP11.phx.gbl
Jul 19 '05 #13
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?thre...4%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]
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Jul 19 '05 #14
"Evertjan." <ex**************@interxnl.net> wrote in message
news:Xn********************@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?thre...4%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?thre...40tkmsftngxa02
Jul 19 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Phil Powell | last post by:
My first time working with a PHP class, and after 6 hours of working out the kinks I am unable to return a value from the class, so now I appeal to the general audience what on earth did I do wrong...
3
by: bjam | last post by:
Hi, I am hoping someone can help with this I am assuming it is pretty basic but I don't see anything that jumps out at me at how to do this. Is there a way to have all the items output from the...
12
by: Jose Fernandez | last post by:
Hello. I'm building a web service and I get this error. NEWS.News.CoverNews(string)': not all code paths return a value This is the WebMethod public SqlDataReader CoverNews(string Sport)...
5
by: D. Shane Fowlkes | last post by:
This may be a very basic question but it's something I've never done before. I've looked at a couple of my favorite sites and books and can't find an answer either. I can write a Function to...
11
by: Tim Frawley | last post by:
I need to return a DataRow or the Row Index in a DataSet wherein the value I am attempting to find is not a primary key. I have to do this often, more than 200 times when importing a file so it...
10
by: Peter | last post by:
Hi, how can I do this (I don't really want to do this but what I do want the same function name but have different return types and the compiler keeps saying "public function .... they differ only...
2
by: Arne | last post by:
Will the dataset below be returned by value or reference? Public Shared Function getDS() As DataSet Dim ds As New DataSet '... do something Return ds End Function
4
by: yaru22 | last post by:
In one of the examples in the book I'm reading, it says: def __init__(self): ... ... ... return It has nothing after "return". I expected it to have some number like 0 or 1.
13
by: cppquester | last post by:
A colleague told me that there is a rule about good stype that a function in C++ should have only one point of return (ie. return statement). Otherwise there might be trouble. I never heard about...
1
by: psycho | last post by:
How do we return a single value from a stored procedure. Suppose I have a stored procedure like this: create proc dbo.spInsertGroup @ID uniqueidentifier @GroupName varchar(100), @IsActive...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.