rs.Update Detail - How to Get

[Sware]

With VB6 how can I get (see) the complete SQL statement generated when I update an ADO recordset? Thanks.

 

[johnwm]

strSQL = "Update mytable SET myField = " & Chr(34) & "NEW" & Chr(34) & " WHERE myID = 1"
[COLOR=red]Debug.Print strSQL[/color]
myConn.Execute (strSQL)

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Sware]

Thanks johnwm. Forgive my newness with ADO but I'm not sure how to fit my situation into the code you provided. I have a recordset named rs1 and a connection named conn1. The recordset has 87 fields which I update from an array before issuing the Update command. The code is:

For i = 0 to 87
rs1.Fields(i) = PlyrArray(i)
Next i
rs1.Update

Can you provide the "fit"? Thanks.

 

[strongm]

There is no SQL update statement issued under this scenario. Update here merely tells the system to commit the outstanding transactions.

 

[Sware]

Thanks strongm. But, does not the "system" internally generate SQL statements to implement rs1.Update statement?

 

[strongm]

No

 

[Sware]

Thanks again. The reason I am asking all this is because the rs1.Update statement is failing with the following error:

'-2147467259 (80004005)':
[Microsoft][Visual FoxPro Driver]SQL: Statement Too Long

 

[dilettante]

Some of the old ODBC drivers do some funky stuff. Have you considered the OLEDB Provider instead?

Microsoft OLE DB Provider for Visual FoxPro 9.0

I won't say this will cure the problem, and you may need an older version (though this one's probably fine).

 

[Sware]

Thanks dilettante. I have tried an older OLEDB driver. The same rs1.Update statement fails with a Multi-Step error.

 

[gmmastros]

Multi-Step error, in my experience, is caused by trying to store too much information in to a field.

I have no experience with FoxPro, but with Access and SQL Server, if you define a string field of length 10, and try to store a string that has a length > 10 characters, you will get this error. I recomend that you check the field length compared to the amount of data you are trying to store.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sware]

Thank you gmmastros - I think you have found the root cause of the problem, i.e. unequal lenghts of field versus data to be stored. I was just finishing a comparison for the 87 fields when you posted and I see four cases where the lengths are unequal. In all four cases the length of the data to be stored is LESS THAN the field length.

In all my searches in several forums I think I recall that the Visual FoxPro driver requires equal lengths (not greater OR less than) and that unequal lengths can cause the "SQL: Statement Too Long" error that I'm getting. (Of course, like the Multi-Step error, the error message and related documentation doesn't point to all the possible causes and is, in fact, misleading. But, I guess we should be used to that. Thank goodness for user forums.)

Now, hopefully, all I have to do is figure out how I screwed up the four cases of data to be stored. That's for tomorrow after a night's sleep.

Thanks again. I'll post the results tomorrow.

 

[Sware]

BLIND ALLEY! I have modified my processing code and verified that the length of all 87 array data elements used to update the recordset is the same as the length of the corresponding fields in the recordset. I have also used a VFP Trace to see the Update statement that FoxPro generates from the VB rs1.Update statement. The length of the generated Update statement is less than 1,800 bytes; so the FoxPro limit of 8,192 bytes is far from exceeded.

But, I'm still getting the "SQL: Statement Too Long" error. Any other ideas? Thanks.

 

[SBerthold]

What happens when you try the following?

Dim i2 As Integer
For i = 0 To 87
    rs1.Fields(i).Value = PlyrArray(i)
    i2 = i2 + 1
    If i2 = 20 Then
        rs1.Update
        i2 = 0
    End If
   
Next i
If i2 > 0 Then rs1.Update

My next question is: Do you have a primary key defined for the table to be updated, or can it be assured that now two rows/records are identical?

 

[Sware]

SBerthold - I get the "SQL: Statement Too Long" error with the code you suggest.

Apparently, I do not have a Primary Key established and it is possible for two rows in the recordset to be identical. I try to define a Primary Key with the following code.

--------------- Code --------------------------------
conn1.Open
If SetKey1(conn1, "GHM_V70.DBF", "GS_ID") = False Then
MsgBox "Primary Key Not Established..........."
conn1.Close
On Error GoTo 0
Exit Function
End If
' Continue with Open of Recordset if True

Public Function SetKey1(conn1 As ADODB.Connection _
TableName As String, PKField As String) As Boolean

On Error GoTo KeyError1
conn1.Execute "ALTER TABLE GHM_V70.DBF ADD CONSTRAINT _
GHM_V70.DBF_PK PRIMARY KEY GS_ID"
SetKey1 = True

