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("s elect sum(wins1) as wins_8 from matches_8ball
where username = '" & var & "'")
if not wins8.eof then
wins__8 = wins8.fields.it em("wins_8").va lue
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]

Re: asp/sql


"Jeff" <gig_bam@adelph ia.net> wrote in message
news:INidnWF8rZ 4_j7HZnZ2dneKdn ZydnZ2d@adelphi a.com...[color=blue]
> 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("s elect sum(wins1) as wins_8 from matches_8ball
> where username = '" & var & "'")
> if not wins8.eof then
> wins__8 = wins8.fields.it em("wins_8").va lue
> 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[/color]

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

select coalesce(sum(wi ns1), 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]

Re: asp/sql

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.


"Mark J. McGinty" <mmcginty@spamf romyou.com> wrote in message
news:u5fdDKCVGH A.1160@TK2MSFTN GP09.phx.gbl...[color=blue]
>
> "Jeff" <gig_bam@adelph ia.net> wrote in message
> news:INidnWF8rZ 4_j7HZnZ2dneKdn ZydnZ2d@adelphi a.com...[color=green]
>> 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("s elect sum(wins1) as wins_8 from matches_8ball
>> where username = '" & var & "'")
>> if not wins8.eof then
>> wins__8 = wins8.fields.it em("wins_8").va lue
>> 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[/color]
>
> What db engine are you using? If SQL Server, try:
>
> select coalesce(sum(wi ns1), 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
>
>
>
>[/color]

[Slim]

Re: asp/sql

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



"Jeff" <gig_bam@adelph ia.net> wrote in message
news:INidnWF8rZ 4_j7HZnZ2dneKdn ZydnZ2d@adelphi a.com...[color=blue]
> 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("s elect sum(wins1) as wins_8 from matches_8ball
> where username = '" & var & "'")
> if not wins8.eof then
> wins__8 = wins8.fields.it em("wins_8").va lue
> 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
>[/color]

[Bob Barrows [MVP]]

Re: asp/sql

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(va r)
set cmd=createobjec t("adodb.comman d")
cmd.commandtext =sql
cmd.commandtype =1
set cmd.activeconne ction=conn
set wins8 = cmd.execute(,ar parm)
wins__8=0
if wins8(1) > 0 then wins__8=wins8(0 )
wins8.close:set wins8=nothing

HTH,
Bob Barrows


Jeff wrote:[color=blue]
> 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.
>
>
> "Mark J. McGinty" <mmcginty@spamf romyou.com> wrote in message
> news:u5fdDKCVGH A.1160@TK2MSFTN GP09.phx.gbl...[color=green]
>>
>> "Jeff" <gig_bam@adelph ia.net> wrote in message
>> news:INidnWF8rZ 4_j7HZnZ2dneKdn ZydnZ2d@adelphi a.com...[color=darkred]
>>> 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("s elect sum(wins1) as wins_8 from
>>> matches_8ball where username = '" & var & "'")
>>> if not wins8.eof then
>>> wins__8 = wins8.fields.it em("wins_8").va lue
>>> 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[/color]
>>
>> What db engine are you using? If SQL Server, try:
>>
>> select coalesce(sum(wi ns1), 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[/color][/color]

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

[Jeff]

Re: asp/sql

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

thanks again everyone


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:eOxK6nCVGH A.6048@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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(va r)
> set cmd=createobjec t("adodb.comman d")
> cmd.commandtext =sql
> cmd.commandtype =1
> set cmd.activeconne ction=conn
> set wins8 = cmd.execute(,ar parm)
> wins__8=0
> if wins8(1) > 0 then wins__8=wins8(0 )
> wins8.close:set wins8=nothing
>
> HTH,
> Bob Barrows
>
>
> Jeff wrote:[color=green]
>> 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.
>>
>>
>> "Mark J. McGinty" <mmcginty@spamf romyou.com> wrote in message
>> news:u5fdDKCVGH A.1160@TK2MSFTN GP09.phx.gbl...[color=darkred]
>>>
>>> "Jeff" <gig_bam@adelph ia.net> wrote in message
>>> news:INidnWF8rZ 4_j7HZnZ2dneKdn ZydnZ2d@adelphi a.com...
>>>> 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("s elect sum(wins1) as wins_8 from
>>>> matches_8ball where username = '" & var & "'")
>>>> if not wins8.eof then
>>>> wins__8 = wins8.fields.it em("wins_8").va lue
>>>> 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(wi ns1), 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[/color][/color]
>
> --
> 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]

[Bob Barrows [MVP]]

Re: asp/sql

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

Jeff wrote:[color=blue]
> I am really trying to understand using array's. this works, now i am
> learning why it works.
>
> thanks again everyone
>
>
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:eOxK6nCVGH A.6048@TK2MSFTN GP11.phx.gbl...[color=green]
>> 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(va r)
>> set cmd=createobjec t("adodb.comman d")
>> cmd.commandtext =sql
>> cmd.commandtype =1
>> set cmd.activeconne ction=conn
>> set wins8 = cmd.execute(,ar parm)
>> wins__8=0
>> if wins8(1) > 0 then wins__8=wins8(0 )
>> wins8.close:set wins8=nothing
>>[/color][/color]

[Jeff]

Re: asp/sql

sql=select sum(wins1) as wins_8, " & _
"count(*) as totalwins from matches_8ball " & _
"where username = ?"
arparm=array(va r)
set cmd=createobjec t("adodb.comman d")
cmd.commandtext =sql
cmd.commandtype =1
set cmd.activeconne ction=conn
set wins8 = cmd.execute(,ar parm)
wins__8=0
if wins8(1) > 0 then wins__8=wins8(0 )
wins8.close:set wins8=nothing

thought this meant array??



"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message news:uXCMzZHVGH A.5004@TK2MSFTN GP11.phx.gbl...[color=blue]
> "arrays"? Why are arrays relevant to either the question you asked or the
> answer I provided?
>
> Jeff wrote:[color=green]
>> I am really trying to understand using array's. this works, now i am
>> learning why it works.
>>
>> thanks again everyone
>>
>>
>> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
>> news:eOxK6nCVGH A.6048@TK2MSFTN GP11.phx.gbl...[color=darkred]
>>> 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(va r)
>>> set cmd=createobjec t("adodb.comman d")
>>> cmd.commandtext =sql
>>> cmd.commandtype =1
>>> set cmd.activeconne ction=conn
>>> set wins8 = cmd.execute(,ar parm)
>>> wins__8=0
>>> if wins8(1) > 0 then wins__8=wins8(0 )
>>> wins8.close:set wins8=nothing
>>>[/color][/color]
>
>[/color]

[Bob Barrows [MVP]]

Re: asp/sql

Jeff wrote:[color=blue]
> sql=select sum(wins1) as wins_8, " & _
> "count(*) as totalwins from matches_8ball " & _
> "where username = ?"
> arparm=array(va r)
> set cmd=createobjec t("adodb.comman d")
> cmd.commandtext =sql
> cmd.commandtype =1
> set cmd.activeconne ction=conn
> set wins8 = cmd.execute(,ar parm)
> wins__8=0
> if wins8(1) > 0 then wins__8=wins8(0 )
> wins8.close:set wins8=nothing
>
> thought this meant array??[/color]


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_fiel d = ?"

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

arparms=array(v ar,some_other_v ar)
....
set rs = cmd.execute(,ar parms)

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/adva..._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...dooverview.asp or
pick up a book such as David Sceppa's "programmin g 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.

[Jeff]

Re: asp/sql

looking for that nook right now.. thanks a bunch

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:%23Sqm3YMV GHA.5468@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> Jeff wrote:[color=green]
>> sql=select sum(wins1) as wins_8, " & _
>> "count(*) as totalwins from matches_8ball " & _
>> "where username = ?"
>> arparm=array(va r)
>> set cmd=createobjec t("adodb.comman d")
>> cmd.commandtext =sql
>> cmd.commandtype =1
>> set cmd.activeconne ction=conn
>> set wins8 = cmd.execute(,ar parm)
>> wins__8=0
>> if wins8(1) > 0 then wins__8=wins8(0 )
>> wins8.close:set wins8=nothing
>>
>> thought this meant array??[/color]
>
>
> 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_fiel d = ?"
>
> then you would use the array() function to create an array with two
> elements:
>
> arparms=array(v ar,some_other_v ar)
> ...
> set rs = cmd.execute(,ar parms)
>
> 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/adva..._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...dooverview.asp or
> pick up a book such as David Sceppa's "programmin g 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.
>
>[/color]