|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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
Show quote
"Jeff" <gig_***@adelphia.net> wrote in message What db engine are you using? If SQL Server, try:news:INidnWF8rZ4_j7HZnZ2dneKdnZydnZ2d@adelphia.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("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 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 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. Show quote "Mark J. McGinty" <mmcginty@spamfromyou.com> wrote in message news:u5fdDKCVGHA.1160@TK2MSFTNGP09.phx.gbl... > > "Jeff" <gig_***@adelphia.net> wrote in message > news:INidnWF8rZ4_j7HZnZ2dneKdnZydnZ2d@adelphia.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("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 > > > > 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 wrote: Show quote > 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@spamfromyou.com> wrote in message > news:u5fdDKCVGHA.1160@TK2MSFTNGP09.phx.gbl... >> >> "Jeff" <gig_***@adelphia.net> wrote in message >> news:INidnWF8rZ4_j7HZnZ2dneKdnZydnZ2d@adelphia.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("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 -- 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. I am really trying to understand using array's. this works, now i am
learning why it works. thanks again everyone Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:eOxK6nCVGHA.6048@TK2MSFTNGP11.phx.gbl... > 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 wrote: >> 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@spamfromyou.com> wrote in message >> news:u5fdDKCVGHA.1160@TK2MSFTNGP09.phx.gbl... >>> >>> "Jeff" <gig_***@adelphia.net> wrote in message >>> news:INidnWF8rZ4_j7HZnZ2dneKdnZydnZ2d@adelphia.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("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 > > -- > 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. > > "arrays"? Why are arrays relevant to either the question you asked or the
answer I provided? Jeff wrote: Show quote > 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@NOyahoo.SPAMcom> wrote in message > news:eOxK6nCVGHA.6048@TK2MSFTNGP11.phx.gbl... >> 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 >> 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?? Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:uXCMzZHVGHA.5004@TK2MSFTNGP11.phx.gbl... > "arrays"? Why are arrays relevant to either the question you asked or the > answer I provided? > > Jeff wrote: >> 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@NOyahoo.SPAMcom> wrote in message >> news:eOxK6nCVGHA.6048@TK2MSFTNGP11.phx.gbl... >>> 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 >>> > > Jeff wrote:
Show quote > sql=select sum(wins1) as wins_8, " & _ Oh I see where you got that now. Yes, there is one array in the code:> "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?? 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. looking for that nook right now.. thanks a bunch
Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:%23Sqm3YMVGHA.5468@TK2MSFTNGP14.phx.gbl... > Jeff wrote: >> 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". > > -- > 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. > > 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 Show quote "Jeff" <gig_***@adelphia.net> wrote in message news:INidnWF8rZ4_j7HZnZ2dneKdnZydnZ2d@adelphia.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("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 >  |
|||||||||||||||||||||||
Other interesting topics