| Groups | Blog | Home |
inetserver asp general : asp/sql
[quoted text, click to view] "Jeff" <gig_bam@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
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
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. [quoted text, click to view] "Mark J. McGinty" <mmcginty@spamfromyou.com> wrote in message news:u5fdDKCVGHA.1160@TK2MSFTNGP09.phx.gbl... > > "Jeff" <gig_bam@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 [quoted text, click to view] 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_bam@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 [quoted text, click to view] "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_bam@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? [quoted text, click to view] 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 >>
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 [quoted text, click to view] "Jeff" <gig_bam@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 >
sql=3Dselect sum(wins1) as wins_8, " & _
"count(*) as totalwins from matches_8ball " & _ "where username =3D ?" arparm=3Darray(var) set cmd=3Dcreateobject("adodb.command") cmd.commandtext=3Dsql cmd.commandtype=3D1 set cmd.activeconnection=3Dconn set wins8 =3D cmd.execute(,arparm) wins__8=3D0 if wins8(1) > 0 then wins__8=3Dwins8(0) wins8.close:set wins8=3Dnothing thought this meant array?? [quoted text, click to view] "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=20 > answer I provided? >=20 > 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=3Dselect sum(wins1) as wins_8, " & _ >>> "count(*) as totalwins from matches_8ball " & _ >>> "where username =3D ?" >>> arparm=3Darray(var) >>> set cmd=3Dcreateobject("adodb.command") >>> cmd.commandtext=3Dsql >>> cmd.commandtype=3D1 >>> set cmd.activeconnection=3Dconn >>> set wins8 =3D cmd.execute(,arparm) >>> wins__8=3D0 >>> if wins8(1) > 0 then wins__8=3Dwins8(0) >>> wins8.close:set wins8=3Dnothing >>> >=20
[quoted text, click to view]
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.
looking for that nook right now.. thanks a bunch
[quoted text, click to view] "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. > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |