Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Problem in query

2 posts in General Discussion Last posting was on 2006-05-05 15:21:15.0Z
john Posted on 2006-05-05 02:00:03.0Z
From: john <johndalde@biz.phisl.net>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Problem in query
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: 203.215.95.78
Message-ID: <445ab1af@forums-2-dub>
X-Original-Trace: 4 May 2006 19:00:15 -0700, 203.215.95.78
Lines: 554
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 May 2006 19:00:18 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 May 2006 19:00:03 -0700
X-Trace: forums-1-dub 1146794403 10.22.108.75 (4 May 2006 19:00:03 -0700)
X-Original-Trace: 4 May 2006 19:00:03 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5320
Article PK: 1373

Hi, hope everybody is fine, i have a problem in my action page. i really
had a hard time tracing this problem. this problem is about generating
quotation number using autogenerate function. so before the quotation
number will be generated it will get first the customers accountsID.
then generate the quotation number. but what happen is when
customeraccountsId is found it will return an error message that
customeraccounts is undefined in getcustAcctID but customeraccounts is a
table.When the customeraccountsID is not found it will return an error
message that " no primary key value for quotation is table
quotation_detail.
here is my page for further references:

please help me....


<cfset lcerror=''>
<cfset sysError=''>
<cfif cgi.REQUEST_METHOD EQ 'POST'>
<cfquery name="qry_Config_QuotationAuto" datasource="#dsn#">
Select configvalue from configuration where configname = 'QuotationAuto'
</cfquery>
<!--- get customeraccountsID --->

<cfquery name="getCustAcctID" datasource="#dsn#">
select customerAccountsID from customerAccounts where customercode =
'#form.customercode#'
</cfquery>

<!--- check if the customer has accounts, if the customer has no account
it will use the default 9999 --->

<cfif getCustAcctID.RecordCount eq 0 OR getCustAcctID.customerAccounts
eq ''>

<cfquery name="qry_Next_quotation_No" datasource="#dsn#">
begin
declare custAcctID integer;
set custAcctID = 9999;
SELECT (
ifnull(prefix,'',prefix) +
ifnull(custAcctID,'',trim(cast(custAcctID as integer)))+
String('-',cast(dateformat(getdate(),'yyMMdd') as varchar(12)),'-')+
+ REPLACE( STR(ifnull(valuecount,'',valuecount)+step, digits, 0 ),'
',IFNULL(ReplaceBlank,'',ReplaceBlank)) )
AS lcID
FROM autonumbers
WHERE UCASE(keyname) = 'Quotation_NO';
end
</cfquery>
<cfelse>
<cfquery name="qry_Next_quotation_No" datasource="#dsn#">
begin
declare custAcctID integer;

set custAcctID = (select top 1 customeraccountsID from customerAccounts
where customercode ='#form.customercode#');
SELECT (
ifnull(prefix,'',prefix) +
ifnull(custAcctID,'',trim(cast(custAcctID as integer)))+
String('-',cast(dateformat(getdate(),'yyMMdd') as varchar(12)),'-')+
+ REPLACE( STR(ifnull(valuecount,'',valuecount)+step, digits, 0 ),'
',IFNULL(ReplaceBlank,'',ReplaceBlank)) )
AS lcID
FROM autonumbers
WHERE UCASE(keyname) = 'Quotation_NO';
end
</cfquery>
</cfif>

<cfset theForm.quotation_no = qry_Next_quotation_no.lcID>


<cfif form.ACTION EQ "add">
<cfset lcnewautonumber = trim(theForm.quotation_no)>
<cfif trim(qry_Next_quotation_No.lcid) EQ lcnewautonumber>

<!--- if autonuber is the same as the default autonumber then update
the autonumber --->
<cfquery name="qry_UpdateAutonumberQuotation" datasource="#dsn#">
Update autonumbers SET valuecount = IFNULL(valuecount,0,valuecount) +
step WHERE UCASE(keyname) = 'QUOTATION_NO';
</cfquery>
</cfif>

<cfif qry_Config_QuotationAuto.configvalue EQ 0>
<cfif NOT isdefined("form.Quotation_no") OR trim(form.Quotation_no) EQ ''>
<cfset lcerror=lcerror&'<li class="error">Quotation Number is
required.</li>'>
<cfelse>
<cfset lcQuoteNum = trim(theForm.Quotation_no)>
</cfif>
</cfif>
<!---
check if new autonumber already exist in the quotation.
If exist then update the autonumber again, increment it again until
there is no error
Then set an error to display that the autonumber was already used.
--->
<cfquery name="qry_QuotationCheckQuotationNumber" datasource="#dsn#">
SELECT Quotation_No
FROM Quotation
WHERE Quotation_No = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#lcnewautonumber#">
</cfquery>


<cfloop condition="qry_QuotationCheckQuotationNumber.Quotation_No GT 0">
<cfset lcIsError=TRUE>
<cfquery name="qry_UpdateAutonumberQuotation" datasource="#dsn#">
Update autonumbers SET valuecount =
IFNULL(valuecount,0,valuecount) + step WHERE UCASE(keyname) =
'Quotation_NO';
</cfquery>
<!--- this query will get the new quotation number --->


<cfquery name="qry_Next_quotation_No" datasource="#dsn#">
begin
declare custAcctID integer;

set custAcctID = (select top 1 customeraccountsID from
customerAccounts where customercode = '#form.customercode#');
SELECT (
ifnull(prefix,'',prefix) +
ifnull(custAcctID,'',trim(cast(custAcctID as integer)))+
String('-',cast(dateformat(getdate(),'yyMMdd') as varchar(12)),'-')+
+ REPLACE( STR(ifnull(valuecount,'',valuecount)+step, digits, 0 ),'
',IFNULL(ReplaceBlank,'',ReplaceBlank)) )
AS lcID
FROM autonumbers
WHERE UCASE(keyname) = 'Quotation_NO';
end
</cfquery>
<!--- end of the query --->

<cfset lcnewautonumber = qry_Next_Quotation_No.lcID>

<cfquery name="qry_QuotationCheckQuotationNumber" datasource="#dsn#">
SELECT Quotation_No
FROM Quotation
WHERE Quotation_No = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#lcnewautonumber#">
</cfquery>
</cfloop>

<cfif form.ACTION EQ "add">
<!--- *** INSERT DATA TO DATABASE TABLE *** --->
<cfset dNow=#LSDateFormat(Now(),"#g_datemask#")#>
<cfset lcDate= '#LSDateFormat(form.quodate, 'yyyy-mm-dd')#'>
<!--- generate a new id --->
<cfif qry_Config_QuotationAuto.configvalue NEQ 0 and
qry_Config_QuotationAuto.recordcount GT 0>

<cfquery name="qryMinQuotationNo" datasource="#dsn#">
begin
declare custAcctID integer;

set custAcctID = (select top 1 customeraccountsID from
customerAccounts where customercode = '#form.customercode#');
SELECT (
ifnull(prefix,'',prefix) +
ifnull(custAcctID,'',trim(cast(custAcctID as integer)))+
String('-',cast(dateformat(getdate(),'yyMMdd') as varchar(12)),'-')+
+ REPLACE( STR(ifnull(valuecount,'',valuecount)+step, digits, 0 ),'
',IFNULL(ReplaceBlank,'',ReplaceBlank)) )
AS lcID
FROM autonumbers
WHERE UCASE(keyname) = 'Quotation_NO';
end
</cfquery>

<cfquery name="qryMaxQuotationNo" datasource="#dsn#">
begin
declare custAcctID integer;

set custAcctID = (select top 1 customeraccountsID from
customerAccounts where customercode = '#form.customercode#');
SELECT (
ifnull(prefix,'',prefix)
ifnull(custAcctID,'',trim(cast(custAcctID as integer)))+
String('-',cast(dateformat(getdate(),'yyMMdd') as varchar(12)),'-')+
+ REPLACE( STR(ifnull(valuecount,'',valuecount)+step, digits, 0 ),'
',IFNULL(ReplaceBlank,'',ReplaceBlank)) )
AS lcID
FROM autonumbers
WHERE UCASE(keyname) = 'Quotation_NO';
end
</cfquery>

<cfquery name="qryMaxQuotation" datasource="#dsn#">
SELECT KEYNAME,ISNULL(RESETVALUE,0) AS
RESETVALUE,ISNULL(MAXVALUE,0) as MAXVALUE,
(select count(*) from Quotation where Quotation_NO between
'#qryMinQuotationNo.lcID#' AND '#qryMaxQuotationNo.lcID#') as MAXQUO
FROM autonumbers where KEYNAME='Quotation_NO'
</cfquery>
<cfif qryMaxQuotation.MAXQUO GTE
qryMaxQuotation.MAXVALUE-qryMaxQuotation.RESETVALUE AND
qryMaxQuotation.MAXQUO GTE 0>
<cfset lcerror=lcerror& '<li class="error">Maximum Quotation
Number has been reach.</li>'>
<cfelse>
<cfstoredproc procedure="GETQUONUM" datasource="#dsn#" debug="yes"
returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
dbvarname="@xfieldname" value="quotation_n0">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
dbvarname="@xcustomercode" value="customercode">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
dbvarname="@xquoteNum" variable="lcquoteNum">
</cfstoredproc>
<cfquery name="qryQuotationNo" datasource="#dsn#">
SELECT Quotation_NO FROM Quotation where Quotation_NO='#lcQuoteNum#'
</cfquery>
<cfloop condition="qryQuotationNo.recordcount GT 0 AND lcerror NEQ
''">
<!-- if maximum is reached then add another digit.--->
<cfquery name="qryMaxQuotation" datasource="#dsn#">
SELECT KEYNAME,ISNULL(RESETVALUE,0) AS
RESETVALUE,ISNULL(MAXVALUE,0) as MAXVALUE,
(select count(*) from Quotation where Quotation_NO between
'#qryMinQuotationNo.lcID#' AND '#qryMaxQuotationNo.lcID#') as MAXQUO
FROM autonumbers where KEYNAME='Quotation_NO'
</cfquery>
<cfif qryMaxQuotation.MAXQUO GTE
qryMaxQuotation.MAXVALUE-qryMaxQuotation.RESETVALUE AND
qryMaxQuotation.MAXQUO GTE 0>
<cfset lcerror=lcerror& '<li class="error">Maximum Quotation
Number has been reach.</li>'>
<cfbreak>
<cfelse>
<cfstoredproc procedure="GETQUONUM" datasource="#dsn#"
debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
dbvarname="@xfieldname" value="Quotation_NO">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
dbvarname="@xcustomercode" value="customercode">
<cfprocparam type="out" cfsqltype="cf_sql_varchar"
dbvarname="@xquoteNum" variable="lcquoteNum">
</cfstoredproc>
<cfquery name="qryQuotationNo" datasource="#dsn#">
SELECT Quotation_NO FROM Quotation where
Quotation_NO='#lcquoteNum#'
</cfquery>
</cfif>
</cfloop>
</cfif>
</cfif>
</cfif>
<cfif form.ACTION EQ "add">

<cfif Isdefined('theForm.customercode')>
<cfif isdefined("theForm.Quotation_no") OR trim(form.Quotation_no) EQ
'*auto*'>
<cfset lcerror=lcerror&'<li class="error">Quotation Number is
required.</li>'>
<cfelse>
<cfset lcquoteNum = trim(theForm.Quotation_no)>
</cfif>
<cfquery name="querySaveInsert" datasource="#dsn#">
insert into Quotation
(Quotation_no,refnum,quodate,duedate,
origcurrency,origrate,origamount,
discount,issuedby,
customercode,remarks,
doccurrency,docrate,docamount,lastupdated,
attention,salesperson, shipbuilding, shipstreet,
shiparea, shipcountry,shipvia,category
)
values
('#lcquoteNum#',
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.refnum#">
,'#lcDate#','#LSDateFormat(form.duedate, 'yyyy-mm-dd')#',
'#form.origcurrency#',
<cfif
lsisnumeric(form.origrate)>#LSParseNumber(form.origrate)#<cfelse>0</cfif>,
<cfif
lsisnumeric(form.origamount)>#LSParseNumber(form.origamount)#<cfelse>0</cfif>,
<cfif
lsisnumeric(form.discount)>#LSParseNumber(form.discount)#<cfelse>0</cfif>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.issuedby#">,
'#form.customercode#',
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.remarks#">,
'#form.doccurrency#',
<cfif
lsisnumeric(form.docrate)>#LSParseNumber(form.docrate)#<cfelse>0</cfif>,
<cfif
lsisnumeric(form.docamount)>#LSParseNumber(form.docamount)#<cfelse>0</cfif>,
#createodbcdatetime(now())#,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.attention#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.salesperson#">,
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.shipbuilding#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.shipstreet#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.shiparea#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.shipcountry#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.shipvia#">,
<cfqueryparam cfsqltype="cf_sql_char" value="#form.category#">
)
</cfquery>
</cfif>

<cfelse><!--- if form.action eq 'edit' --->
<cfset lcDate= '#LSDateFormat(form.quodate, 'yyyy-mm-dd')#'>
<cfset lcquoteNum= '#form.Quotation_no#'>
<!--- *** INSERT DATA TO DATABASE TABLE *** --->
<cfquery name="querySaveUpdate" datasource="#dsn#">
update Quotation
set
refnum=<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.refnum#">,
quodate='#lcDate#',
duedate='#LSDateFormat(form.duedate, 'yyyy-mm-dd')#',
origcurrency='#form.origcurrency#',
origrate=<cfif
lsisnumeric(form.origrate)>#LSParseNumber(form.origrate)#<cfelse>0</cfif>,
origamount=<cfif
lsisnumeric(form.origamount)>#LSParseNumber(form.origamount)#<cfelse>0</cfif>,
discount=<cfif
lsisnumeric(form.discount)>#LSParseNumber(form.discount)#<cfelse>0</cfif>,
issuedby=<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.issuedby#">,
customercode='#form.customercode#',
remarks=<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.remarks#">,
doccurrency='#form.doccurrency#',
docrate=<cfif
lsisnumeric(form.docrate)>#LSParseNumber(form.docrate)#<cfelse>0</cfif>,
docamount=<cfif
lsisnumeric(form.docamount)>#LSParseNumber(form.docamount)#<cfelse>0</cfif>,
lastupdated=now(),
attention=<cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.attention#">,
quotationrev = '#form.quotationrev#',
salesperson = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.salesperson#">,
shipbuilding = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.shipbuilding#">,
shipstreet = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.shipstreet#">,
shiparea = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.shiparea#">,
shipcountry = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.shipcountry#">,
shipvia = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#form.shipvia#">
category = <cfqueryparam cfsqltype="cf_sql_char"
value="#form.category#">
where
Quotation_no = '#lcquoteNum#'
</cfquery>

</cfif>


<cfloop list="#form.idcode#" index="ItemIndex">
<cfset quodcode="quodcode" & #ItemIndex#><!--- saved quotation(quo)
detail code --->
<cfset servicecode="servicecode" & #ItemIndex#>
<cfset description="description" & #ItemIndex#>
<cfset accountCode="accountCode" & #ItemIndex#>
<cfset qty="qty" & #ItemIndex#>
<cfset qtyunit="qtyunit" & #ItemIndex#>
<cfset unitprice="unitprice" & #ItemIndex#>
<cfset amount="amount" & #ItemIndex#>
<cfset cost= "cost" & #ItemIndex#>
<cfset famount="famount" & #ItemIndex#>
<cfset ChkDel="ChkDel" & #ItemIndex#>
<cfset perioddisplay="perioddisplay" & #ItemIndex#>
<cfset perioddate="perioddate" & #ItemIndex#>
<cfset underline="underline" & #ItemIndex#>




<!--- <cfquery name="qry_advanceplan" datasource="#dsn#">
select advance from planservices where
planserviceid='#form[servicecode]#'
</cfquery> --->
<!---<cfset lcperioddisplay= LSDateFormat(form[perioddisplay],
'yyyy-mm-dd')>--->
<!--- <cfif qry_advanceplan.advance EQ 1>
<cfset lcadvance=1>
<cfelse>
<cfset lcadvance=0>
</cfif> --->
<cfif IsDefined("form.quodcode#ItemIndex#") and form[quodcode] NEQ "">
<cfif IsDefined("form.ChkDel#ItemIndex#")>
<!--- if hidden field is found delete it if check is delete--->
<cfquery name="delete_Quotation_detail" datasource="#dsn#">
delete from Quotation_details
where Quotationdetailid=<cfqueryparam value="#form[quodcode]#"
cfsqltype="cf_sql_integer">
</cfquery>
<cfelse>
<!--- update it --->
<cfif (form[servicecode] EQ '' OR form[servicecode] EQ '0' )>
<cfif form[description] NEQ ''>
<cfquery name="update_Quotation_detail" datasource="#dsn#">
update Quotation_detail
set PlanserviceId = '#form[servicecode]#',
PlanServiceDesc = '#form[description]#',
qty = <cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>NULL</cfif>,
qtyunit = '#form[qtyunit]#',
unitprice = <cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,
amount =<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,
famount = xConvertPrice('#form.doccurrency#',<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,'form.origcurrency',#LSParseNumber(form.docrate)#,#LSParseNumber(form.origrate)#),

perioddate = <cfif
isdate(form[perioddate])>'#LSDateFormat(form[perioddate],
'yyyy-mm-dd')#'<cfelse>NULL</cfif>,
perioddisplay = '#form[perioddisplay]#',
underline = <cfif
IsDefined("form.underline#ItemIndex#")>1<cfelse>0</cfif>
<!---accountCode =form[accountCode],--->
<!--- removed jerry pang 06/09/2004 04:40pm
qty = NULL,
qtyunit = NULL,
unitprice = NULL,
amount =NULL,
famount = NULL,
perioddate = NULL,
perioddisplay = NULL--->
where Quotationdetailid=<cfqueryparam value="#form[quodcode]#"
cfsqltype="cf_sql_integer">
</cfquery>
<cfelse>
<!--- if trim(form[description]) EQ '' --->
<cfquery name="delete_Quotation_detail" datasource="#dsn#">
DELETE from Quotation_details
where invoicedetailid=<cfqueryparam value="#form[quodcode]#"
cfsqltype="cf_sql_integer">
</cfquery>
</cfif>
<cfelse>
<cfquery name="update_Quotation_detail" datasource="#dsn#">
update Quotation_details
set PlanserviceId = '#form[servicecode]#',
PlanServiceDesc = '#form[description]#',
<!---accountCode =form[accountCode],--->
qty = <cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>NULL</cfif>,
qtyunit = '#form[qtyunit]#',
unitprice = <cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,
cost = <cfif
lsisnumeric(form[cost])>#LSParseNumber(form[cost])#<cfelse>0</cfif>
amount =<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,
famount = xConvertPrice('#form.doccurrency#',<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,'form.origcurrency',#LSParseNumber(form.docrate)#,#LSParseNumber(form.origrate)#),

perioddate = <cfif
isdate(form[perioddate])>'#LSDateFormat(form[perioddate],
'yyyy-mm-dd')#'<cfelse>NULL</cfif>,
perioddisplay = '#form[perioddisplay]#',
underline = <cfif
IsDefined("form.underline#ItemIndex#")>1<cfelse>0</cfif>
<!---#LSParseNumber(form[amount])#--->
where Quotationdetailid=<cfqueryparam value="#form[quodcode]#"
cfsqltype="cf_sql_integer">
</cfquery>
</cfif>
</cfif>
<cfelse>
<cfif form[servicecode] NEQ '' AND form[servicecode] NEQ '0'>
<!--- insert the new items --->
<cfquery name="insert_Quotation_detail" datasource="#dsn#">
insert into
Quotation_details(Quotation_no,PlanServiceId,PlanServiceDesc,accountcode,
qty,
qtyunit,
unitprice,
cost,<!--- john added --->
amount,
famount,
perioddate,
perioddisplay,
underline)

values('#lcquoteNum#',#form[servicecode]#,'#form[description]#','#form[accountCode]#',
<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>,
'#form[qtyunit]#',
<cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,
<cfif
lsisnumeric(form[cost])>#LSparseNumber(form[cost])#<cfelse>0</cfif>,
<cfif lsisnumeric(form[qty])>
#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,
xConvertPrice('#form.doccurrency#',<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,'form.origcurrency',#LSParseNumber(form.docrate)#,#LSParseNumber(form.origrate)#),

<cfif
isdate(form[perioddate])>'#LSDateFormat(form[perioddate],
'yyyy-mm-dd')#'<cfelse>NULL</cfif>,
'#form[perioddisplay]#',
<cfif IsDefined("form.underline#ItemIndex#")>1<cfelse>0</cfif>
)
</cfquery>
<cfelse>
<cfif (form[servicecode] EQ '' OR form[servicecode] NEQ '0') and
form[description] NEQ ''>
<cfquery name="insert_Quotation_detail" datasource="#dsn#">
insert into
Quotation_details(Quotation_no,PlanServiceId,PlanServiceDesc,accountcode,
qty,
qtyunit,
unitprice,
cost,<!--- john added --->
amount,
famount,
perioddate,
perioddisplay,
underline)

values('#lcquoteNum#','#form[servicecode]#','#form[description]#','#form[accountCode]#',
<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>NULL</cfif>,
'#form[qtyunit]#',
<cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>NULL</cfif>,
<cfif
lsisnumeric(form[cost])>#LSParseNUmber(forn[cost])#<cfelse>0</cfif>
<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>NULL</cfif>,
xConvertPrice('#form.doccurrency#',<cfif
lsisnumeric(form[qty])>#LSParseNumber(form[qty])#<cfelse>0</cfif>*<cfif
lsisnumeric(form[unitprice])>#LSParseNumber(form[unitprice])#<cfelse>0</cfif>,

'form.origcurrency',#LSParseNumber(form.docrate)#,#LSParseNumber(form.origrate)#),

<cfif
isdate(form[perioddate])>'#LSDateFormat(form[perioddate],
'yyyy-mm-dd')#'<cfelse>NULL</cfif>,
'#form[perioddisplay]#',
<cfif IsDefined("form.underline#ItemIndex#")>1<cfelse>0</cfif>
)
</cfquery>
</cfif>
</cfif>
</cfif>
</cfloop>
</cfif>
</cfif>


Greg Fenton Posted on 2006-05-05 15:21:15.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Problem in query
References: <445ab1af@forums-2-dub>
In-Reply-To: <445ab1af@forums-2-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: gfenton-xp.sybase.com
X-Original-NNTP-Posting-Host: gfenton-xp.sybase.com
Message-ID: <445b6d6b$1@forums-1-dub>
Date: 5 May 2006 08:21:15 -0700
X-Trace: forums-1-dub 1146842475 10.25.98.184 (5 May 2006 08:21:15 -0700)
X-Original-Trace: 5 May 2006 08:21:15 -0700, gfenton-xp.sybase.com
Lines: 26
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5323
Article PK: 1376


john wrote:
> When the customeraccountsID is not found it will return an error
> message that " no primary key value for quotation is table
> quotation_detail.

Please always provide the version and build number of SQL Anywhere that
you are using (e.g. use the command "dbeng9 -v" for SQL Anywhere 9.x)

Can you try paring the code down to as few statements as possible that
reproduce the error?

Or at the very least, give an idea of where the error is being thrown from?

BTW: this thread would be better served if posted to the newsgroup:

sybase.public.sqlanywhere.general

Thanks,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/