Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

String or binary data would be truncated - VFP9

Status
Not open for further replies.
JimWWinter

JimWWinter

Programmer
Dec 6, 2004
217
US
Getting the message 'String or binary data would be truncated.' when I try to save an updated remote view back to SQL Server.

Only one field in the view is set to Updateable and that field is defined as decimal (15,2) in SQL Server. The view, as initially set in ViewEditor, had the field defined as N(17,2). I changed it to N(15,2) and still got the error. I also tried N(10,2) and, after Del Lee pointed out that SQL Server doesn't count the decimal in the length of the field, N(16,2). In all cases I get the same error and the table isn't updated.

I have also tried SET NUMERIC_ROUNDABORT OFF.

Can anyone suggest what else I should be checking?

TIA,
Jim
 
Sort by date Sort by votes
Did you try a 'convert(decimal(15,2),MyValue)'?

Brian
 
Upvote 0 Downvote

Jim,

One way to trouble-shoot this sort of issue is to try sending an UPDATE command directly to the server.

Something like this:

Code: 
lnConn = SQLCONNECT("MyConnectionObject")
SLQEXEC(lnConn, ;
  "UPDATE MyTable SET MyField = 123.45 WHERE ID = 1"

See if you get the same error when you do it that way. If not, the problem lies in the view definition. If you do get the error, try executing the UPDATE command directly in SQL Server's Query Analyzer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote
If I execute the following in Query Analyzer
Code: 
UPDATE dbo.tblInventoryPrice SET pri_Current_Price=10.47
WHERE own_Owner_Code        = 'PA'        AND
      inp_Short_Item_Number = 'BRUS-0016' AND
      prl_Price_List_Code   = 'R'         AND
      pri_term              = 1
it updates without complaint.

This is the view definition as per VFEGenView:
Code: 
***************** Begin View Definition V_PLAY_PRICING ***************


LOCAL LAOPENEDDBCS[1,1], lcSet_Exact
PRIVATE lWriteDBCXProps

lcSet_Exact=SET('EXACT')
IF lcSet_Exact='OFF'
	SET EXACT ON
ENDIF


***Make sure the current database is correct
IF ADATABASES(LAOPENEDDBCS)=0 or ASCAN(LAOPENEDDBCS,"V_PROADV")=0
	OPEN DATABASE "C:\VFEAPPS\PROADV\DATA\V_PROADV"
ELSE
	SET DATABASE TO "V_PROADV"
ENDIF


IF lcSet_Exact='OFF'
	SET EXACT OFF
ENDIF



CREATE SQL VIEW "V_PLAY_PRICING" ; 
 REMOTE CONNECT "INORDER_PLAY" ; 
	AS SELECT inv.inp_Vendor,;
		inv.inp_Long_Item_Name,;
		inv.inp_Long_Item_Number,;
		prc.own_Owner_Code,;
		prc.inp_Short_Item_Number,;
		prc.prl_Price_List_Code,;
		prc.pri_term,;
		prc.pri_Current_Price,;
		itm.vit_Sku,;
		brk.vpb_Vendor_Unit_Price,;
		ity.ity_Inventory_Type_Name;
	FROM dbo.tblInventoryPrice prc;
		JOIN dbo.tblInventoryPrimary inv;
			ON prc.inp_Short_Item_Number = inv.inp_Short_Item_Number;
		JOIN dbo.tblVendorItem itm;
			ON inv.inp_Short_Item_Number = itm.inp_Short_Item_Number AND;
				inv.inp_Vendor = itm.ven_Vendor_Name;
		JOIN dbo.tblVendorItemPriceBreak brk;
			ON itm.vit_Sku = brk.vit_Sku AND;
				itm.ven_Vendor_Name = brk.ven_Vendor_Name;
		JOIN dbo.tblInventoryType ity;
			ON inv.ity_Inventory_Type_Code = ity.ity_Inventory_Type_Code

DBSetProp('V_PLAY_PRICING', 'View', 'UpdateType', 1)
DBSetProp('V_PLAY_PRICING', 'View', 'WhereType', 1)
DBSetProp('V_PLAY_PRICING', 'View', 'FetchMemo', .T.)
DBSetProp('V_PLAY_PRICING', 'View', 'SendUpdates', .T.)
DBSetProp('V_PLAY_PRICING', 'View', 'UseMemoSize', 255)
DBSetProp('V_PLAY_PRICING', 'View', 'MaxRecords', -1)
DBSetProp('V_PLAY_PRICING', 'View', 'Tables', 'dbo.tblInventoryPrice')
DBSetProp('V_PLAY_PRICING', 'View', 'Prepared', .T.)
DBSetProp('V_PLAY_PRICING', 'View', 'CompareMemo', .T.)
DBSetProp('V_PLAY_PRICING', 'View', 'FetchAsNeeded', .T.)
DBSetProp('V_PLAY_PRICING', 'View', 'FetchSize', 100)
DBSetProp('V_PLAY_PRICING', 'View', 'ParameterList', "vp_Vendor,'C';vp_Type_Name,'C';vp_Long_Name,'C';vp_Long_Number,'C'")
DBSetProp('V_PLAY_PRICING', 'View', 'Comment', "")
DBSetProp('V_PLAY_PRICING', 'View', 'BatchUpdateCount', 1)
DBSetProp('V_PLAY_PRICING', 'View', 'ShareConnection', .T.)

*!* Field Level Properties for V_PLAY_PRICING
*** Props for the V_PLAY_PRICING.inp_vendor field.
DBSetProp('V_PLAY_PRICING.inp_vendor', 'Field', 'KeyField', .F.)
DBSetProp('V_PLAY_PRICING.inp_vendor', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.inp_vendor', 'Field', 'UpdateName', 'dbo.tblInventoryPrimary.inp_Vendor')
DBSetProp('V_PLAY_PRICING.inp_vendor', 'Field', 'DataType', "C(9)")
 
*** Props for the V_PLAY_PRICING.inp_long_item_name field.
DBSetProp('V_PLAY_PRICING.inp_long_item_name', 'Field', 'KeyField', .F.)
DBSetProp('V_PLAY_PRICING.inp_long_item_name', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.inp_long_item_name', 'Field', 'UpdateName', 'dbo.tblInventoryPrimary.inp_Long_Item_Name')
DBSetProp('V_PLAY_PRICING.inp_long_item_name', 'Field', 'DataType', "C(80)")
 
*** Props for the V_PLAY_PRICING.inp_long_item_number field.
DBSetProp('V_PLAY_PRICING.inp_long_item_number', 'Field', 'KeyField', .F.)
DBSetProp('V_PLAY_PRICING.inp_long_item_number', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.inp_long_item_number', 'Field', 'UpdateName', 'dbo.tblInventoryPrimary.inp_Long_Item_Number')
DBSetProp('V_PLAY_PRICING.inp_long_item_number', 'Field', 'DataType', "C(20)")
 
*** Props for the V_PLAY_PRICING.own_owner_code field.
DBSetProp('V_PLAY_PRICING.own_owner_code', 'Field', 'KeyField', .T.)
DBSetProp('V_PLAY_PRICING.own_owner_code', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.own_owner_code', 'Field', 'UpdateName', 'dbo.tblInventoryPrice.own_Owner_Code')
DBSetProp('V_PLAY_PRICING.own_owner_code', 'Field', 'DataType', "C(2)")
 
*** Props for the V_PLAY_PRICING.inp_short_item_number field.
DBSetProp('V_PLAY_PRICING.inp_short_item_number', 'Field', 'KeyField', .T.)
DBSetProp('V_PLAY_PRICING.inp_short_item_number', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.inp_short_item_number', 'Field', 'UpdateName', 'dbo.tblInventoryPrice.inp_Short_Item_Number')
DBSetProp('V_PLAY_PRICING.inp_short_item_number', 'Field', 'DataType', "C(10)")
 
*** Props for the V_PLAY_PRICING.prl_price_list_code field.
DBSetProp('V_PLAY_PRICING.prl_price_list_code', 'Field', 'KeyField', .T.)
DBSetProp('V_PLAY_PRICING.prl_price_list_code', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.prl_price_list_code', 'Field', 'UpdateName', 'dbo.tblInventoryPrice.prl_Price_List_Code')
DBSetProp('V_PLAY_PRICING.prl_price_list_code', 'Field', 'DataType', "C(1)")
 
*** Props for the V_PLAY_PRICING.pri_term field.
DBSetProp('V_PLAY_PRICING.pri_term', 'Field', 'KeyField', .T.)
DBSetProp('V_PLAY_PRICING.pri_term', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.pri_term', 'Field', 'UpdateName', 'dbo.tblInventoryPrice.pri_term')
DBSetProp('V_PLAY_PRICING.pri_term', 'Field', 'DataType', "I")
 
*** Props for the V_PLAY_PRICING.pri_current_price field.
DBSetProp('V_PLAY_PRICING.pri_current_price', 'Field', 'KeyField', .F.)
DBSetProp('V_PLAY_PRICING.pri_current_price', 'Field', 'Updatable', .T.)
DBSetProp('V_PLAY_PRICING.pri_current_price', 'Field', 'UpdateName', 'dbo.tblInventoryPrice.pri_Current_Price')
DBSetProp('V_PLAY_PRICING.pri_current_price', 'Field', 'DataType', "N(16,2)")
 
*** Props for the V_PLAY_PRICING.vit_sku field.
DBSetProp('V_PLAY_PRICING.vit_sku', 'Field', 'KeyField', .F.)
DBSetProp('V_PLAY_PRICING.vit_sku', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.vit_sku', 'Field', 'UpdateName', 'dbo.vit_Sku')
DBSetProp('V_PLAY_PRICING.vit_sku', 'Field', 'DataType', "C(20)")
 
*** Props for the V_PLAY_PRICING.vpb_vendor_unit_price field.
DBSetProp('V_PLAY_PRICING.vpb_vendor_unit_price', 'Field', 'KeyField', .F.)
DBSetProp('V_PLAY_PRICING.vpb_vendor_unit_price', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.vpb_vendor_unit_price', 'Field', 'UpdateName', 'dbo.vpb_Vendor_Unit_Price')
DBSetProp('V_PLAY_PRICING.vpb_vendor_unit_price', 'Field', 'DataType', "N(15,2)")
 
*** Props for the V_PLAY_PRICING.ity_inventory_type_name field.
DBSetProp('V_PLAY_PRICING.ity_inventory_type_name', 'Field', 'KeyField', .F.)
DBSetProp('V_PLAY_PRICING.ity_inventory_type_name', 'Field', 'Updatable', .F.)
DBSetProp('V_PLAY_PRICING.ity_inventory_type_name', 'Field', 'UpdateName', 'dbo.ity_Inventory_Type_Name')
DBSetProp('V_PLAY_PRICING.ity_inventory_type_name', 'Field', 'DataType', "C(20)")

Note that I'm only updating one field in one table, the fields from other tables are just there to provide meaningful information to personnel doing price updates.


Regards,
Jim
 
Upvote 0 Downvote
Jim,

The only thing that looks suspicious in your view is:

Code: 
DBSetProp('V_PLAY_PRICING', 'View', 'WhereType', 1)

I suggest you change it to:

Code: 
DBSetProp('V_PLAY_PRICING', 'View', 'WhereType', [b]3[/b])

(You can do that interactively in the View Designer by setting "SQL Where clause" to "Key and Modified Fields" in the Update Criteria page).

However, that doesn't explain the error you are seeing, although I think it might cause the update to fail.

That aside, the problem seems to be related to data types. You say the field is defined as Decimal in SQL Server. If you have control over the structure of the table, can you try changing it to, say, Money and see what effect that has.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote
Thanks for looking at it Mike. Unfortunately, changing it "Key and Modified Fields" didn't change the results.

As the SQL tables are part of a .Net app that we use, I can't change data types.

Interestingly, at someone elses suggestion, I've just rebuilt everything from scratch, starting with just one table in the view, testing it from the command line, and then adding another table, testing, etc., until I was back to the original view. From the command line they all work. It's just in the application that the TABLEUPDATE fails.

Regards,
Jim
 
Upvote 0 Downvote
Finally tracked it down! The problem was in my connection string. Changing 'UseTrustedConnection=yes' to 'UseTrustedConnection=no' fixed the problem and the view now updates the table as expected.

Regards,
Jim
 
Upvote 0 Downvote
Mike,

I don't really understand it all as I'm new to SQL Server, but BOb Archer had me poke around a little and I found there is an encrypted trigger on the table. He surmized that "they are storing the username in an audit table when the record is updated. They should be trimming the username so it does not cause this error."

In any event, it's working now so I'm feeling very relieved.

Regards,
Jim
 
Upvote 0 Downvote

Jim,

Oh, no. Not the "encrypted trigger" problem <g>.

I've had a couple of really weird SQL Server problems in the last couple of months, and both of them came down to the fact that the table has a trigger which I didn't know about.

I could kick myself for not thinking of that in your case. I must stick a yellow note to my monitor to remind me .... Any time you can't solve a SQL Server problem, check the triggers.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Vince Wen
  • Question
Better way to install VFP9+Innosetup application?
Replies
4
Views
299
Vince Wen
Vince Wen
Steve-vfp9user
  • Locked
  • Question
WhatsAPP and VFP9 SP2 2
Replies
4
Views
576
Paul Gibson
Paul Gibson
Scott24x7
  • Locked
  • Question
VFP9 and OpenAI 1
Replies
5
Views
1K
Scott24x7
Scott24x7
ggreen61
  • Locked
  • Question
NAS for Shared Files Access via VFP9
Replies
3
Views
472
Drainie
Drainie
BugZap13
  • Locked
  • Question
Quickbooks QBFC16 SDK 64 bit usage from VFP9/VFPA
Replies
1
Views
328
GTGeek88
GTGeek88

Part and Inventory Search

Sponsor

Back
Top