 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / December 2006Recordset Closed After Having Just Being Opened
Hey, If someone could help me with this it would be greatly appreciated. I have the following code, part of a function (irrevlant and parameter declaration has been removed). Suffised to say the function works most of the time and a recordset is usually returned. However sometimes, as is commented the recordset is closed after the execute statement. What is causing this to happen? Dim sqlConn As ADODB.Connection Dim sqlCmd As ADODB.Command Dim sqlRS As ADODB.Recordset ' Establish connection. Set sqlRS = New ADODB.Recordset Set sqlCmd = New ADODB.Command Set sqlConn = New ADODB.Connection sqlConn.ConnectionString = CurrentProject.Connection sqlConn.Open sqlConn.CursorLocation = adUseClient 'Cache data locally ' Open recordset. With sqlCmd .ActiveConnection = sqlConn .CommandText = ExecuteSP.Name .CommandType = adCmdStoredProc .Parameters.Refresh .Parameters....(removed) Set sqlRS = .Execute() End With 'Here the SP has executed correctly but the sqlRS is closed ;( ???!?!?!? Regards, Marc > Hey, > If someone could help me with this it would be greatly appreciated. [quoted text clipped - 30 lines] > 'Here the SP has executed correctly but the sqlRS is closed ;( > ???!?!?!? Just a quick comment... This is a common phenomena when using ADO to call a stored procedure if no data is returned. But the exact details escape me at the moment. IIRC there are possible changes that can be made to the SP itself to protect against this. (depending on provider/database of course). I'll do a little research and get back to you. But for the moment it will help to assure you that no major 'bug' is at work here. <g> What database are you using? SQLServer? -ralph Yes SQLserver with an access ADP 2003 frontend. Regards, Marc > > Hey, > > If someone could help me with this it would be greatly appreciated. [quoted text clipped - 44 lines] > > -ralph > Yes SQLserver with an access ADP 2003 frontend. You are creating a "firehose", a read-only, forward-only recordset. If the procedure returns 0 rows, there would be nothing else you could do with it -- can't insert, got no rows... nothing left to do but close it, so it does that for you. If you need a different kind of cursor, pass the command object to a recordset as the source parameter (along with desired options.) If you just need to detect that this is the case, test the recordset.State property; as long as the recordset has been created, it will always be valid to reference. -Mark > Regards, > Marc [quoted text clipped - 49 lines] >> >> -ralph Hey Mark, Many thanks for that. Could you possibly elobrate what you mean by 'pass the command object to a recordset as the source parameter'. I have tried something similar to sqlRS.Open command, connection etc Regards, Marc > > Yes SQLserver with an access ADP 2003 frontend. > [quoted text clipped - 64 lines] > >> > >> -ralph > Hey Mark, > Many thanks for that. Could you possibly elobrate what you mean > by 'pass the command object to a recordset as the source parameter'. I > have tried something similar to > > sqlRS.Open command, connection etc You must set the command.ActiveConnection to an already-opened connection object, and omit the connection parameter when calling recordset.Open: Set command.ActiveConnection = connection sqlRs.Open command, , [other params] -Mark > Regards, > Marc [quoted text clipped - 78 lines] >> >> >> >> -ralph Do you have "set nocount on" in the stored proc? Hey, If someone could help me with this it would be greatly appreciated. I have the following code, part of a function (irrevlant and parameter declaration has been removed). Suffised to say the function works most of the time and a recordset is usually returned. However sometimes, as is commented the recordset is closed after the execute statement. What is causing this to happen? Dim sqlConn As ADODB.Connection Dim sqlCmd As ADODB.Command Dim sqlRS As ADODB.Recordset ' Establish connection. Set sqlRS = New ADODB.Recordset Set sqlCmd = New ADODB.Command Set sqlConn = New ADODB.Connection sqlConn.ConnectionString = CurrentProject.Connection sqlConn.Open sqlConn.CursorLocation = adUseClient 'Cache data locally ' Open recordset. With sqlCmd .ActiveConnection = sqlConn .CommandText = ExecuteSP.Name .CommandType = adCmdStoredProc .Parameters.Refresh .Parameters....(removed) Set sqlRS = .Execute() End With 'Here the SP has executed correctly but the sqlRS is closed ;( ???!?!?!? Regards, Marc |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.