>Hi
>
>Does anyone have some example code of using ADO to connect to SQL Server 2005 - I'm in a rush to complete some code (are we always), so something concise with example code would be brilliant.
>
>In essense, I want to retrieve a cursor from SQL Server 2005 (using a stored procedure within SQL Server), and then have a fully updatable cursor that's controlled by SQL Server 2005 but viewable/modifiable within VFP. I need to get to this point fairly quickly, hence asking for demo code by someone that's already done it and can easily demonstrate how to get the data, set the buffering properties, then commit the changes from within VFP.
>
>Regards
Derek,
Sorry didn't read the whole thread and might be parroting what has already been said. Here is a sample using a generic class (normally I use non-generic, well defined ones myself but we always need a generic one too for different purposes:)
Local lcConStr As String, loTable As CaGeneric
lcConStr = 'Provider=SQLNCLI10;server=.\SQL2008;Trusted_Connection=yes;Database=Northwind'
loTable = Createobject('CaGeneric',m.lcConStr)
loTable.Alias = 'Customers'
loTable.SelectCmd = 'select * from Customers'
loTable.QueryFill()
loTable.MakeUpdatable('Customers','CustomerID')
Browse
Tableupdate(2,.T.,'Customers')
Define Class CaGeneric As CursorAdapter
BatchUpdateCount = 100
WhereType = 1
MapVarchar = .T.
MapBinary = .T.
BufferModeOverride = 5
DataSourceType = 'ADO'
Procedure Init(tcConnectionString)
Set Multilocks On
Store This.DataSourceType To ;
this.InsertCmdDataSourceType, ;
this.UpdateCmdDataSourceType, ;
this.DeleteCmdDataSourceType
Local loConnDataSource
loConnDataSource = Createobject('ADODB.Connection')
loConnDataSource.ConnectionString = m.tcConnectionString
loConnDataSource.Open()
This.Datasource = Createobject('ADODB.RecordSet')
This.Datasource.CursorLocation = 3
This.Datasource.LockType = 3
This.Datasource.ActiveConnection = m.loConnDataSource
loCommand = Createobject('ADODB.Command')
loCommand.ActiveConnection = loConnDataSource
This.AddProperty('oCommand',loCommand)
This.UpdateCmdDataSource=loCommand
This.InsertCmdDataSource=loCommand
This.DeleteCmdDataSource=loCommand
Endproc
Procedure MakeUpdatable(tcTableName,tckeyField,tlDoNotIncludeKey)
This.Tables = m.tcTableName
This.KeyFieldList = m.tckeyField
Local ix
For ix = 1 To Fcount(This.Alias)-1
If !m.tlDoNotIncludeKey Or !(Upper(Field(m.ix,This.Alias,0)) == Upper(m.tckeyField))
This.UpdatableFieldList = This.UpdatableFieldList + ;
IIF(Empty(This.UpdatableFieldList),'',',') + ;
FIELD(m.ix,This.Alias,0)
Endif
This.UpdateNameList = This.UpdateNameList + ;
IIF(Empty(This.UpdateNameList),'',',') + ;
TEXTMERGE('<< FIELD(m.ix,this.Alias,0) >> << m.tcTableName >>.<< FIELD(m.ix,this.Alias,0) >>')
Endfor
Endproc
Procedure QueryFill()
Local llSuccess
llSuccess = This.CursorFill(.F.,.F.,0,This.oCommand)
If !m.llSuccess
Messagebox(This.GetErrorExplanation())
Endif
Return m.llSuccess
Endproc
Procedure GetErrorExplanation
Local lcError,ix
Local Array aWhy[1]
Aerror(aWhy)
lcError = ""
For ix = 1 To 7
lcError = m.lcError + Transform(aWhy[m.ix]) + Chr(13)
Endfor
Return m.lcError
Endproc
Enddefine
You can set Select/Update/Delete/InsertCmd to SPs.
Cetin