"Statement too long" error

[steve....@teleware.co.uk]

New to this ADO stuff, I have been wrackining my brains to figure out
why I keep getting this error whenever I try to update a recordset. I
can retrieve the data OK but I cant update it!!!!!!!

-2147467259 (80004005)
[Microsoft][ODBC Visual Foxpro Driver]SQL:Statement too long

Anybody give me any ideas?

cheers

Steve

Sent via Deja.com http://www.deja.com/
Before you buy.

[chris petchey]

Perhaps your sql statement is too long

Give us a (break) clue,

under what circumstances does this happen? What is the code you are running?

Helpful questions get better answers

<steve....@teleware.co.uk> wrote in message
news:8fh3m8$vaq$1...@nnrp1.deja.com...

[Steve Collins]

> Perhaps your sql statement is too long
>
>
>
> Give us a (break) clue,
>
> under what circumstances does this happen? What is the code you are
running?
>
> Helpful questions get better answers
>
>

<SARCASM MODE ON>
oooohhh....we are a tetchy bunch arent we.......its lucky that doctors
havent got the same reasoning...."My back hurts doctor!"...."What arent you
going to give me any detail, what nerve in particular is trapped? Which
specific muscles are you using when it hurts.......when you lean
over!!....cmon man, you have to better than that if you want me to help
you!!!!"
<SARCASM MODE OFF>

ok..clientside cursor, batchoptimistic lock type, static cursortype
(although Ive tried dynamic, forward only)

If I do a standard "recordset.updatebatch" after amending a particular field
in the recordset I get a SQL statement too long. If I do "recordset.update"
it does not error but does not update the dbf ( ... i'm using Foxpro).

code used as follows:

DLL first, which handles the updates and connections

Private strSQL As String
Private strConnect As String
Dim ADOCn As ADODB.Connection

Public Function GetData() As ADODB.Recordset
If Not ADOCn Is Nothing Then
Else
Err.Raise vbObjectError + 98, "ADOGetData", "No valid Connection"
End If
Dim ADORs As ADODB.Recordset
Set ADORs = New ADODB.Recordset
With ADORs
.CursorLocation = adUseClient
.ActiveConnection = ADOCn
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic 'batch updates.
.Open strSQL
End With

'DisConnect the Recordset from the connection.
Set ADORs.ActiveConnection = Nothing
Set GetData = ADORs
Set ADORs = Nothing
End Function

Private Property Get ConnectStr() As String
ConnectStr = strConnect
End Property

Private Property Let ConnectStr(strCn As String)
strConnect = strCn
End Property

Public Property Get SQL() As String
SQL = strSQL
End Property

Public Property Let SQL(nSQL As String)
strSQL = nSQL
End Property

Public Sub UpdateRS(ByVal ClientRs As ADODB.Recordset)
Dim ADORs As New ADODB.Recordset
If Not ADOCn Is Nothing Then
Else
Err.Raise vbObjectError + 99, "ADOUpdate", "No valid Connection"
End If
ADORs.ActiveConnection = strConnect
ADORs.Open ClientRs
ADORs.Update

*also used ADORs.updatebatch which gives me the statement too long error

End Sub

Public Sub ADOConnect(strConnect As String, Optional CnTimeOut As _
Integer = 20)
Set ADOCn = New ADODB.Connection
With ADOCn
.Provider = "MSDASQL" '"SQLOLEDB"
.CursorLocation = adUseClient
.ConnectionString = strConnect
.CommandTimeout = CnTimeOut
.Open
End With
ConnectStr = ADOCn
End Sub

BUTTON CLICK OFF A TEST FORM

Private Sub Command1_Click()
On Error GoTo ErrorHandler

Dim ADORs As ADODB.Recordset
Dim objADOData As TeleADO.TeleData
Dim rField As ADODB.Field
Dim iValue As Byte

' Instantiate the Data Object.
Set objADOData = New TeleADO.TeleData
With objADOData
.SQL = Text3.Text
.ADOConnect strConnect, 15 'Establish connection.
End With

Set ADORs = New ADODB.Recordset
' Rtrn the Resultset from Data object.
Set ADORs = objADOData.GetData
' The Resultset is disConnected at this point.

'ADORs.AddNew

ADORs(Text5.Text) = Text6.Text
ADORs.MarshalOptions = adMarshalModifiedOnly
objADOData.UpdateRS ADORs
MsgBox "Data Changed", vbOKOnly, "Data Object"
Exit Sub

ErrorHandler:
MsgBox "Change Failed:" & vbCrLf & Err.Number & vbCrLf & _
Err.Description, vbOKOnly, "Data Object"
Debug.Print Err.Number & vbCrLf & Err.Description
Exit Sub
End Sub