Tableupdate:updates multiple rows, when only one row should be updated 3

[CMcC]

HI All.

Working in vfp9, with SQL backend. Creating remote views. When a customer record is changed, the fields that were selected for change (say name and zip) are being updated multiple times for different 'customers'. so when I change a zip code to '88888' for that one customer, it varies but normally around 100 rows are affected. This only seems to occur with more than 3000 records in the sql database.

I perform a "LOCATE" on the remote view (to locate that specific customer) and then issuing a tableupdate().

Ive tried throwing the data into a cursor and array, but stil the same results. Has anyone experienced this?

Thanks

 

[Olaf Doschke]

What are you executing? Tableupdate() can update a single row or multiple ones, for sure.

Also, if you have a view, in which zip is a joined field and have the foreign key of that joined table in the view, this updates the related child record and that can affect multiple joins, of course.

You've got to be more specific on your table schemas and view and the code you use, to say more.

Bye, Olaf.

 

[CMcC]

HI Olaf.
There are no joins.
Have a cursor that is built upon a 'whole' table for a lookup feature. Since I am working with remote views, I use this cursor to search within the whole result set. Once found in this cursor, I send to another form with a customer number, then close this particular cursor.

In that detail form, I select * from the live table where livetable.customer number = the customer number parameter into a one record cursor . This cursor populates the text boxes on this screen.
I verified that there is only one record in this cursor.

When a user selects to edit this customer, I send to another form (and have tried just editing from the detail form) passing in the customer number where I locate this customer number in the live table.

I do some replace statements, like name and a 'dummy' zip (so that I can see if multiple records are being updated), and perform a tableupdate().

I then look at the SQL server and multiple records have that dummy zip and there are always multiple records - with the names the same and the zip the same.

It appears to work just fine when the sql server table has under 3000 records. Anything over that I get multiple records.

If you need more detail, let me know!
Thanks

 

[mmerlinn]

CMcC

Without looking at the relevant code, Olaf will have a very hard time understanding exactly what is happening and when. Until he can examine your code, all he can do is guess, and usually guess wrong.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[CMcC]

lookup form - load event:

	SELECT  tname, notes, tstatus, lastupdate, cust_num;
		FROM npmaster; 
                INTO cursor grdname

**** this cursor is recordsource for the grid

lookup form - "Find Customer" button click

SELECT grdName
INDEX ON tname TO who + "tname"
	Set Order To who + "tname"
Set Exact Off
IF Seek(searchstring)
 Thisform.pageframe1.ActivePage = 2 
*!* this page has a grid that is bound the cursor above.
endif

Grid.text1.click event *** Takes to a detail form

DO FORM infotest WITH grdName.cust_num

Detail Form INFOTEST - init()

Lparameters custnum
SELECT * FROM npmaster WHERE npmaster.cust_num = custnum INTO CURSOR NPMAST   *** This is the one record cursor

EDIT CUSTOMER BUTTON:

DO FORM neweditcust WITH npmast.cust_num

NEWEDITCUST.init()

LPARAMETERS custnumber
thisform.custnum = custnumber
Select npmaster
Locate For npmaster.cust_num = custnumber
thisform.txtname.Value = npmaster.tname
thisform.txtzip.Value = npmaster.zip
Thisform.Refresh()
**** had these fields control sourced to the text boxes but changed to replace statements and still got the same results...multiple records updated.

EDITFORM Save Button Click:

SELECT npmaster
	Replace npmaster.tname With Thisform.txtname.Value
	Replace npmaster.zip With Thisform.txtzip.Value
	= Tableupdate(.T.)

In a test, I changed the zip code only to 12345. Did not change the name.
Multiple records have been changed - this test has 114 rows affected.

Thank you! Sorry if I didnt post the code.
Cmcc

 

[Olaf Doschke]

Still unclear:

View definitions. What is the keyfield
Are there relations set? npmaster suggests you have a master and slave/child table related 1:n

If I were you I'd not only look into what arrives at SQL Server, but look into npmaster and see via _TALLY how many records you updated in npmaster.

Also: If cust_num is a foreign key field and your view has set it up as the keyfield to reidentify the record, then you don't only update one, but all records with that foreign key. That could be the reason for multiple updates in the SQL Server table, while you have only replaced in one record of the view.

Tableupdate() does behind the scenes create an updatestatement, depending on what you set as updatablefieldlist, keyfieldlist, where type and some more, this can be something like:

Update sqlservertable set field = ?vfpcursor.field,... where cust_num = ?vfpcursor.custnum. And if the keyfieldlist does not contain a primary key field, but a foreign key field, this one update updates many records.

I need to know your table and view schema, what's primary key etc.
Please open the view designer and then in the View menu choose the item "View SQL". This schould display not only the vies SQL query, but also several DBSetProps, post all this code, please. That will tell us what the view does, how it updates the sql server table.

Bye, Olaf.

 

[CMcC]

There are no relations. There are no joins.
The primary key is the customer number which is 'cust_num'
This is the only table I am trying to update and the name 'npmaster' means it is the master customer table (not a master/child table).
We are not using SQL server in the traditional fashion.
We are in the process of converting our foxpro tables to SQL databases, so the tables within my database are not 'related' in any way.

