ADO.COMMAND

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Avi G - 28 Aug 2003 18:31 GMT

Hi. I have a question. I would like to use paging. So I need a recordset
that is either keyset or dynamic cursortype. I would like to use the
ado.command object because then I can have single quotes ( ' ) in my
parameters. However I do not see anywhere that I can change the command
object to be either dynamic or keyset. How do I do this?
Paging and ' in my program?
I use VB 6, SQL Server, ADO 2.8
Thanks.
Sample:
Dim myRes As New ADODB.Recordset
Dim cmd As New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "storedProcedureName"
'I can pass in a single quote, but no paging enabled.
cmd.Parameters.AppEnd cmd.CreateParameter("@search", adVarChar,
adParamInput, 500,"DAVID'S")
Set myRes = cmd.Execute
'---------------------------------------------------------------------
'Here no single quotes, but there is paging enabled
Dim strSearch As String
strSearch = Replace(tlbContactTop.Tools("ID_SearchContact").Edit.Text,
"'", "")
myRes.CursorLocation = adUseClient
myRes.Open "storedProcedureName '" & strSearch & "'", oSQLConnection,
adOpenDynamic, adLockOptimistic

Thanks for all your help,
Avi

Reply to this Message

Val Mazur - 29 Aug 2003 03:45 GMT

Hi,

If you need to specify cursor settings, then you could use Recordset's Open
method and pass Command as an source, for example

MyADORecordset.Open cmd, MyADOConnection, .....

Actually it is not good to declare and instantiate ADO-related variables in
declaration part. It may lead to memory leak. You should split declaration
and instantiation, like

Dim myRes As ADODB.Recordset
Set myRes = New ADODB.Recordset

Signature

Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp

> Hi. I have a question. I would like to use paging. So I need a recordset
> that is either keyset or dynamic cursortype. I would like to use the
[quoted text clipped - 24 lines]
> Thanks for all your help,
> Avi

Reply to this Message

Al Reid - 29 Aug 2003 17:16 GMT

I'm not sure with regards to your question about using the Command object (I
use it all of the time but, have never needed to be concerned about paging)
but if you do not work it out, you can continue to use your original code
except replace the single quote with two (2) single quotes:
strSearch = Replace(tlbContactTop.Tools("ID_SearchContact").Edit.Text,
"'", "''")

good luck!

Reply to this Message

VB Forum / Database Access / August 2003

ADO.COMMAND