Error in SQl Insert Qry

Discussion forum about all things SqlBase.
Subbulakshmi

Error in SQl Insert Qry

Post by Subbulakshmi » 23 Oct 2014, 08:24

Hi All,

THe below is the query when gets executed via Team Developer getting error and the record is not inserted. But when i run in teh SQL Server mAnagement studio, i ddint get the error the record gets inserted.

INSERT INTO sysadm.index_value
( effective_date, index_number, index_value, index_movement )
VALUES
( '2014-10-20', 245, 83.46,1.8798828125 )

THE Team developer variable it holds the value is number.
Sql Table datatype is decimal 9,5

CAN any one help me to identufy the issue. what is the range of number datatype in Team Devloper, how much it can hold ?

FRBhote
India
Posts: 2176
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Error in SQl Insert Qry

Post by FRBhote » 24 Oct 2014, 06:30

What is the error?

Subbulakshmi

Re: Error in SQl Insert Qry

Post by Subbulakshmi » 25 Oct 2014, 06:16

Arrached the error, when i debugged, the above the insert query when the error comes.
You do not have the required permissions to view the files attached to this post.

frevelundfey

Re: Error in SQl Insert Qry

Post by frevelundfey » 27 Oct 2014, 10:40

Hi,
your question should also mention which error you get and some more Information of the context.
I assume you run the Statement against MS SqlServer.

One possible cause for an error could be that when you send a date as literal (in your case '2014-10-20') the server might expect another date-format (e.g. yyyy-dd-MM).
The same with your numbers - you use a dot as decimal - but maybe the server expects a comma.

Regards Thomas

Subbulakshmi

Re: Error in SQl Insert Qry

Post by Subbulakshmi » 27 Oct 2014, 12:34

error is sql error - 3617

code is :

HI ,
I will update the forum as well.
Function: Save
Description:
Returns
Boolean:
Parameters
String: spIndexNumber
String: spIndexValue
Number: npIndexMovement
Static Variables
Local variables
Boolean: bOk
Number: nTempIndexValue
String: nTest
String: sSql
Actions
Set nTempIndexValue = SalStrToNumber( spIndexValue )
When SqlError
Return FALSE
Set sSql = "
INSERT INTO sysadm.index_value
( effective_date, index_number, index_value, index_movement )
VALUES
( :dtgSystemDate, :spIndexNumber, :nTempIndexValue, :npIndexMovement"
If NOT SqlPrepareAndExecute( hgSql, sSql )
Set sSql = "
UPDATE sysadm.index_value SET
index_value = :nTempIndexValue,
index_movement = :npIndexMovement
WHERE effective_date = :dtgSystemDate
AND index_number = :spIndexNumber"
If SqlPrepareAndExecute( hgSql, sSql )
Set bOk = TRUE
Else
Set bOk = TRUE
Return bOk

THe indexmovement is calculated in the following function.

!!CB!! 279
Function: EvaluateIndexMovement
Description:
Returns
Boolean:
Parameters
String: spIndexNumber
Number: npIndexValue
Receive Number: npIndexMovement
Static Variables
Local variables
Boolean: bOk
Date/Time: dtPreviousEffectiveDate
Number: nFetch
Number: nOldIndexValue
String: sSql
Actions
Set sSql = "
SELECT index_value, effective_date
INTO :nOldIndexValue, :dtPreviousEffectiveDate
FROM sysadm.index_value
WHERE index_number = :spIndexNumber
AND effective_date <= :dtgPreviousDate
ORDER BY effective_date DESC"
If SqlPrepareAndExecute( hgSql, sSql )
If SqlFetchNext( hgSql, nFetch )
Set bOk = TRUE
If bOk
If spIndexNumber = cBusinessParams.FindBusinessParam( SalNumberToStrX( PARAM_MONEY_MARKET, 0))
OR spIndexNumber = cBusinessParams.FindBusinessParam( SalNumberToStrX( PARAM_UTCM_INDEX, 0 ))
Set npIndexMovement = ( npIndexValue * ( dtgSystemDate - dtPreviousEffectiveDate ) / 365.25 )
Else
If nOldIndexValue = 0
Set npIndexMovement = 0
Else
Set npIndexMovement = ( ( npIndexValue - nOldIndexValue ) / nOldIndexValue ) * 100
Return bOk

its been declared as receive number.

Igor Ivanovic
Site Admin
Site Admin
Croatia
Posts: 1406
Joined: 05 Mar 2017, 12:37
Location: Zagreb, Croatia

Re: Error in SQl Insert Qry

Post by Igor Ivanovic » 09 Dec 2014, 08:26

Sorry, don't know what the problem is.

When posting code, please use the Code display.

Without code:
Function: GConnect
Description:
Returns
Number:
Parameters
Static Variables
Local variables
Actions
Return SqlConnectDotNet (ghSql, CONNECTION_STRING, '', 9 )

With code:

Code: Select all

Function: GConnect
   Description:
   Returns
      Number:
   Parameters
   Static Variables
   Local variables
   Actions
      Return SqlConnectDotNet (ghSql, CONNECTION_STRING, '', 9 )
Way, way better, isn't it?
Igor Ivanovic
Image

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests