rs.Update Detail - How to Get? (continued)

[Sware]

It looks my inclusion of the "too-wide" FoxPro Trace data and my attempts to expannd the Window have screwed up the forum page preentation with the effect that the Reply section is unusable (what else is going to go badly with this thread?!) I'm starting the new thread to continue the discussion and hope that the previous contributors can find it without the usual email trigger. You'll obviously have to refer to the old thread for the full story.

To reply to Andy's last post in the old thread:

I guess I'm confused by your comments. The trace data contains the SQL statement generated from by the FoxPro driver from the (ADO) rs1.Update statement in my VB code. Note that the trace shows an UPDATE statement and it DOES contain the field names (e.g. hand_calc, index_calc, etc.), along with the SET "command" that establishes the Values for the fields.

Also, as I understand ADO, the INSERT INTO command ADDS a record to a table; the UPDATE command MODIFIES an existing record in a table. It is the latter that I want to do!

Thus, I don't understand your focus on INSRT INTO. I am going to use Debug.Print to look at the generated SQL statement from a rs.Update statement that works on a different table. I'll post what I see versus the table we've been discussing.

 

[ArtieChoke]

A few things:

In this code fragment:

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

You are actually updating 88 fields. How many fields are in your table?

I could not find a way to see the update query (and yes an update query does get created) in ADO so I guess you'll need to do the trace. Note that the update query needs a where statement or it will update the entire database. If you don't have a primary key, then it will use whatever fields you update or all the fields in the table - I'm not sure which.

I think some of the people are asking you to create your own update statement so you have control of what's going out through ADO. The insert was just an example of how to construct one.

How do you get the initial record to update?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[dilettante]

It seems that the FoxPro interfaces for ADO do indeed have to build a SQL statement. Who'd a thunk it?

Do these apply?

SQL Statement too long using FoxOleDB

"Statement too long" error <-- no help here yet.

Howto update FoxPro DBF or DBC via ADO.NET?

TABLEUPDATE( ) Function <-- search page for "Statement too long

 

[Sware]

Artie, thanks for your response. To address your points:

The actual VB code is: for i = 0 to 86. The 0 to 87 you saw was from the example of one of the thread responders. There are 87 fields in the record. The complete actual VB code is:

For i = 0 to 86
If rs1.Fields(i) <> PlyrArray(i) Then
rs1.Fields(i) = PlyrArray(i)
End If
Next i
rs1.Update

Thus, only fields that have been changed in the processing of PlyrArray are changed in the recordset.

I have done the FoxPro Trace (the inclusion of which in one of my replies messed up at least my system's presentation of the original thread and my ability to Reply). The WHERE clause includes all 87 fields. The SET section before the WHERE includes 27 of the 87 fields, at least some of which do not seem to be fields where data has been changed (?). I do not have a Primary Key but duplicate records are not possible because one of the fields is an ID that is unique for each record.

I get the initial record to update as follows:

rs1.CursorLocation = adUseClient
rs1.Open "SELECT * FROM GHM_V70.DBF", conni, adOpenStatic,
adLockOptimistic

The conn1 connection string is:

conn1.ConnectionString = "Provider=MSDASQL;
Driver=(Microsoft Visual FoxPro Driver);
Extended Properties="";
SourceDB=" & App.Path & "\;
SourceType=DBF;
Deleted=Yes;"";"

The connection and recordset seem to be okay because I can use the recordset fields to populate VB forms on which users view the data and take actions that lead to processing. I initially populate the PlyrArray array from the recordset created by rs1.Open and reflect the results of the processing in the array. The problem occurs when I move changed data from the array to the corresponding recordset fields and then do rs1.Update. (By the way, I have verified that the data type (String) and length of all the array elements and their corresponding recordset fields are the same.)

What is mentioned above about 27 of the 87 fields appearing in the SET section of the traced UPDATE statement and that some of them don't seem to be fields that would have changed in the processing, leads me to wonder if my processing code is screwed up. I'm going to investigate that further.

In the meantime your additional comments and suggestions are welcome. I will also appreciate input from anyone who can help me fix the display situation in the original thread.

 

[Sware]

Whoa! I've done some additional observing and testing with the following result:

-- For databases other than the one where the "Statement Too Long" error occurs, and where I place data in the recordset fields directly from individual string variables [e.g. rs2.Fields(3) = "P"] versus from the PlyrArray array as I do with the rs1 recordset, the Update statement (e.g. rs2.Update) succeeds without error and the database is updated correctly.

Thus, it seems that there might be something afoul in the relationship between the rs1 recordset and PlyrArray. As mentioned in my previous post, I've verified that both the data type (String) and length of all of the recordset fields and the corresponding array elements are the same.

On the other hand, the number of fields in the database records where rs.Update succeeds is FAR LESS than the 87 fields in the database where the "Statement Too Long" error occurs. I tried to split up the updates to handle just a few of the fields that I know change in processing (e.g. For i = 84 to 86) but the error still occurs.

Any more ideas?

 

[ArtieChoke]

Try making your id field a primary key. That way the where clause will be very small. Maybe that's where you are running into problems.

Or generate your own update statement using the id field in the where clause. It's kind of a pain since using a recordset makes it so much easier.

I can't imagine why it would be any different between using string literals vs. your array. Is the array defined as string? Also, are all the fields in the table string or is there some conversion taking place?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Sware]

Artie,

Thanks again for your response. First, to answer your questions: yes, the array is defined as String; and yes, all the fields in the table are String.

Now, for what I think will be the "home run" on the problem -- a Primary Key. As you say, it will limit the WHERE clause versus the present situation where the clause contains all 87 fields. I thought I was using a Primary Key but the trace shows that its establishment failed because of a syntax problem. Here's how I try to do it:

conn1.Execute "ALTER TABLE GHM_V70_DBF ADD CONSTRAINT _
GHM_V70.DBF_PK PRIMARY KEY GS_ID"

This statement is part of the following function:

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

I call the function as follows:

If SetKey1(conn1, "GHM_V70.DBF", "GS_ID") = False Then

What follows the above statement is a MsgBox that reports the error but, based on something I read awhile back (obviously bad advice), I "masked" the error. When I now unmask it I get the error message.

So, I don't have a Primary Key. Can you (or someone) tell me what's wrong with how I try to create it?

As you say, I could use my own UPDATE statement with GS_ID as the WHERE clause. But, as you also say, using rs1.Update is so much easier.

 

[ArtieChoke]

The alter statement should look something like this:

ALTER TABLE GHM_V70_DBF ADD PRIMARY KEY (gs_id)

Note the () around the field name. Also the field can't be defined as allowing nulls.

What's the error message you get. If your gs_id isn't unique you will get an error. If you already have a primary key you will get an error.

I take it you don't have a fox-pro front end to create the primary key with?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Sware]

Artie,

I had to put quotemarks around what you suggested to get VB to accept the statement. That is:

conn1.Execute "ALTER TABLE GHM_V70.DBF ADD PRIMARY KEY _(GS_ID)"

The error I get is -214721700 Syntax Error from the Visual FoxPro Driver.

As you assume, I do NOT have a Visual FoxPro front end.

 

[ArtieChoke]

I was just showing it's default format - yep, for VB you gotta use quotes to make it a string. What's the "_" doing in there after key or is that just a typo? Same for the table name with the period.

I just checked some foxpro reference and it looks like you don't need the () around the field name - doh!

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[ArtieChoke]

One other thing. How are you getting the error message? You should check the error collection of the connection object. It will have more information. There can be multiple error messages so you'll need to print them all.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Sware]

Artie,
The "_" after KEY is a typo. The "_" in GHM_V70 is there because GHM_V70.DBF is the name of the database (table).

I tried the command without the () around the field name - I still get the syntax error.

I get the error via an ON ERROR GOTO in which I printed the Err.Number and the Err.Description. I did not do the whole error collection. I will do that but I'm going to away for awhile (or my wife will leave me!).

 

[ArtieChoke]

You should be using the name of the table, not the database. You should be connected to the database.

Okay, see you later - don't want the wife leavin' on my head! ;-)

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Sware]

