CursorAdapter, Parameters sql command not working

I use cursoradapter builder to build a class and compile it to a datacls.exe
file.
I issue the command :

odatacls = NEWOBJECT( "mtn08", "Datacls", "Datacls.exe" )
odatacls.selectcmd = 'select * from mtn08 where code = ?lcmyvar'
lcmyvar="ST"
odatacls.cursorfill()

to query a parameters sql results, the Error message show as :

Microsoft OLE DB Provider for SQL Server : No Value given for one or more
required parameters.


How to pass the parameters into the query?

Villi
Mon Jun 28 03:00:42 CDT 2004

tsair wrote:

> odatacls = NEWOBJECT( "mtn08", "Datacls", "Datacls.exe" )
> odatacls.selectcmd = 'select * from mtn08 where code =
> ?lcmyvar'
> lcmyvar="ST"
> odatacls.cursorfill()

You have to put the value of lcMyVar in the statement, not
its name, so the code would look like this:

odatacls.selectcmd = 'select * from mtn08 where code = '+lcMyVar

Note: this is for character type variables and its the easier
case,
since with other data you gave to str() or dtoc() them.
Example for a date, ldMyDate

selectcmd = 'select * from mtn08 where date =
ctod('+dtoc(ldMyDate)+')'

HTH
Villi

tsair
Mon Jun 28 03:11:25 CDT 2004

Thank you for your reply,

The purporse of the parameters is use for when I change the value of
variable lcmyvar, then issue the command Cursorrefresh to query another set
of datas.

if i change the odatacls.selectcmd = 'select * from mtn08 where code =
'+lcMyVar, the next requery have to change again the the odatacls.selectcmd.


"Villi Bernaroli" <a@b.c> wrote in message
news:uImPEYOXEHA.3012@tk2msftngp13.phx.gbl...
> tsair wrote:
>
> > odatacls = NEWOBJECT( "mtn08", "Datacls", "Datacls.exe" )
> > odatacls.selectcmd = 'select * from mtn08 where code =
> > ?lcmyvar'
> > lcmyvar="ST"
> > odatacls.cursorfill()
>
> You have to put the value of lcMyVar in the statement, not
> its name, so the code would look like this:
>
> odatacls.selectcmd = 'select * from mtn08 where code = '+lcMyVar
>
> Note: this is for character type variables and its the easier
> case,
> since with other data you gave to str() or dtoc() them.
> Example for a date, ldMyDate
>
> selectcmd = 'select * from mtn08 where date =
> ctod('+dtoc(ldMyDate)+')'
>
> HTH
> Villi
>
>

tsair
Tue Jun 29 04:26:32 CDT 2004

Tank you for your reply,

I try PUBLIC lcMyvar, it still not working.

May be i call the odatacls = NEWOBJECT( "mtn08", "Datacls", "Datacls.exe" ),
the class is call from external EXE file, so my public variable cannot pass
to that EXE file.

Is that any way to pass the parameters into that EXE file so it can process
the ADO selectcmd function ?






"Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in message
news:0A1FE099-2413-4341-A0AB-017B26D1E141@microsoft.com...
> Have you declared the variable as public? I had to when I used the
?variable name in the selectcmd. Then when the variable changes I just
issue REQUERY and the date refreshes fine.
>
> "tsair" wrote:
>
> > Thank you for your reply,
> >
> > The purporse of the parameters is use for when I change the value of
> > variable lcmyvar, then issue the command Cursorrefresh to query another
set
> > of datas.
> >
> > if i change the odatacls.selectcmd = 'select * from mtn08 where code =
> > '+lcMyVar, the next requery have to change again the the
odatacls.selectcmd.
> >
> >
> > "Villi Bernaroli" <a@b.c> wrote in message
> > news:uImPEYOXEHA.3012@tk2msftngp13.phx.gbl...
> > > tsair wrote:
> > >
> > > > odatacls = NEWOBJECT( "mtn08", "Datacls", "Datacls.exe" )
> > > > odatacls.selectcmd = 'select * from mtn08 where code =
> > > > ?lcmyvar'
> > > > lcmyvar="ST"
> > > > odatacls.cursorfill()
> > >
> > > You have to put the value of lcMyVar in the statement, not
> > > its name, so the code would look like this:
> > >
> > > odatacls.selectcmd = 'select * from mtn08 where code = '+lcMyVar
> > >
> > > Note: this is for character type variables and its the easier
> > > case,
> > > since with other data you gave to str() or dtoc() them.
> > > Example for a date, ldMyDate
> > >
> > > selectcmd = 'select * from mtn08 where date =
> > > ctod('+dtoc(ldMyDate)+')'
> > >
> > > HTH
> > > Villi
> > >
> > >
> >
> >
> >

tsair
Tue Jun 29 20:08:31 CDT 2004

I found the solution, but don't know is this a good solution.

To pass the parameters into the cursoradapter class which is compile into
external EXE file like dataclas.exe, i have to create a adodb.command object
in the cursoradapter.

Add in a properties 'oadocommand' into the cursoradapter to hold the
ADODB.Command object

add code below after the loConnDataSource.Open

this.oadocommand = createobject( 'ADODB.Command' )
this.oadocommand.activeConnection = This.DataSource.ActiveConnection

The disadvantage coding above is when each time I re-Builder the
cursoradapter class, the coding above disapear.


"tsair" <tsair@myjaring.net> wrote in message
news:uM3FwsbXEHA.3596@tk2msftngp13.phx.gbl...
> Tank you for your reply,
>
> I try PUBLIC lcMyvar, it still not working.
>
> May be i call the odatacls = NEWOBJECT( "mtn08", "Datacls",
"Datacls.exe" ),
> the class is call from external EXE file, so my public variable cannot
pass
> to that EXE file.
>
> Is that any way to pass the parameters into that EXE file so it can
process
> the ADO selectcmd function ?
>
>
>
>
>
>
> "Bernie Beattie" <BernieBeattie@discussions.microsoft.com> wrote in
message
> news:0A1FE099-2413-4341-A0AB-017B26D1E141@microsoft.com...
> > Have you declared the variable as public? I had to when I used the
> ?variable name in the selectcmd. Then when the variable changes I just
> issue REQUERY and the date refreshes fine.
> >
> > "tsair" wrote:
> >
> > > Thank you for your reply,
> > >
> > > The purporse of the parameters is use for when I change the value of
> > > variable lcmyvar, then issue the command Cursorrefresh to query
another
> set
> > > of datas.
> > >
> > > if i change the odatacls.selectcmd = 'select * from mtn08 where code =
> > > '+lcMyVar, the next requery have to change again the the
> odatacls.selectcmd.
> > >
> > >
> > > "Villi Bernaroli" <a@b.c> wrote in message
> > > news:uImPEYOXEHA.3012@tk2msftngp13.phx.gbl...
> > > > tsair wrote:
> > > >
> > > > > odatacls = NEWOBJECT( "mtn08", "Datacls", "Datacls.exe" )
> > > > > odatacls.selectcmd = 'select * from mtn08 where code =
> > > > > ?lcmyvar'
> > > > > lcmyvar="ST"
> > > > > odatacls.cursorfill()
> > > >
> > > > You have to put the value of lcMyVar in the statement, not
> > > > its name, so the code would look like this:
> > > >
> > > > odatacls.selectcmd = 'select * from mtn08 where code = '+lcMyVar
> > > >
> > > > Note: this is for character type variables and its the easier
> > > > case,
> > > > since with other data you gave to str() or dtoc() them.
> > > > Example for a date, ldMyDate
> > > >
> > > > selectcmd = 'select * from mtn08 where date =
> > > > ctod('+dtoc(ldMyDate)+')'
> > > >
> > > > HTH
> > > > Villi
> > > >
> > > >
> > >
> > >
> > >
>
>

Mark
Wed Jun 30 10:33:22 CDT 2004

You can use paramters using CA and ADO. I see you found a solution. You
might want to look at my free CA Builder class that will create a CA in a
VCX for every table in your SQL DB. I also have sample code for CAs also
that may help you with CA and ADO. Just click the FoxPro Tools link at

http://www.mctweedle.com

and download the 2 CA zip files. Read the extensive comments near the top of
the CABuilder.PRG file.

"tsair" <tsair@myjaring.net> wrote in message
news:e8g4jCLXEHA.2844@TK2MSFTNGP12.phx.gbl...
> I use cursoradapter builder to build a class and compile it to a
datacls.exe
> file.
> I issue the command :
>
> odatacls = NEWOBJECT( "mtn08", "Datacls", "Datacls.exe" )
> odatacls.selectcmd = 'select * from mtn08 where code = ?lcmyvar'
> lcmyvar="ST"
> odatacls.cursorfill()
>
> to query a parameters sql results, the Error message show as :
>
> Microsoft OLE DB Provider for SQL Server : No Value given for one or more
> required parameters.
>
>
> How to pass the parameters into the query?
>
>