I have a website that i am converting from MS Access to MS SQL Server. I have a select statement on on page, that worked fine in Access, and currently works fine in Query Analyzer, but wont work properly on the asp page.

The recordcount always comes back -1

anyone help me on why its not wrorking?

Thanks

post the code

try

Rs.CursorType = 3

after define the recordset.

ZeligOn: I get "Operation is not allowed when the object is open"

i tried declaring the recordset then trying to set it but i couldnt get it to work

Memnoch1207:

here is the shortened form........

set objcon = server.createobject("ADODB.connection")

objcon.Open ("Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=mydatabase;" & _
"User ID=xxxx;" & _
"Password=xxxx")


selsql2 = "SELECT tblSites.site_name, tblSites.site_id, " _
& "tblSites.area_id, tblMessages.message_date, " _
& "tblProduction.time, tblProduction.FlowDuration, " _
& "tblProduction.GasVol, tblProduction.AvgSP, " _
& "tblProduction.AvgFT, tblProduction.AvgDP, " _
& "tblProduction.AvgTubP, tblProduction.AvgCasP, " _
& "tblProduction.section_type, tblUserSites.User_ID, " _
& "tblmessages.message_date_no_time FROM " _
& "(tblSites INNER JOIN (tblMessages INNER JOIN tblProduction " _
& "ON tblMessages.message_id = tblProduction.message_id) ON " _
& "tblSites.site_id = tblMessages.site_id) INNER JOIN " _
& "tblUserSites ON tblSites.site_id = tblUserSites.Site_Id " _
& "WHERE (((tblProduction.section_type)=1) AND " _
& "((tblUserSites.site_ID)=14) AND "_
& "((tblUserSites.user_ID)=5) And " _
& "((tblmessages.message_date_no_time) > '1/4/2003')) " _
& "ORDER BY tblmessages.message_date_no_time DESC"

set objrs2 = objcon.execute(selsql2)

'if the data for the site is not todays data, make the font color red, otherwise, make the fontcolor black.
if objrs2.eof then
response.write "<td colspan=6>" & tablefontsize & "Internal Server Error "</td></tr>"
else

Blah blah blah.....

Hi this is no surprise, somehow recordcount donot works in oracle and sql server using oledb. Though you can try to open recordset using 1,3,2 in last in rs.open statement where you specify cursor type,location etc