I thought for sure, that I knew the customer number at the time the user selects to edit, that I could "Scatter" the npmast cursor and upon choosing to "save" the changes, just LOCATE the live record - GATHER - then perform a tableupdate.
I think Im going to try SQLEXEC or something like that, because I am still confused as to why it updates only one record if the live table contains UNDER 3000 records.

Thanks anyhow.

 

[TamarGranor]

I thought for sure, that I knew the customer number at the time the user selects to edit, that I could "Scatter" the npmast cursor and upon choosing to "save" the changes, just LOCATE the live record - GATHER - then perform a tableupdate.
I think Im going to try SQLEXEC or something like that, because I am still confused as to why it updates only one record if the live table contains UNDER 3000 records.

There's no reason to use SCATTER/GATHER in VFP. Just use buffering and bind controls right to the table's fields.

Back to the immediate problem. How is the view set up for updates? What's specified as the key field for the view? What update type are you using?

Tamar

 

[Olaf Doschke]

There is no reason to assume tableupdate fails at a certain size of a table. I do maintain tables with million records without problem.

Bye, Olaf.

 

[JRB-Bldr]

Working in vfp9, with SQL backend. Creating remote views

One thing I have found when using Update-able Remote Views is to look closely at the Update Criteria Key selection.

With the associated Database Open, from the Command Window do a MODIFY VIEW MySQLView and look at the Update Criteria tab page.

If, by chance, you have one or more Keys selected which are non-Unique to each individual record, when you do your TABLEUPDATE(), ALL of the records with the matching Key value(s) will update.

Make certain that the Update Criteria Key (or combination of Keys) is unique to each record.

Good Luck,
JRB-Bldr

 

[CMcC]

Thanks all for your input. The views are built dynamically. With that being said, I am not sure that I can 'open' the tab of the view? I was told that the view is an 'instance' of the table and any changes would reflect on the SQL table if a tableupdate is issued. (on buffering = 5).
Thanks for all your help.

*************************************************************************************
* Open_SQL_View returns the current work area
* dbf_FileName is the SQL Table to open
* sql_Conn is the Connection inside the FoxPro Project set to connect to SQL Server
* buffParm is the Buffering Setting inside the CURSORSETPROP() Function
* strOrderBy is how the data will be ordered in the select statement from FoxPro
**************************************************************************************
Procedure Open_SQL_View
Parameters dbf_FileName, sql_Conn, buffParm, strOrderBy

If Empty(strOrderBy)
	Create Sql View &dbf_FileName Connection &sql_Conn As;
		Select * From &dbf_FileName Where delflag = 0
Else
*	Create Sql View &dbf_FileName Connection &sql_Conn As;
*		Select * From &dbf_FileName Where delflag = 0 Order By &strOrderBy
	Create Sql View &dbf_FileName Connection &sql_Conn As;
		Select * From &dbf_FileName Where &strorderby AND delflag = 0 
		

Endif

Select 0
Use SQLNOTEDB!&dbf_FileName
*Use testlookup!&dbf_FileName
=Afields(fieldresult)
szKeyList=fieldresult[1,1]
szFieldList=""
szUpdateList=""
For i=1 To Alen(fieldresult,1)
	If Len(Alltrim(szFieldList))>0
		szFieldList=Alltrim(szFieldList)+","
	Endif
	szFieldList=szFieldList+Alltrim(fieldresult[i,1]) + ;
		" DBO." + Alltrim(dbf_FileName) + "."+Alltrim(fieldresult[i,1])
	If Len(Alltrim(szUpdateList))>0
		szUpdateList=Alltrim(szUpdateList)+","
	Endif
	szUpdateList=szUpdateList+Alltrim(fieldresult[i,1])
Next

=CursorSetProp("Buffering",buffParm,Alltrim(dbf_FileName))
=CursorSetProp("Tables",'dbo.' + Alltrim(dbf_FileName))
=CursorSetProp("UpdateNameList",szFieldList)
=CursorSetProp("KeyFieldList",szKeyList)
=CursorSetProp("UpdatableFieldList",szUpdateList)
=CursorSetProp("WhereType",1)
=CursorSetProp("SendUpdates",.T.)

 

[JRB-Bldr]

The views are built dynamically. With that being said, I am not sure that I can 'open' the tab of the view?

As long as the Remote View is built into a VFP Database and not Deleted - it will remain resident and you will be able to use the VFP Command Window to Open the Database and MODIFY VIEW ThisView

From your code I see that you are using the value in the variable szKeyList to contain the list of fields to use as your Remote View's Key(s).
And, again from your code, that value appears to be only the first field in the table ( szKeyList=fieldresult[1,1] )

Is the value of that field unique to each individual record in the table?
If not, then you can expect your TABLEUPDATES to operate on all of the multiple records with a matching Field1 value.

USE your remote view and just BROWSE the table contents.
Check if the Key field values are Unique.

Remember that the Key field(s) in the Remote View need to be a Unique field or combination of fields so that they uniquely identify each record (or multiple record set) on which an operation is to occur.

If this should be the situation, then you need to use more fields within your szKeyList so that their combined values will be unique to each individual record.

Who knows, this may not prove to be a problem at all, but it is certainly worth checking.

Good Luck,
JRB-Bldr

 

[CMcC]

HI JRB-Bldr.

That makes alot of sense now! I couldnt understand what the association is between the fields. The first field in this table is a 'last updated date' field. SO! updated date were being updated. I am going to move the field (just to test) and see if only one record gets updated. Cheers to all who have contributed.

Cmcc