help with some ASP code. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

help with some ASP code.

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-07-14 : 03:15:40

Hey, I need a little help on an issue I am having. What I am doing is connecting to a table which is setup with a row for each move a person knows. So if the person knows two moves there are two entrees for that person. What I want to do is write an if command the goes though all the entrees and if there is no entrée for the work “Special” and then write some code back to the web page. I was going to do an do while not objrs.eof and an if statement but that is looking at all then entrees one by one and reting the code multiple times. Anyone know what I might be able to do to stop this? Here is my code:
------------code----------
do while not objrs.eof
if trim(objrs("movename")) = "Special" then
else
response.write “<option value=’learnspecial’>Learn your Special Move</option>”
end if
objrs.movenext
loop.

--
For thouse with wings, fly to your dreams.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-14 : 10:03:28

Why not only retrive from the database what you are interested in.

if exists
select * from tbl where user_name = 'myuser' and movename = 'special'
select 1
else
select 2

or just
select * from tbl where user_name = 'myuser' and movename = 'special'
and check for an empty recordset

in your code you could set a flag

do while not objrs.eof and flag = 0
and set the flag to 1 in the else.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-07-15 : 04:49:55

I am only looking at the info in the one table I need it from. The only thing wrong with your suggestion is that your making a string for the single attack. This is a game which has almost 40 differant attackes each with differant levels. I dont' think it is going to be very good for makeing a str for each move and level for 75 players. Got any other ideas?

--
For thouse with wings, fly to your dreams.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-15 : 08:31:48

Eagle_f90,

Not sure if this is what you are asking, but why not just count the number of special moves for the user? Something like

select count(*)
from tbl
where user_name = 'myuser'
and movename = 'special'

You can then get the rs.recordcount and if it is > 0, then the user has special moves. If not, build your response.write “<option value=’learnspecial’>Learn your Special Move</option>” .

Another possible optimization for the game if you are calling data from the database all the time is to store the recordset into an array as soon as you set the recordset.

Instead of "do while not objrs.eof", loop etc., etc., try myArray = rs.getrows(). This loads the recordset into the myArray variable allowing you to go ahead and close the connection to the database.

Then you can go through the array instead of pinging the open recordset and making calls to the backend again. I have have great performance gains using the following when I am getting back a lot of information from the database and do not want to paginate.

Dim iRowLoop1
Dim myArray

myArray = rs.getrows()
For iRowLoop1 = 0 to UBound(myArray,2)
Response.Write myArray(0,iRowLoop1)
Response.Write myArray(1,iRowLoop1)
Response.Write myArray(2,iRowLoop1)
Next

Where 0 in myArray(0,iRowLoop1) is going to be the first column from your select statement.

Just a suggestion that may improve performace on the website.

Let me know if the above is not what you meant for the special move.

Jeremy

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-07-16 : 23:05:06

Just a quick question in the
Response.Write myArray(0,iRowLoop1)
Response.Write myArray(1,iRowLoop1)
Response.Write myArray(2,iRowLoop1)

is 0, is always the first row or do I have to define that two?

--
For thouse with wings, fly to your dreams.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-17 : 08:16:58

0 in myArray(0,iRowLoop1) refers to the column in the array. Basically, when you load the recordset into an array, you are in a manner of speaking loading a temp table (not exactly, but for illustration purposes). So you end up with something like the following:

Index F_Name L_Name etc.
0 Eagle f90
1 Jeremy Oldham

The iRowLoop1 defines the index of the array, so 0 to UBound(myArray,2) says start at row 0 and go through the upper bound of the index. Then the myArray(0,iRowLoop1) says give me column 0 of the current row. (Stating the obvious here that arrays are 0 based)

Let me know if you have any more questions.

Did the Count(*) solution solve your other problem?

Jeremy

Edited by - joldham on 07/17/2002 08:17:31

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-07-18 : 03:49:25

Ok, with your help you have fixed one of two problems. Not the one I was looking for but in my page i keep having to close the connectin and then open it aging. But with your idead to put it to the "0,iRowLoop1" I did figure out a a possible sulotion to my curent issue (all though it has not been tested yet). I basicly do an if move name <> spcial then move to the next entree else write the info. I dd try the count but it did the same thing, keep writing the same thing over and over.

--
For thouse with wings, fly to your dreams.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-18 : 08:31:19

I am not sure if the following will work, but you can give it a try if you think it meets the requirements. I have left out unecessary code that you should know what to do with.

strSQL = "select * from tbl where user_name = 'myuser' and movename = 'special'"
....
Dim iRowLoop1
Dim myArray

myArray = objrs.getRows()

if UBound(myArray,2) < 1 then
response.write "<option value=’learnspecial’>Learn your Special Move</option>"
end if

Basically, the SQL is from nr's post to see if there are any special moves. Since you load this recordset into the array myArray, you can then do one if statement to see if there is anything in the array. If there isn't, then you write the Learn special move stuff.

As far as having to re-open the connections, I would put all your SQL statements at the beginning and load each one of them into their individual arrays and then close the connection. Not sure this will solve your problem without seeing the whole page code. Another little trick I learned (if it can be done within your requirements) is to add all the information you need for a page to return in one SP. For instance,

CREATE PROCEDURE usp_game_info
AS
Select statement 1

Select statement 2

Select Statement 3

Where each of the select statements are different information you need at different areas of the page. Then in code, you can access the different select statements as follows:

Dim myArray1, myArray2, myArray3
.....
myArray1 = objRs.getRows()
Set objRS = objRS.NextRecordset
myArray2 = objRs.getRows()
Set objRS = objRS.NextRecordset
myArray3 = objRs.getRows()

objRS.Close
Set objRs = Nothing
conn.close
Set conn = Nothing

This way you can load all your data at once and close your connection as quickly as possible. You endure some overhead the entire time a connection is open, whether you are actively using it or not. So using the above, you cut down on the time you have to keep the connection open.

Let me know if we are getting any closer to solving the issue.

Jeremy

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2002-07-18 : 21:06:34

I just tested your code for my moves problem and it seams to be working! As for the closing and opening I have done what you suggested and loaded all the info into an Array and it is running a lot faster and is taking a lot less code. Thanks for your help and patients in helping with my issue! YOU RULE!

--
For thouse with wings, fly to your dreams.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources