Not having used cursor adapters very much and wanting to try them as a short cut to make the conversion of a suite of programs into "Client server" I have the following Question.
I have a parent table which I can pick up in the cursoradapter modifying the "SelectCmd property and then issuing a CursorFill method no problem, the parent table refreshes itself OK and reflecs this in the form.
However, when I add a child table that is NOT a cursor adapter but a VFP table and then set up the relationship in the DE as you would with standard VFP tables using a dragged link between the parent and child, then when I "Refill" the parent table, the child doesn't reposition itself as per the relationship. Its just as though the relationship needs to be refreshed, but you can't refresh a relationship!!!
Any Ideas.... this is driving me mad and I wish I'd stayed with SPT throughout, but using adapters would save so much recoding...
Dave C
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BD031ECABF2B60499200AAB3DBB4A9997366B206@EX-A-FPL.FPL.LOCAL
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
It is a better way of doing things, to do your CA's in code and not in
the DE. You have greater control, especially if your select statement
involves more than one table. If you use the DE CA you would have to
change the select statement manually, because the wizard can only create
a select of just one table efficiently ( I mean, adding the joins properly)
Another thing you might consider, is not to use relations at all but sql
select statements to get the child table, based on the primary key of
the father table that has a correspondence in the child table as a
foreign key. IOW, get the primary key of the record in the father table,
then issue a select statement to get the child records based on that key
which should be the foreign key in the child.
ie nPK = fathertable.pkfield
select * from childtable where foreingkeyfield = nPK
So every time you refresh your CA (you can use requery(), just like a
view ), get the PK of the record you just obtained, and use a select
statement as above to get your child records.
BTW, your child could be another CA or just a non-CA cursor, as you
explained
The other thing that comes to mind is that the only way I find CA's
benefitial in a situation as the one you describe is when you need to
update the underlying table, otherwise, simple SPT statements would do
just fine.
Rafael Copquin
El 11/10/2011 12:41, Dave Crozier escribió:
> Not having used cursor adapters very much and wanting to try them as a short cut to make the conversion of a suite of programs into "Client server" I have the following Question.
>
> I have a parent table which I can pick up in the cursoradapter modifying the "SelectCmd property and then issuing a CursorFill method no problem, the parent table refreshes itself OK and reflecs this in the form.
>
> However, when I add a child table that is NOT a cursor adapter but a VFP table and then set up the relationship in the DE as you would with standard VFP tables using a dragged link between the parent and child, then when I "Refill" the parent table, the child doesn't reposition itself as per the relationship. Its just as though the relationship needs to be refreshed, but you can't refresh a relationship!!!
>
>
> Any Ideas.... this is driving me mad and I wish I'd stayed with SPT throughout, but using adapters would save so much recoding...
>
>
> Dave C
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E9469AE.5080300@fibertel.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Rafael,
Well that is the way I normally do things i.e entirely in code and using SPT but I thought I'd just take a dip in to see if it would save time.... Seemingly not, so I'll just recode everything using my normal Data Access Components...
Thanks for the reply. As they say, if we don't try these new things we'll never know how good or bad they are.
Dave
-----Original Message-----
From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf Of Rafael Copquin
Sent: 11 October 2011 17:07
To: profox@leafe.com
Subject: Re: Cursor Adapter in DE Question
It is a better way of doing things, to do your CA's in code and not in
the DE. You have greater control, especially if your select statement
involves more than one table. If you use the DE CA you would have to
change the select statement manually, because the wizard can only create
a select of just one table efficiently ( I mean, adding the joins properly)
Another thing you might consider, is not to use relations at all but sql
select statements to get the child table, based on the primary key of
the father table that has a correspondence in the child table as a
foreign key. IOW, get the primary key of the record in the father table,
then issue a select statement to get the child records based on that key
which should be the foreign key in the child.
ie nPK = fathertable.pkfield
select * from childtable where foreingkeyfield = nPK
So every time you refresh your CA (you can use requery(), just like a
view ), get the PK of the record you just obtained, and use a select
statement as above to get your child records.
BTW, your child could be another CA or just a non-CA cursor, as you
explained
The other thing that comes to mind is that the only way I find CA's
benefitial in a situation as the one you describe is when you need to
update the underlying table, otherwise, simple SPT statements would do
just fine.
Rafael Copquin
El 11/10/2011 12:41, Dave Crozier escribió:
> Not having used cursor adapters very much and wanting to try them as a short cut to make the conversion of a suite of programs into "Client server" I have the following Question.
>
> I have a parent table which I can pick up in the cursoradapter modifying the "SelectCmd property and then issuing a CursorFill method no problem, the parent table refreshes itself OK and reflecs this in the form.
>
> However, when I add a child table that is NOT a cursor adapter but a VFP table and then set up the relationship in the DE as you would with standard VFP tables using a dragged link between the parent and child, then when I "Refill" the parent table, the child doesn't reposition itself as per the relationship. Its just as though the relationship needs to be refreshed, but you can't refresh a relationship!!!
>
>
> Any Ideas.... this is driving me mad and I wish I'd stayed with SPT throughout, but using adapters would save so much recoding...
>
>
> Dave C
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BD031ECABF2B60499200AAB3DBB4A9997366B55E@EX-A-FPL.FPL.LOCAL
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Running the risk of overkill in this matter, I use CA's assiduously in
all my apps, but always in code, not the wizard. Even in client/server
situations, CA's are great, simply because you can insert, delete or
modify your CA cursor as any regular VFP cursor and then, with a simple
tableupdate you get the underlying tables updated. With SPT you have to
code with the slight modifications demanded by T-SQL, which in some
cases is different from VFP syntax. A bit of extra work and care.
I developed a series of classes that build CA's in code by simply
passing parameters to the class and coding is much faster that way.
In any case, you can use a combination of SPT and CA's in the same
routine and both will work just as well. I guess it is a matter of
choice or oportunity.
Rafael Copquin
El 12/10/2011 4:17, Dave Crozier escribió:
> Rafael,
> Well that is the way I normally do things i.e entirely in code and using SPT but I thought I'd just take a dip in to see if it would save time.... Seemingly not, so I'll just recode everything using my normal Data Access Components...
>
> Thanks for the reply. As they say, if we don't try these new things we'll never know how good or bad they are.
>
> Dave
>
>
> -----Original Message-----
> From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf Of Rafael Copquin
> Sent: 11 October 2011 17:07
> To: profox@leafe.com
> Subject: Re: Cursor Adapter in DE Question
>
> It is a better way of doing things, to do your CA's in code and not in
> the DE. You have greater control, especially if your select statement
> involves more than one table. If you use the DE CA you would have to
> change the select statement manually, because the wizard can only create
> a select of just one table efficiently ( I mean, adding the joins properly)
>
> Another thing you might consider, is not to use relations at all but sql
> select statements to get the child table, based on the primary key of
> the father table that has a correspondence in the child table as a
> foreign key. IOW, get the primary key of the record in the father table,
> then issue a select statement to get the child records based on that key
> which should be the foreign key in the child.
>
> ie nPK = fathertable.pkfield
>
> select * from childtable where foreingkeyfield = nPK
>
> So every time you refresh your CA (you can use requery(), just like a
> view ), get the PK of the record you just obtained, and use a select
> statement as above to get your child records.
>
> BTW, your child could be another CA or just a non-CA cursor, as you
> explained
>
> The other thing that comes to mind is that the only way I find CA's
> benefitial in a situation as the one you describe is when you need to
> update the underlying table, otherwise, simple SPT statements would do
> just fine.
>
> Rafael Copquin
>
>
> El 11/10/2011 12:41, Dave Crozier escribió:
>> Not having used cursor adapters very much and wanting to try them as a short cut to make the conversion of a suite of programs into "Client server" I have the following Question.
>>
>> I have a parent table which I can pick up in the cursoradapter modifying the "SelectCmd property and then issuing a CursorFill method no problem, the parent table refreshes itself OK and reflecs this in the form.
>>
>> However, when I add a child table that is NOT a cursor adapter but a VFP table and then set up the relationship in the DE as you would with standard VFP tables using a dragged link between the parent and child, then when I "Refill" the parent table, the child doesn't reposition itself as per the relationship. Its just as though the relationship needs to be refreshed, but you can't refresh a relationship!!!
>>
>>
>> Any Ideas.... this is driving me mad and I wish I'd stayed with SPT throughout, but using adapters would save so much recoding...
>>
>>
>> Dave C
>>
>>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E95A159.6080600@fibertel.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: MB Software Solutions, LLC
Posted: 2011-10-12 10:24:42 Link
On 10/12/2011 10:16 AM, Rafael Copquin wrote:
> Running the risk of overkill in this matter, I use CA's assiduously in
> all my apps, but always in code, not the wizard. Even in client/server
> situations, CA's are great, simply because you can insert, delete or
> modify your CA cursor as any regular VFP cursor and then, with a simple
> tableupdate you get the underlying tables updated. With SPT you have to
> code with the slight modifications demanded by T-SQL, which in some
> cases is different from VFP syntax. A bit of extra work and care.
>
> I developed a series of classes that build CA's in code by simply
> passing parameters to the class and coding is much faster that way.
>
> In any case, you can use a combination of SPT and CA's in the same
> routine and both will work just as well. I guess it is a matter of
> choice or oportunity.
I'll take a moment to again point out that Paul McNett's
MakeUpdatable.prg in the ProFox downloads section is a great utility for
making SPT cursors updatable easily.
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E95A32A.60901@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Very true. As a matter of fact it was you that pointed me to that prg
some time ago, which I incorporated into my classes. It is really a very
useful utility.
I wholeheartedly recommend it!
Rafael Copquin
>
> I'll take a moment to again point out that Paul McNett's
> MakeUpdatable.prg in the ProFox downloads section is a great utility for
> making SPT cursors updatable easily.
>
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E95A52F.5070605@fibertel.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Rafael,
Are you using Windows or SQL Server Authentication?
How are you connecting to the SQL Server data in the main form?
Currently I am trying out with the master data which seems to be working.
Can you send me an example of
building CA in code. Currently I have saved a class for each table that I am
working with.
Thanks
Ajoy Khaund
akhaund@hotmail.com
--------------------------------------------------
From: "Rafael Copquin" <rcopquin@fibertel.com.ar>
Sent: Wednesday, October 12, 2011 7:46 PM
To: <profoxtech@leafe.com>
Subject: Re: Cursor Adapter in DE Question
> Running the risk of overkill in this matter, I use CA's assiduously in
> all my apps, but always in code, not the wizard. Even in client/server
> situations, CA's are great, simply because you can insert, delete or
> modify your CA cursor as any regular VFP cursor and then, with a simple
> tableupdate you get the underlying tables updated. With SPT you have to
> code with the slight modifications demanded by T-SQL, which in some
> cases is different from VFP syntax. A bit of extra work and care.
>
> I developed a series of classes that build CA's in code by simply
> passing parameters to the class and coding is much faster that way.
>
> In any case, you can use a combination of SPT and CA's in the same
> routine and both will work just as well. I guess it is a matter of
> choice or oportunity.
>
> Rafael Copquin
>
>
> El 12/10/2011 4:17, Dave Crozier escribió:
>> Rafael,
>> Well that is the way I normally do things i.e entirely in code and using
>> SPT but I thought I'd just take a dip in to see if it would save time....
>> Seemingly not, so I'll just recode everything using my normal Data Access
>> Components...
>>
>> Thanks for the reply. As they say, if we don't try these new things we'll
>> never know how good or bad they are.
>>
>> Dave
>>
>>
>> -----Original Message-----
>> From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On
>> Behalf Of Rafael Copquin
>> Sent: 11 October 2011 17:07
>> To: profox@leafe.com
>> Subject: Re: Cursor Adapter in DE Question
>>
>> It is a better way of doing things, to do your CA's in code and not in
>> the DE. You have greater control, especially if your select statement
>> involves more than one table. If you use the DE CA you would have to
>> change the select statement manually, because the wizard can only create
>> a select of just one table efficiently ( I mean, adding the joins
>> properly)
>>
>> Another thing you might consider, is not to use relations at all but sql
>> select statements to get the child table, based on the primary key of
>> the father table that has a correspondence in the child table as a
>> foreign key. IOW, get the primary key of the record in the father table,
>> then issue a select statement to get the child records based on that key
>> which should be the foreign key in the child.
>>
>> ie nPK = fathertable.pkfield
>>
>> select * from childtable where foreingkeyfield = nPK
>>
>> So every time you refresh your CA (you can use requery(), just like a
>> view ), get the PK of the record you just obtained, and use a select
>> statement as above to get your child records.
>>
>> BTW, your child could be another CA or just a non-CA cursor, as you
>> explained
>>
>> The other thing that comes to mind is that the only way I find CA's
>> benefitial in a situation as the one you describe is when you need to
>> update the underlying table, otherwise, simple SPT statements would do
>> just fine.
>>
>> Rafael Copquin
>>
>>
>> El 11/10/2011 12:41, Dave Crozier escribió:
>>> Not having used cursor adapters very much and wanting to try them as a
>>> short cut to make the conversion of a suite of programs into "Client
>>> server" I have the following Question.
>>>
>>> I have a parent table which I can pick up in the cursoradapter modifying
>>> the "SelectCmd property and then issuing a CursorFill method no problem,
>>> the parent table refreshes itself OK and reflecs this in the form.
>>>
>>> However, when I add a child table that is NOT a cursor adapter but a VFP
>>> table and then set up the relationship in the DE as you would with
>>> standard VFP tables using a dragged link between the parent and child,
>>> then when I "Refill" the parent table, the child doesn't reposition
>>> itself as per the relationship. Its just as though the relationship
>>> needs to be refreshed, but you can't refresh a relationship!!!
>>>
>>>
>>> Any Ideas.... this is driving me mad and I wish I'd stayed with SPT
>>> throughout, but using adapters would save so much recoding...
>>>
>>>
>>> Dave C
>>>
>>>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/E3756158ACD146539FB50A18CE0570FC@ajoykcompaq
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Mark McCasland wrote a CA builder utility and there's a reference to it in the downloads section. Unfortunately, it appears to point to a dead link. I've found a version on foxite and will submit it to Ed.
--
rk
-----Original Message-----
From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Crozier
Sent: Wednesday, October 12, 2011 3:17 AM
To: profoxtech@leafe.com
Subject: RE: Cursor Adapter in DE Question
Thanks for the reply. As they say, if we don't try these new things we'll never know how good or bad they are.
Dave
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD04419F491FF03@ACKBWDDQH1.artfact.local
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
As requested, below are the methods necessary to create a cursor adapter
on the fly
use thus:
thisform.makeadapter('customers','curCustomers',[select * from customers])
As for authentication, I use SQL Server authentication
Connect through ODBC
local cConnString
cConnString=[DRIVER=SQL Server Native Client
10.0;SERVER=MAINSERVER\SQLEXPRESS;UID=sa;PWD=12345;APP=Microsoft Visual
FoxPro;WSID=INVOICINGPC;DATABASE=TESTDATABASE;LANGUAGE=English;]
thisform.nHandle = SQLStringConnect(cConnString )
BTW don't ever use the master data, you could screw things up badly.
Just create a test database, put some tables in it, fill them with data
and go on.
I strongly advise you to read the books from Hentzenwerke, Megafox....,
Internet Applications..... etc. That's where you get all the information
you need
Rafael Copquin
** makeadapter method
Lparameters tcTable,tcAlias,tcCmd
Local cSCH,cUFL,cUNL,cCmd
thisform.lOK = .T.
thisform.structure(tcTable)
cUFL = Thisform.cUFL && updatable field list
cUNL = Thisform.cUNL && update name list
cSCH = Thisform.cSCH && table schema
cCmd = tcCmd
thisform.lOK = .T.
Try
Use in Select(tcAlias)
If PemStatus(this,'oCA',5) = .T.
Removeproperty(Thisform,"oCA")
Endif
This.AddProperty("oCA")
This.oCA = Createobject("cursoradapter")
Catch To oErrors
Messagebox(" CursorAdapter object generation failed"+;
Chr(13)+oErrors.Message,16,"Attention",2000)
thisform.lOK = .F.
Finally
Endtry
If thisform.lOK = .F.
Return
Endif
If thisform.lOK = .T.
Try
With Thisform.oCA
.DataSourceType = "ODBC"
.DataSource = thisform.nHandle
.Alias = tcAlias
.Tables = tcTabla
.BufferModeOverride = 5
.KeyFieldList = thisform.cKeyField
.SendUpdates = .t.
.usetransactions = .f. && when set to .F., the CA does
not use transactions so you must set transactions to manual in the SQL
Server
.SelectCmd = cCmd
.UpdatableFieldList = cUFL
.UpdateNameList = cUNL
.CursorSchema = cSCH
.CursorFill()
Endwith
Select (thisform.oCA.alias)
Catch To oErrors
messagebox("Cursor generation
failed"+Chr(13)+oErrors.Message,16,'Attention')
thisform.lOK = .F.
Endtry
Endif
return
**************************************************
**structure method
lparameter cTable
Local cSch,cUNL,cUFL,cField,N
Store '' to cSch,cUNL,cUFL
Store 0 to nResult
thisform.cKeyField = thisform.getkeyfield(cTable)
** this is the VFP cursor structure generated when data from SQL Server
brought back
SQLColumns(thisform.nHandle,cTable,[FOXPRO],'curStructure')
Select curStructure
Scan all
cUNL = cUNL + field_name+[ ]+JustStem(cTable)+[.]+field_name+[,]
cUFL = cUFL + field_name+[,]
cSCH = cSCH + field_name+[ ]+field_type
Do case
Case InList(field_type,[C],[D],[I],[T])
cSCH = cSCH + [(] +Transform(field_len) +[)]
Case field_type = [N]
cSCH = cSCH + [(]
+Transform(field_len)+[,]+Transform(field_dec) +[)]
Case field_type = [M]
cSCH = cSCH + [(] +Transform(8000) +[)]
EndCase
cSCH = cSCH + [,]
EndScan
Use in Select('curStructure')
** remove comma from end of strings
thisform.cSCH = Substr(cSCH,1,Len(Alltrim(cSCH))-1) && schema
thisform.cUNL = Substr(cUNL,1,Len(Alltrim(cUNL))-1) && updatenamelist
thisform.cUFL = Substr(cUFL,1,Len(Alltrim(cUFL))-1) && updatablefieldlist
return
***********************************************************
**getkeyfield method
lparameters cTable
Local cField,N,cKeyField
Use in Select('curStructure')
** this allows you to know what is the key field directly from the SQL
Server data
SQLColumns(thisform.nHandle,cTable,[NATIVE],'curStructure')
Select curStructure
Locate for Lower(Alltrim(type_name)) ='int identity'
If Found()
cKeyField = Alltrim(column_name)
EndIf
Use in Select('curStructure')
Return cKeyField
El 13/10/2011 1:24, Ajoy Khaund escribió:
> Rafael,
>
> Are you using Windows or SQL Server Authentication?
> How are you connecting to the SQL Server data in the main form?
>
> Currently I am trying out with the master data which seems to be working.
> Can you send me an example of
> building CA in code. Currently I have saved a class for each table that I am
> working with.
>
> Thanks
>
> Ajoy Khaund
> akhaund@hotmail.com
>
>
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4E96D794.8010105@fibertel.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Thanks
Let me go through it and I will get back to you. It looks wonderful.
Ajoy Khaund
--------------------------------------------------
From: "Rafael Copquin" <rcopquin@fibertel.com.ar>
Sent: Thursday, October 13, 2011 5:50 PM
To: <profoxtech@leafe.com>
Subject: Re: Cursor Adapter in DE Question
> As requested, below are the methods necessary to create a cursor adapter
> on the fly
>
> use thus:
>
> thisform.makeadapter('customers','curCustomers',[select * from customers])
>
> As for authentication, I use SQL Server authentication
>
> Connect through ODBC
>
> local cConnString
>
> cConnString=[DRIVER=SQL Server Native Client
> 10.0;SERVER=MAINSERVER\SQLEXPRESS;UID=sa;PWD=12345;APP=Microsoft Visual
> FoxPro;WSID=INVOICINGPC;DATABASE=TESTDATABASE;LANGUAGE=English;]
>
> thisform.nHandle = SQLStringConnect(cConnString )
>
> BTW don't ever use the master data, you could screw things up badly.
> Just create a test database, put some tables in it, fill them with data
> and go on.
>
> I strongly advise you to read the books from Hentzenwerke, Megafox....,
> Internet Applications..... etc. That's where you get all the information
> you need
>
> Rafael Copquin
>
> ** makeadapter method
> Lparameters tcTable,tcAlias,tcCmd
>
> Local cSCH,cUFL,cUNL,cCmd
>
> thisform.lOK = .T.
>
> thisform.structure(tcTable)
>
> cUFL = Thisform.cUFL && updatable field list
> cUNL = Thisform.cUNL && update name list
> cSCH = Thisform.cSCH && table schema
> cCmd = tcCmd
>
> thisform.lOK = .T.
>
> Try
>
> Use in Select(tcAlias)
>
> If PemStatus(this,'oCA',5) = .T.
> Removeproperty(Thisform,"oCA")
> Endif
>
> This.AddProperty("oCA")
> This.oCA = Createobject("cursoradapter")
>
> Catch To oErrors
>
> Messagebox(" CursorAdapter object generation failed"+;
> Chr(13)+oErrors.Message,16,"Attention",2000)
>
> thisform.lOK = .F.
>
> Finally
> Endtry
>
> If thisform.lOK = .F.
> Return
> Endif
>
>
> If thisform.lOK = .T.
> Try
>
> With Thisform.oCA
> .DataSourceType = "ODBC"
> .DataSource = thisform.nHandle
> .Alias = tcAlias
> .Tables = tcTabla
> .BufferModeOverride = 5
> .KeyFieldList = thisform.cKeyField
> .SendUpdates = .t.
> .usetransactions = .f. && when set to .F., the CA does
> not use transactions so you must set transactions to manual in the SQL
> Server
> .SelectCmd = cCmd
> .UpdatableFieldList = cUFL
> .UpdateNameList = cUNL
> .CursorSchema = cSCH
> .CursorFill()
> Endwith
>
> Select (thisform.oCA.alias)
>
> Catch To oErrors
>
> messagebox("Cursor generation
> failed"+Chr(13)+oErrors.Message,16,'Attention')
>
> thisform.lOK = .F.
>
> Endtry
> Endif
>
> return
>
> **************************************************
> **structure method
>
> lparameter cTable
>
> Local cSch,cUNL,cUFL,cField,N
>
> Store '' to cSch,cUNL,cUFL
> Store 0 to nResult
>
> thisform.cKeyField = thisform.getkeyfield(cTable)
>
> ** this is the VFP cursor structure generated when data from SQL Server
> brought back
>
> SQLColumns(thisform.nHandle,cTable,[FOXPRO],'curStructure')
>
>
> Select curStructure
>
> Scan all
>
> cUNL = cUNL + field_name+[ ]+JustStem(cTable)+[.]+field_name+[,]
> cUFL = cUFL + field_name+[,]
>
> cSCH = cSCH + field_name+[ ]+field_type
>
> Do case
> Case InList(field_type,[C],[D],[I],[T])
> cSCH = cSCH + [(] +Transform(field_len) +[)]
> Case field_type = [N]
> cSCH = cSCH + [(]
> +Transform(field_len)+[,]+Transform(field_dec) +[)]
> Case field_type = [M]
> cSCH = cSCH + [(] +Transform(8000) +[)]
> EndCase
> cSCH = cSCH + [,]
>
>
> EndScan
> Use in Select('curStructure')
>
> ** remove comma from end of strings
>
> thisform.cSCH = Substr(cSCH,1,Len(Alltrim(cSCH))-1) && schema
> thisform.cUNL = Substr(cUNL,1,Len(Alltrim(cUNL))-1) && updatenamelist
> thisform.cUFL = Substr(cUFL,1,Len(Alltrim(cUFL))-1) && updatablefieldlist
>
> return
>
> ***********************************************************
> **getkeyfield method
>
> lparameters cTable
>
> Local cField,N,cKeyField
>
> Use in Select('curStructure')
>
> ** this allows you to know what is the key field directly from the SQL
> Server data
>
> SQLColumns(thisform.nHandle,cTable,[NATIVE],'curStructure')
>
> Select curStructure
>
>
> Locate for Lower(Alltrim(type_name)) ='int identity'
> If Found()
> cKeyField = Alltrim(column_name)
> EndIf
>
> Use in Select('curStructure')
>
> Return cKeyField
>
>
>
> El 13/10/2011 1:24, Ajoy Khaund escribió:
>> Rafael,
>>
>> Are you using Windows or SQL Server Authentication?
>> How are you connecting to the SQL Server data in the main form?
>>
>> Currently I am trying out with the master data which seems to be working.
>> Can you send me an example of
>> building CA in code. Currently I have saved a class for each table that I
>> am
>> working with.
>>
>> Thanks
>>
>> Ajoy Khaund
>> akhaund@hotmail.com
>>
>>
>>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/14D08AFE70494019AB8B01ABA6F78DB2@ajoykcompaq
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.