asp/sql

[Jeff]

In my asp script, i am using sql to get a sum. I understand if the row
doesn't contain a value, it is skipped. But the problem i am running into is
if there isn't a row that meets the criteria at all.

i have this

set wins8 = conn.execute("select sum(wins1) as wins_8 from matches_8ball
where username = '" & var & "'")
if not wins8.eof then
wins__8 = wins8.fields.item("wins_8").value
else
wins__8 = 0
end if

i thought that if it didn't find the '" & var & "' at all in the username
column, it should return a 0 value. but this isn't working as you can see
here.

http://pool.gig-gamers.com/all_stats2.asp

and since that isn't working, or any of the others like it, the table
contains blank spaces.
any ideas??

i guess i am trying to see if there is a way to maybe say... if wins__8 =
Nul or something

 

[Mark J. McGinty]

In my asp script, i am using sql to get a sum. I understand if the row
doesn't contain a value, it is skipped. But the problem i am running into
is if there isn't a row that meets the criteria at all.

i have this

set wins8 = conn.execute("select sum(wins1) as wins_8 from matches_8ball
where username = '" & var & "'")
if not wins8.eof then
wins__8 = wins8.fields.item("wins_8").value
else
wins__8 = 0
end if

i thought that if it didn't find the '" & var & "' at all in the username
column, it should return a 0 value. but this isn't working as you can see
here.

http://pool.gig-gamers.com/all_stats2.asp

and since that isn't working, or any of the others like it, the table
contains blank spaces.
any ideas??

i guess i am trying to see if there is a way to maybe say... if wins__8 =
Nul or something

What db engine are you using? If SQL Server, try:

select coalesce(sum(wins1), 0) as wins_8 from matches_8ball ...

Also consider parameterizing that query, by using a command object, rather
than dynamic SQL, which risks a SQL injection attack.


-Mark

 

[Jeff]

i am using Access DB, and I already tried that.
I am getting it to work, just takes extra work.
i am getting a count(username) first, then if the count is > 0 process it,
if not, then the var = 0

it is working that way, but i am still looking for an easier way to do it.

 

[Slim]

the error is telling you that the field wins_8 does not exist in the table
you are accessing, or maybe the "_" is causing problems, try "sum(wins1) as
wins8" maybe

 

[Bob Barrows [MVP]]

Do both the count and the sum in the same statement:

sql=select sum(wins1) as wins_8, " & _
"count(*) as totalwins from matches_8ball " & _
"where username = ?"
arparm=array(var)
set cmd=createobject("adodb.command")
cmd.commandtext=sql
cmd.commandtype=1
set cmd.activeconnection=conn
set wins8 = cmd.execute(,arparm)
wins__8=0
if wins8(1) > 0 then wins__8=wins8(0)
wins8.close:set wins8=nothing

HTH,
Bob Barrows

 

[Jeff]

I am really trying to understand using array's. this works, now i am
learning why it works.

thanks again everyone

 

[Bob Barrows [MVP]]

"arrays"? Why are arrays relevant to either the question you asked or the
answer I provided?

 

[Bob Barrows [MVP]]

sql=select sum(wins1) as wins_8, " & _
"count(*) as totalwins from matches_8ball " & _
"where username = ?"
arparm=array(var)
set cmd=createobject("adodb.command")
cmd.commandtext=sql
cmd.commandtype=1
set cmd.activeconnection=conn
set wins8 = cmd.execute(,arparm)
wins__8=0
if wins8(1) > 0 then wins__8=wins8(0)
wins8.close:set wins8=nothing

thought this meant array??

Oh I see where you got that now. Yes, there is one array in the code:
arparm. This is because a variant array (an array containing a set of
variant elements) is used to pass arguments (data) to either a stored
procedure or a string containing parameter markers (the question marks) in a
Command object's Execute statement. If there were multiple parameter
markers, like

sql=select sum(wins1) as wins_8, " & _
"count(*) as totalwins from matches_8ball " & _
"where username = ? and some_other_field = ?"

then you would use the array() function to create an array with two
elements:

arparms=array(var,some_other_var)
....
set rs = cmd.execute(,arparms)

The main reason* I like to do it this way is that using parameters is the
main line of defense against sql injection
(http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf)
Of course, this should not be your only line of defense: validating user
input should always be done in server-side code before sending it to your
database. Using parameters does not prevent hackers from injecting malicious
html into your database fields (which is why it is a good idea to always
HTMLEncode data that came from user inputs before writing it to the
Response)

* A secondary reason is it relieves me of the requirement of worrying about
delimiters in the sql strings I write. Do you see any apostrophes in the
above sql string?

You may find it helpful to read through the ADO documentation at
http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadooverview.asp or
pick up a book such as David Sceppa's "programming ADO".

 

[Jeff]

looking for that nook right now.. thanks a bunch

Oh I see where you got that now. Yes, there is one array in the code:
arparm. This is because a variant array (an array containing a set of
variant elements) is used to pass arguments (data) to either a stored
procedure or a string containing parameter markers (the question marks) in
a
Command object's Execute statement. If there were multiple parameter
markers, like

sql=select sum(wins1) as wins_8, " & _
"count(*) as totalwins from matches_8ball " & _
"where username = ? and some_other_field = ?"

then you would use the array() function to create an array with two
elements:

arparms=array(var,some_other_var)
...
set rs = cmd.execute(,arparms)

The main reason* I like to do it this way is that using parameters is the
main line of defense against sql injection
(http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf)
Of course, this should not be your only line of defense: validating user
input should always be done in server-side code before sending it to your
database. Using parameters does not prevent hackers from injecting
malicious
html into your database fields (which is why it is a good idea to always
HTMLEncode data that came from user inputs before writing it to the
Response)

* A secondary reason is it relieves me of the requirement of worrying
about
delimiters in the sql strings I write. Do you see any apostrophes in the
above sql string?

You may find it helpful to read through the ADO documentation at
http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadooverview.asp or
pick up a book such as David Sceppa's "programming ADO".

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