Resultset and connection status questions

mrjeffy321
10-21-2004, 03:43 PM
two diferent questions here:

1. the more inportant one. How can I tell how many results were returned in my result set after I query a database with an SQL statement. I need to know becuase depending on how many I get, I will need to take diferent courses of action, there are only 3 possibilies either I get nothing back, one back, or more than 1 back, I need to test how many I get.

2. is there a way to test whether or not I have an established connection allready to a database? I would like to be able to know this so I can tell if I need to close it first, before I open a new one. I thought I could just use my own boolean vaiable and set it myself when I had one or when I closed one, but is there a way of getting it straight from the connection?

ed_creed
10-21-2004, 03:57 PM
two diferent questions here:

1. How can I tell how many results were returned in my result set after I query a database with an SQL statement.



2. is there a way to test whether or not I have an established connection allready to a database?


1. rs.RecordCount........if rs.RecordCount > 1 then...blah blah blah

2. if rs.state = 1 then
rs.close
end if

mrjeffy321
10-21-2004, 04:46 PM
1. rs.RecordCount

this doesnt work, this seemed like the obvious choice to me when I was trying to figure it out on my own, but no, I dont understand it.
when I have a result set with what I know to be item in it, it gives me a return value of -1. when I dont have anything in my result set, it gives me an error saying, "the operation requested by the application is not allowed if the object is open". and if I have more than one item in the resuly set then me another error. so this cannot be right, at least the way I am using it, and the way you suggested it.

loquin
10-21-2004, 05:00 PM
Recordcount only works when the data source is set up with client-side cursors. This will copy the entire table to the client, though, which may result in poor performance. If there IS a lot of data in the entire table, try issuing a "Select Count(Fieldname)" SQL command first, with the same WHERE parameters in the query. Then, get the returned record count from rs.fields(0).Value.

Then, close rs, and re-open with your existing query.

mrjeffy321
10-21-2004, 06:14 PM
SELECT count(*) from tablename WHERE ...

there that seems to do it.


both of my questions now seem to be be asnwered, Thanks

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum