Wiki Home

Sql Problems


Namespace: WIN_COM_API
Yep. I missed something totaly stupd. kCashReceipt_pk is blank which is not a valid GUID. I figured it out after I wrote Clip Props.

Here is basically the code that caused this:
use v_CashReceipt nodata
scatter name loCashReceipt BLANK
loCashReceipt.kClient_fk = lkClient
loCashReceipt.cCheckNumber = something
loCashReceipt.rest of fields...
append blank  && kCashReceipt_pk's default fires and sets it to a GUID
gather name loCashReceipt && kCashReceipt_pk gets blanked out by the BLANK loCashReceipt.kCashReceipt_pk
if tableupdate() && ka boom.


Here is everything I looked at. When I have some time I will add in the steps and tools used.

tableupdate() returns .f.,
aError(x) returns 1
x[1,2] = "Connectivity error: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification"
x[2,4] = "22005"

Sql Profiler shows one line: set implicit_transactions on

values:
CCLIENTID C > <
CCLIENTNAME C > <
CCREDITEDACCOUNTCODE C >12 <
CDEBITEDACCOUNTCODE C >11 <
DTRANSACTIONDATE D >12/10/2003<
KBILLPAYITEM_FK C >A1F06F5C-9526-D811-A135-009027075830<
KCASHRECEIPT_FK C >8E9B5495-3904-1740-952D-0F2107171A05<
KCLIENT_FK C >BB9B705C-9526-D811-A135-009027075830<
KJOURNAL_PK C > <
NINVOICEITEMNO N >0<
NINVOICENO N >0<
NTRANSACTIONNO N >64455<
YTRANSACTIONAMOUNT N >25<

Here is the view def:

FUNCTION V_CASHRECEIPT
	CREATE SQL VIEW V_CASHRECEIPT REMOTE CONNECTION DBCCON AS ;
		select CashReceipt.kCashReceipt_pk, ;
				CashReceipt.kClient_fk, ;
				CashReceipt.nCashReceiptNo, ;
				CashReceipt.cCheckNumber, ;
				CashReceipt.dCheckDate, ;
				CashReceipt.dDepositDate, ;
				CashReceipt.yCheckAmount, ;
				0 as yApplied, ;
				0 as yUnapplied, ;
				0 as nInvoiceNo, ;
				cClientId, ;
				cClientName;
			FROM CashReceipt ;
				LEFT JOIN Client ;
					ON kClient_pk = kClient_fk
		
	* View Properties
	DBSetProp("V_CASHRECEIPT", 'View', 'UpdateType',1)
	DBSetProp("V_CASHRECEIPT", 'View', 'WhereType',3)
	DBSetProp("V_CASHRECEIPT", 'View', 'FetchMemo',.T.)
	DBSetProp("V_CASHRECEIPT", 'View', 'SendUpdates',.T.)
	DBSetProp("V_CASHRECEIPT", 'View', 'UseMemoSize',255)
	DBSetProp("V_CASHRECEIPT", 'View', 'FetchSize',100)
	DBSetProp("V_CASHRECEIPT", 'View', 'MaxRecords',-1)
	DBSetProp("V_CASHRECEIPT", 'View', 'Tables',[dbo.CashReceipt])
	DBSetProp("V_CASHRECEIPT", 'View', 'Comment',[])
	DBSetProp("V_CASHRECEIPT", 'View', 'BatchUpdateCount',1)
	DBSetProp("V_CASHRECEIPT", 'View', 'ShareConnection',.T.)
	DBSetProp("V_CASHRECEIPT", 'View', 'Prepared',.F.)
	DBSetProp("V_CASHRECEIPT", 'View', 'CompareMemo',.F.)
	DBSetProp("V_CASHRECEIPT", 'View', 'FetchAsNeeded',.F.)
	DBSetProp("V_CASHRECEIPT", 'View', 'RuleExpression',[])
	DBSetProp("V_CASHRECEIPT", 'View', 'RuleText',[])
	DBSetProp("V_CASHRECEIPT", 'View', 'ParameterList',[vp_nCashReceiptNo,'N';vp_cCheckNumber,'C';vp_cCheckNumber,'C';vp_yCheckAmount,'Y';vp_cClientId,'C'])
	
	* View Field Properties
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','KeyField',.T.)
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','Updatable',.T.)
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','UpdateName',[dbo.CashReceipt.kCashReceipt_pk])
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','DataType',[C(36)])
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','DefaultValue',[guid(36)])
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.kcashreceipt_pk",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','Updatable',.T.)
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','UpdateName',[dbo.CashReceipt.kClient_fk])
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','DataType',[C(36)])
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.kclient_fk",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','Updatable',.T.)
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','UpdateName',[dbo.CashReceipt.nCashReceiptNo])
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','DataType',[I])
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','DefaultValue',[nMkkey( "CR", "CashRecpt" )])
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.ncashreceiptno",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','Updatable',.T.)
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','UpdateName',[dbo.CashReceipt.cCheckNumber])
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','DataType',[C(10)])
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.cchecknumber",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','Updatable',.T.)
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','UpdateName',[dbo.CashReceipt.dCheckDate])
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','DataType',[D])
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','DefaultValue',[date()])
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.dcheckdate",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','Updatable',.T.)
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','UpdateName',[dbo.CashReceipt.dDepositDate])
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','DataType',[D])
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','DefaultValue',[date()])
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.ddepositdate",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','Updatable',.T.)
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','UpdateName',[dbo.CashReceipt.yCheckAmount])
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','DataType',[Y])
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.ycheckamount",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','Updatable',.F.)
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','UpdateName',[dbo.0])
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','DataType',[I])
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.yapplied",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','Updatable',.F.)
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','UpdateName',[dbo.0])
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','DataType',[I])
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.yunapplied",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','Updatable',.F.)
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','UpdateName',[dbo.invoice.ninvoiceno])
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','DataType',[I])
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.ninvoiceno",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','Updatable',.F.)
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','UpdateName',[dbo.Client.cClientId])
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','DataType',[C(10)])
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.cclientid",'Field','RuleText',[])
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','KeyField',.F.)
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','Updatable',.F.)
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','UpdateName',[dbo.Client.cClientName])
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','DataType',[C(40)])
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','Caption',[])
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','DefaultValue',[])
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','RuleExpression',[])
	DBSetProp("V_CASHRECEIPT.cclientname",'Field','RuleText',[])
	
ENDFUNC && V_CASHRECEIPT





SqlColumns() copy to foo type csv
table_cat,table_schem,table_name,column_name,data_type,type_name,column_size,buffer_length,decimal_digits,num_prec_radix,nullable,remarks,sql_data_type,sql_datetime_sub,char_octet_length,ordinal_position,is_nullable,ss_data_type
"pw","dbo","CashReceipt","kCashReceipt_pk",-11,"uniqueidentifier",36,16,0,0,0,"",-11,0,0,1,"NO",37
"pw","dbo","CashReceipt","kClient_fk",-11,"uniqueidentifier",36,16,0,0,1,"",-11,0,0,2,"YES",37
"pw","dbo","CashReceipt","nCashReceiptNo",4,"int",10,4,0,10,0,"",4,0,0,3,"NO",56
"pw","dbo","CashReceipt","cCheckNumber",1,"char",10,10,0,0,1,"",1,0,10,4,"YES",39
"pw","dbo","CashReceipt","dCheckDate",11,"datetime",23,16,3,0,1,"",9,3,0,5,"YES",111
"pw","dbo","CashReceipt","dDepositDate",11,"datetime",23,16,3,0,1,"",9,3,0,6,"YES",111
"pw","dbo","CashReceipt","yCheckAmount",3,"money",19,21,4,10,1,"",3,0,0,7,"YES",110

ODBC TRace
vfp8            854-85c	ENTER SQLBindParameter
		HSTMT               02A480A8
		UWORD                        1
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0
		PTR                0x04686EC0
		SQLLEN                     0
		SQLLEN *            0x03DC6080

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        1
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0 
		PTR                0x04686EC0
		SQLLEN                     0
		SQLLEN *            0x03DC6080 (36)

vfp8            854-85c	ENTER SQLBindParameter 
		HSTMT               02A480A8
		UWORD                        2 
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0 
		PTR                0x03DC6450
		SQLLEN                     0
		SQLLEN *            0x03DC60BC

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        2 
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0 
		PTR                0x03DC6450
		SQLLEN                     0
		SQLLEN *            0x03DC60BC (36)

vfp8            854-85c	ENTER SQLBindParameter 
		HSTMT               02A480A8
		UWORD                        3 
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0 
		PTR                0x03DC6488
		SQLLEN                     0
		SQLLEN *            0x03DC60F8

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        3 
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0 
		PTR                0x03DC6488
		SQLLEN                     0
		SQLLEN *            0x03DC60F8 (36)

vfp8            854-85c	ENTER SQLBindParameter 
		HSTMT               02A480A8
		UWORD                        4 
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0 
		PTR                0x03DC64C0
		SQLLEN                     0
		SQLLEN *            0x03DC6134

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        4 
		SWORD                        1 
		SWORD                        1 
		SWORD                      -11 
		SQLULEN                   36
		SWORD                        0 
		PTR                0x03DC64C0
		SQLLEN                     0
		SQLLEN *            0x03DC6134 (36)

vfp8            854-85c	ENTER SQLBindParameter 
		HSTMT               02A480A8
		UWORD                        5 
		SWORD                        1 
		SWORD                        4 
		SWORD                        4 
		SQLULEN                   10
		SWORD                        0 
		PTR                0x03DC618C
		SQLLEN                     0
		SQLLEN *            0x03DC6170

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        5 
		SWORD                        1 
		SWORD                        4 
		SWORD                        4 
		SQLULEN                   10
		SWORD                        0 
		PTR                0x03DC618C
		SQLLEN                     0
		SQLLEN *            0x03DC6170 (4)

vfp8            854-85c	ENTER SQLBindParameter
		HSTMT               02A480A8
		UWORD                        6 
		SWORD                        1 
		SWORD                       11 
		SWORD                       11 
		SQLULEN                   23
		SWORD                        3 
		PTR                0x03DC61BC
		SQLLEN                     0
		SQLLEN *            0x03DC61AC

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        6 
		SWORD                        1 
		SWORD                       11 
		SWORD                       11 
		SQLULEN                   23
		SWORD                        3 
		PTR                0x03DC61BC
		SQLLEN                     0
		SQLLEN *            0x03DC61AC (16)

vfp8            854-85c	ENTER SQLBindParameter 
		HSTMT               02A480A8
		UWORD                        7 
		SWORD                        1 
		SWORD                        1 
		SWORD                        3 
		SQLULEN                   19
		SWORD                        4 
		PTR                0x03DC61F8
		SQLLEN                    22
		SQLLEN *            0x03DC61E8

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        7 
		SWORD                        1 
		SWORD                        1 
		SWORD                        3 
		SQLULEN                   19
		SWORD                        4 
		PTR                0x03DC61F8
		SQLLEN                    22
		SQLLEN *            0x03DC61E8 (-3)

vfp8            854-85c	ENTER SQLBindParameter 
		HSTMT               02A480A8
		UWORD                        8 
		SWORD                        1 
		SWORD                        1 
		SWORD                        1 
		SQLULEN                    6
		SWORD                        0 
		PTR                0x04686EF8
		SQLLEN                     0
		SQLLEN *            0x03DC6224

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        8 
		SWORD                        1 
		SWORD                        1 
		SWORD                        1 
		SQLULEN                    6
		SWORD                        0 
		PTR                0x04686EF8
		SQLLEN                     0
		SQLLEN *            0x03DC6224 (6)

vfp8            854-85c	ENTER SQLBindParameter 
		HSTMT               02A480A8
		UWORD                        9 
		SWORD                        1 
		SWORD                        1 
		SWORD                        1 
		SQLULEN                    6
		SWORD                        0 
		PTR                0x03DC64F8
		SQLLEN                     0
		SQLLEN *            0x03DC6260

vfp8            854-85c	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
		UWORD                        9
		SWORD                        1 
		SWORD                        1 
		SWORD                        1 
		SQLULEN                    6
		SWORD                        0
		PTR                0x03DC64F8
		SQLLEN                     0
		SQLLEN *            0x03DC6260 (6)

vfp8            854-85c	ENTER SQLExecDirect
		HSTMT               02A480A8
		UCHAR *             0x03DC6510 [      -3] "INSERT INTO Journal (kJournal_pk,kClient_fk,kCashReceipt_fk,kBillPayItem_fk,nTransactionNo,dTransactionDate,yTransactionAmount,cDebitedAccountCode,cCreditedAccountCode) VALUES (?,?,?,?,?,?,?,?,?)\ 0"
		SDWORD                    -3

vfp8            854-85c	EXIT  SQLExecDirect  with return code -1 (SQL_ERROR)
		HSTMT               02A480A8
		UCHAR *             0x03DC6510 [      -3] "INSERT INTO Journal (kJournal_pk,kClient_fk,kCashReceipt_fk,kBillPayItem_fk,nTransactionNo,dTransactionDate,yTransactionAmount,cDebitedAccountCode,cCreditedAccountCode) VALUES (?,?,?,?,?,?,?,?,?)\ 0"
		SDWORD                    -3

		DIAG [22005] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (0)

vfp8            854-85c	ENTER SQLErrorW
		HENV                02A41540
		HDBC                02A415E8
		HSTMT               02A480A8
		WCHAR *             0x00124664 (NYI)
 		SDWORD *            0x00125714
		WCHAR *             0x00124264
		SWORD                      512
		SWORD *             0x00125718

vfp8            854-85c	EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
		HENV                02A41540
		HDBC                02A415E8
		HSTMT               02A480A8
		WCHAR *             0x00124664 (NYI)
 		SDWORD *            0x00125714 (0)
		WCHAR *             0x00124264 [      81] "[Microsoft][ODBC SQL Server Driver]Inval"
		SWORD                      512
		SWORD *             0x00125718 (81)

vfp8            854-85c	ENTER SQLErrorW
		HENV                02A41540
		HDBC                02A415E8
		HSTMT               02A480A8
		WCHAR *             0x00124664 (NYI)
 		SDWORD *            0x00125714
		WCHAR *             0x00124264
		SWORD                      512
		SWORD *             0x00125718

vfp8            854-85c	EXIT  SQLErrorW  with return code 100 (SQL_NO_DATA_FOUND)
		HENV                02A41540
		HDBC                02A415E8
		HSTMT               02A480A8
		WCHAR *             0x00124664 (NYI)
 		SDWORD *            0x00125714
		WCHAR *             0x00124264
		SWORD                      512
		SWORD *             0x00125718

vfp8            854-85c	ENTER SQLCancel
		HSTMT               02A480A8

vfp8            854-85c	EXIT  SQLCancel  with return code 0 (SQL_SUCCESS)
		HSTMT               02A480A8
( Topic last updated: 2003.12.10 05:51:56 PM )