FunctionExit:
Exit Function

KeyError1:
SetKey1 = False
Resume ExitFunction
--------------- End of Code --------------------------

I get the following error: -2147217900. The error description is [Microsoft][ODBC Visual FoxPro Driver]Syntax error.

Based on something I read a long time ago (I can't remember exactly what) I originally had "masked" this error number by setting SetKey1 = True when the error occurred (which possibly is why I got through to the rs1.Update statement and the "Statement Too Long" error). Without the masking I get the "Primary Key Not Established" message from the MsgBox statement in the above code.

So, the current question is why the -2147217900 syntax error when I try to set a Primary Key?

 

[SBerthold]

I do not know FoxPro's DLL syntax, as -2147217900 means a syntax error in the DDL command.
Try using "Create Index":

conn1.Execute "CREATE UNIQUE INDEX MyIndexName ON MyTable(MyUniqueField) WITH PRIMARY"

or ask in the FoxPro tek-tips forum.


>Apparently, I do not have a Primary Key established and it is possible for two rows in the recordset to be identical.

Then that is probably your main or first problem here. It cannot be determined which record needs to be updated unless there is something unique in each record, either a unique field, or if not, then the dbms may be trying to establish uniqueness using all fields together (in the Sql statement's WHERE clause, which is created when Update is called), which may be why the statement is too long.

 

[abaldwin]

One suggestion is to try and build the sql statement like John stated first.

something like this

dim stSql as string
stsql = "INSERT INTO MyTable (list of filed names) VALUE (

For i = 0 to 87
stsql = stsql & "'" & plyrarray(i) & "'",
next i
debug.print stsql

conn1.execute stsql

You will need to play with the stsql statement to get the right fields and all (I am typing this without the benefit of my ide or a db handy) but at least you will be able to build up a sql statement and see it before you execute the sql statement.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[Sware]

abaldwin - thanks. To all who have responded, I apologize for misleading you in one aspect of this problem. In the database that I'm working with it is NOT POSSIBLE to have identical rows/records. There's a GS_ID field that is unique for each row. So, I don't think I need a Primary Key (which I can't seem to create anyway).

Now for the latest input from abaldwin. First of all, I understand that INSERT INTO adds a NEW record. That's not what I want to do -- I want to MODIFY an EXISTING record. Nonetheless, I've tried abaldwin's code with some modifications as follows to avoid a syntax error and to reflect the fact that the 87 fields are identified from 0 to 86. FldNams is an array with the 86 field names.

Dim stSql as String
stSql = "INSERT INTO GHM_V70.DBF(FldNams) VALUE("
For i = 0 to 85
stSql = stSql & "'" & PlyrArray(i) & "'" & ","
Next i
stSql = stSql & PlyrArray(86) & Chr(34)
Debug.Print stSql
conn1.Execute stSql

The generated SQL statement appears to be correct and it contains 3891 bytes. However, I get the following error on the conn1.Execute statement: [Visual FoxPro Driver]Command contains unrecognized phrase/keyword

What next? By the way, I apologize but I still don't understand how to adapt John's UPDATE mytable SET..... code to the 86-fields situation.

 

[abaldwin]

Can you put a break in your code at the above debug.print statement.

Then under the immediate window copy and paste your sql statement here. We may be able to tell you the problem from there.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[jadams0173]

I'm by no means an expert but I'm going to give this one a shot since I get more help here than I give...please no snickers j/k

for i= Lbound(PlyrArray) to UBound(PlyrArray)
  
  sql="UPDATE YourTable Set SomeField='" PlyrArray(i) "'" _
  & "Where SomeOtherField='" & SomeValue & "'"

'You can use the debug statement to see the complete sql string.

Debug.Print sql
conn.execute sql, , 129

next i

I tried to have patience but it took to long! -DW

 

[Sware]

Andy, here you go:

INSERT INTO GHM_V70.DBF(FldNams) VALUE('01103','T',' ','P',' ','M',' ',' ',' ','AGOPOVICH, ARSENIO ',' 4',' 3',' 0',' 0',' 0.4',' 53.2','NH',' NH ',' NH ',' 5.1',' 8.4',' 8',' 7.3',' 76.5','NH',' NH ',' NH ','04/09/07',' 69 03/13/07, 1,72.2,129, -2.8,Y,Y, 69,T',' ',' N 81R N 84C N 82C N 84T N 81RUN 80RUN 78C N 81R N 85T N 84TUN 80RUN 80R N 82CUN 80T N 81TUN 74TUN 74TUN 74RUN 69TUN 70T',' ',' 6',' 5.1',' ','12/21/05, 8.4','08/17/06, 8.4','08/17/06, 7.5','01/04/07, 7.5','01/04/07, 7.5','03/14/07, 0.4','04/09/07, 0.4','04/09/07, 0.4',' ',' ',' ',' ','B 70 03/14/07, 1,72.2,129, -1.9,Y,Y, 70,T,A,N ','B 69 03/13/07, 1,72.2,129, -2.8,Y,Y, 69,T,A,N ','B 74 08/17/06, 1,72.2,129, 1.6,Y,Y, 74,R,A,N ','B 74 06/18/02, 2,70.2
,125, 3.4,Y,Y, 74,T,A,N ','B 74 06/18/02, 1,70.2,125, 3.4,Y,Y, 74,T,A,N ',' 81 09/21/97, 1,69.2,119, 11.2,Y,Y, 81,T,C,N ','U 80 07/22/97, 1,69.2,119, 10.3,Y,N, 80,T,A,N ','T 40 06/25/97, 5,34.6,119, 5.1,Y,N, 40,9,A,N ','T 40 06/25/97, 4,34.6,119, 5.1,Y,N, 40,9,A,N ',' 42 06/25/97, 3,34.6,119, 7.0,Y,N, ,9,A,N ','B 80 06/25/97, 2,69.2,119, 10.3,Y,N, 81,R,A,N ','B 80 06/25/97, 1,69.2,119, 10.3,Y,N, 80,R,A,N ',' 84 06/19/97,10,69.2,119, 14.1,Y,N, 84,T,A,N ',' 85 06/19/97, 9,69.2,119, 15.0,Y,Y, 85,T,A,N ','T 81 06/19/97, 8,69.2,119, 11.2,Y,Y, 81,R,A,N ','U 40 06/19/97, 7,34.6,119, 5.1,Y,Y, 40,9,A,N ','B 80 06/19/97, 6,69.2,119, 10.
3,Y,Y, 80,R,A,N ','T 81 06/19/97, 5,69.2,119, 11.2,Y,Y, 81,R,A,N ','U 38 06/19/97, 4,34.6,119, 3.2,Y,Y, 38,9,A,N ',' 84 06/19/97, 3,69.2,119, 14.1,Y,Y, 84,T,A,N ',' 41 06/19/97, 2,34.6,119, 6.1,Y,Y, 41,9,A,N ',' 41 06/19/97, 1,34.6,119, 6.1,Y,Y, 41,9,A,N ',' 43 06/18/97, 1,34.6,119, 8.0,Y,Y, 43,9,A,N ',' 81 06/17/97, 1,69.2,119, 11.2,Y,Y, 81,R,A,N ','T 79 06/16/97, 1,69.2,119, 9.3,Y,Y, 81,R,A,N ',' 82 06/15/97, 1,69.2,119, 12.2,Y,Y, 82,R,A,N ',' 94 11/12/96, 1,69.2,119, 23.5,Y,N, 94,R,A,N ',' 96 11/11/96, 1,69.2,119, 25.4,Y,N, 96,R,C,N ',' 95 11/10/96, 1,69.2,119, 24.5,Y,N, 95,R,A,N ',' 90 11/05/96, 1,69.2,119, 19.8,Y,N, 90
,R,A,N ',' 88 11/01/96, 1,69.2,119, 17.9,Y,N, 88,R,A,N ',' 41 10/22/96, 1,34.6,119, 6.1,Y,N, 41,9,A,N ',' 42 10/20/96, 1,34.6,119, 7.0,Y,N, 42,9,A,N ',' 43 10/01/96, 1,34.6,119, 8.0,Y,N, 43,9,A,N ',' 44 09/01/96, 1,34.6,113, 9.4,Y,N, 44,9,C,N ',' 45 08/30/96, 1,34.6,119, 9.9,Y,N, 45,9,A,N ',' 81 08/26/96, 1,69.2,119, 11.2,Y,N, 81,T,A,N ',' 91 06/17/96, 1,69.2,119, 20.7,Y,N, 91,T,A,N ',' 88 04/13/07, 1,72.2,129, 13.8,Y,N, 88,R,A,N ', "
-------------------------------------------------------

The fields/columns from 47 to 86 are 75-characters long. The last field (the 86th) is a blank field. Both the existing table and PlyrArray(86) consist of 86 Space characters.