has anybody found a way to set the cursortype of a recordset now that DW uses <b>Server.CreateObject ("ADODB.Command"</b> instead of <b>Server.CreateObject("ADODB.Recordset"</b>??????

thanks


UD4|DW8.02|DWCS3|ASP|VBScript|IIS5.1|Access|WinXPPro & WinXPHome.
www.reunite.co.uk
www.dogworld-uk.com

anybody???

UD4|DW8.02|DWCS3|ASP|VBScript|IIS5.1|Access|WinXPPro & WinXPHome.
www.reunite.co.uk
www.dogworld-uk.com

I know this problem has been taxing a lot of people and no one seems to know the answer... well i believe i have cracked it. The problem is that using <b>Server.CreateObject ("ADODB.Command"</b> to open your recordset means you cannot alter the cursortype from <b>adOpenForwardOnly</b> therefore you can only use the results of the recordset once on the page.

However <b>Server.CreateObject("ADODB.Connection"</b> allows you to set the cursorlocation to the client machine instead of the server, if you do this the default cursortype is always <b>adOpenStatic</b> which allows movement backwards and forwards through a recordset and so you can use it more than once on the page.

With this in mind i have edited the default recordset creating code to use connection as well as command, it seems to work.

The problem is that because you have edited it, DW no longer recognises the recordset so make sure you have finished your page before doing it, you will get lots of errors showing up in the server behaviours inspector but they should still work.
So

<b>&lt;%
Dim rsAllaccomm
Dim rsAllaccomm_cmd
Dim rsAllaccomm_numRows
Set rsAllaccomm_cmd = Server.CreateObject ("ADODB.Command"
rsAllaccomm_cmd.ActiveConnection = MM_conncaravan_STRING
rsAllaccomm_cmd.CommandText = "SELECT * FROM Apartments"
rsAllaccomm_cmd.Prepared = true
Set rsAllaccomm = rsAllaccomm_cmd.Execute
rsAllaccomm_numRows = 0
%&gt;</b>

becomes

<b>&lt;%
Const adUseClient = 3
Dim rsAllaccommConnection
Dim rsAllaccomm
Dim rsAllaccomm_cmd
Dim rsAllaccomm_numRows
Set rsAllaccomm_cmd = Server.CreateObject ("ADODB.Command"
Set rsAllaccommConnection = Server.CreateObject("ADODB.Connection"
rsAllaccommConnection.ConnectionString = MM_conncaravan_STRING
rsAllaccommConnection.cursorLocation = adUseClient
rsAllaccommConnection.Open
rsAllaccomm_cmd.ActiveConnection = rsAllaccommConnection
rsAllaccomm_cmd.CommandText = "SELECT * FROM Apartments"
rsAllaccomm_cmd.Prepared = true
Set rsAllaccomm = rsAllaccomm_cmd.Execute
rsAllaccomm_numRows = 0
%&gt;</b>

I have tested this a few times and I can always use the recordset more than once on a page so it seems to work.

oh and don't forget to close the connection at the bottom of your page, just like you do with the recordset

<b>&lt;%
rsAllaccommConnection.Close()
Set rsAllaccommConnection = Nothing
%&gt;</b>

you may now bow & scrape and call me "God" <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

UD4|DW8.02|DWCS3|ASP|VBScript|IIS5.1|Access|WinXPPro & WinXPHome.
www.reunite.co.uk
www.dogworld-uk.com

Edited by - Davecl on 04 Feb 2008 11:08:40

Nice work dave,

I shall certainly give it a go and see what 'clever' applications I can think of to use it!

Would be great if you could post some links to pages using the theory for us to look at....

(and a mat for us to kneel on?)

<img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]