usp_save_user_buypointitems_ncash

Syek · 06/23/2013, 03:35

hi, somebody could help me with this problem?

the problem is the usual error 0 when buying things from item mall

Here usp_Save_User_BuyPointItems_NCash:

Spoiler

Here the logs:

PS_DBAGENT__system log start

Spoiler

PS_GAMELOG__system log start

Spoiler

If I'm missing some logs or dbo please tell me

castor4878 · 06/23/2013, 05:47

PS_DBAGENT__system.log indicates what kind of error occurs:

[SQL Server]La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo., SQL STATE: 22007, NATIVE ERROR: 242 (0xF2)

and where the error occurs, we also got all transmitted parameters:

2013-06-22 19:17:46 SaveBuyPointItem 7 Leon qerr=0, {?=call usp_Save_User_BuyPointItems_NCash(1,7,0,'B1_Cface0 001', '2013-06-22 19:17:46')}

so, yet another, date conversion error ....

your stored procedure is defined as:

Code:

ALTER Proc [dbo].[usp_Save_User_BuyPointItems_NCash]

@UserUID int,
@CharID int,
@UsePoint int,
@ProductCode varchar(20),
@textDate varchar(30)

AS

DECLARE @UseTime datetime
set @UseTime=CONVERT(datetime, @textDate, 120)

so far so good, it receives:
1 as an int stored in @UserUID
7 as an int stored in @CharID
0 as an int stored in @UsePoint
'B1_Cface0 001' as varchar(20) (it is 13 char long) stored in @ProductCode
'2013-06-22 19:17:46' as varchar(30) (it is 29 char long) stored in @textDate

then you convert that "ODBC canonical" date into a datetime field using the convert function with the right parameter (120 means it is "ODBC canonical").

*w/o surptise* the log doesn't contain something like, "invalid argument for convert function", nor "unknown convert function", nor "invalid argument for assignment / invalid value for @UseTime field" or whatever close to that.

the error is so in the code present after.

we have:

PS_UserData.dbo.usp_Update_UserPoint @UserUID, @UsePoint

that does not use date (and the log does contain a date related error).

so last statement:

INSERT INTO PointLog(UseType,UserUID,CharID,UsePoint,ProductCo de,UseDate)
VALUES(@UseType,@UserUID,@CharID,@UsePoint,@Produc tCode,@textDate)

where we need about 3 seconds to figure out that the string-field-containing-an-OBDC-string-date-that-the-server-can-NOT-handle-by-itself parameter (the "textDate" variable) is used instead of the @UseTime variable, the one we introduced to handle that error ...

Syek · 06/23/2013, 07:40

thanks thanks thanks you are awesome

thanks for all the help more than says the errors, explaining to us how to fix them and how to understand why they happen

long live to you

AxelMac · 08/31/2013, 03:17

Quote:
Originally Posted by castor4878
PS_DBAGENT__system.log indicates what kind of error occurs:

[SQL Server]La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo., SQL STATE: 22007, NATIVE ERROR: 242 (0xF2)

and where the error occurs, we also got all transmitted parameters:

2013-06-22 19:17:46 SaveBuyPointItem 7 Leon qerr=0, {?=call usp_Save_User_BuyPointItems_NCash(1,7,0,'B1_Cface0 001', '2013-06-22 19:17:46')}

so, yet another, date conversion error ....

your stored procedure is defined as:
Code:
ALTER Proc [dbo].[usp_Save_User_BuyPointItems_NCash]

@UserUID int,
@CharID int,
@UsePoint int,
@ProductCode varchar(20),
@textDate varchar(30)

AS

DECLARE @UseTime datetime
set @UseTime=CONVERT(datetime, @textDate, 120)
so far so good, it receives:
1 as an int stored in @UserUID
7 as an int stored in @CharID
0 as an int stored in @UsePoint
'B1_Cface0 001' as varchar(20) (it is 13 char long) stored in @ProductCode
'2013-06-22 19:17:46' as varchar(30) (it is 29 char long) stored in @textDate

then you convert that "ODBC canonical" date into a datetime field using the convert function with the right parameter (120 means it is "ODBC canonical").

*w/o surptise* the log doesn't contain something like, "invalid argument for convert function", nor "unknown convert function", nor "invalid argument for assignment / invalid value for @UseTime field" or whatever close to that.

the error is so in the code present after.

we have:

PS_UserData.dbo.usp_Update_UserPoint @UserUID, @UsePoint

that does not use date (and the log does contain a date related error).

so last statement:

INSERT INTO PointLog(UseType,UserUID,CharID,UsePoint,ProductCo de,UseDate)
VALUES(@UseType,@UserUID,@CharID,@UsePoint,@Produc tCode,@textDate)

where we need about 3 seconds to figure out that the string-field-containing-an-OBDC-string-date-that-the-server-can-NOT-handle-by-itself parameter (the "textDate" variable) is used instead of the @UseTime variable, the one we introduced to handle that error ...

you can post the complet query? pls

~~Hep~~ · 08/31/2013, 10:16

#Wrong Section btw. Thank you castor , helped me too .

AxelMac · 08/31/2013, 13:47

what changes should I make?