Resultset and connection status questions

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?

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

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.

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.

SELECT count(*) from tablename WHERE ...

there that seems to do it.


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