Artie,
I'm back (and so is my wife!).

There's only one error in the conn1 Error Collection:
Number: 2147217900
Description: [ODBC Visual FoxPro Driver]:Syntax Error
Source: Microsoft OLE DB Provider for ODBC Drivers

So, it appears that the syntax of the conn1.Execute "ALTER TABLE........." statement is causing the error.

I guess we're confused terminology-wise. I'm not sure what our respective understandings are of what a "table" is in the context of ADO. As I understand things:

Connection: a connection to a data source, e.g. DBF files. Its creation includes definition of the Provider (e.g. MSDASQL) and the Driver (e.g. Microsoft Visual FoxPro Driver).

Recordset: a set of records from a "database" (quote marks for emphasis) that is created from a SELECT statement that references the Connection.

In the book 'Visual Basic 6 Database Programming' it says that SELECT statements are used to retrieve records in a "databse". It then presents the following form for a basic SELECT statement:

SELECT fields FROM table

Thus, I conclude that database and table are the same thing. And GHM_V70.DBF is ALL OF a physical file, a databsase, and a table. When I use rs1.Open "SELECT * FROM GHM_V70.DBF, conn1,......." I get a recordset of the content of GHM_V70.DBF and I can successfully use its fields to populate VB forms. I just can't update it, apparently because of the large number of fields and the absence of a Primary Key.

Anyhow, the central problem is how to create a Primary Key on whatever (I assume the recordset). I got the code that I use (as shown in a previous post) some time ago from a source that I can't remember. It obviously conveys that the the Primary Key is established via a conn1.Execute statement that includes specification of a "table" that I specify as GHM_V70.DBF. I do know that there's no Execute command for recordsets; so it appears that conn1.Execute "ALTER TABLE........." is the way to go if I can get the syntax right.

I sincerely hope that you, or somebody, can resolve this, and soon. I'm about to give up and abandon ADO's rs1.Update statement and do the laborious Update statement where I SET the value of all87 fields and include only GS_ID in the WHERE clause.

 

[ArtieChoke]

Thus, I conclude that database and table are the same thing.

If you only have one table, then that could be the case. A database consists of tables and other objects. It's the container. You don't access the database directly - only the objects - and tables make up the main objects since they contain the data.

Not having used Foxpro programmatically, I'm not familiar with it's syntax for tables, etc. Just making sure - I know they have an extension for the database name, since it's a file, but I don't know how table names are constructed. It sounds like each table is represented in it's own file. This is not usually the case in larger databases.

Try dropping the ".dbf" from the table name and see if that works. Grasping at straws time - doh!

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[johnwm]

This problem still looks very Foxpro specific - have you tried one of the many Foxpro forums on the site? From what you're saying about tables and databases in Foxpro there must be some pretty big differences between Foxpro and normal databases. Even if a normal database only has one table you can't reference the database as a table, or vice versa.

___________________________________________________________
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]

I've gotten a clarification of the database/table situation from another source. Artie is correct - there is a difference! And, it makes a difference with respect to my application.

In FoxPro itself there is a .DBC (Container) file that is the "database". The .DBF file(s) are "tables" within the database. The net of the Primary Key situation is that Primary Keysyou can only set a Primary Key on a table that is in a database!

My .DBF files are from a Clipper application and are considered "free tables" (i.e. not part of a container/database). Therefore, a Primary Key cannot be set.

It's been a long path to get to this understanding and I apologize for dragging you along the path. The good news is that there are two alternatives: a direct Update statement instead of ADO's rs.Update and something called a Candidate Key. I'll pursue both of these and post the result when the "Statement Too Long" error is resolved.

Thanks again.