usp_save_user_buypointitems_ncash

06/23/2013 03:35 Syek#1
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:


Here the logs:

PS_DBAGENT__system log start

PS_GAMELOG__system log start


If I'm missing some logs or dbo please tell me :)
06/23/2013 05:47 castor4878#2
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 ...
06/23/2013 07:40 Syek#3
thanks thanks thanks you are awesome :handsdown: thanks for all the help more than says the errors, explaining to us how to fix them and how to understand why they happen :D

long live to you :handsdown:
08/31/2013 03:17 AxelMac#4
Quote:
Originally Posted by castor4878 View Post
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
08/31/2013 10:16 Hep#5
#Wrong Section btw. Thank you castor , helped me too . :D
08/31/2013 13:47 AxelMac#6
what changes should I make?