bsheikh
asked on
Invalid character value for cast specification
Hi Experts,
I've got very serious and strange problem . and need to fix as soon as possible
let me paste my series of stored procedures.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
ALTER proc insert_criteria (@SD datetime,@ED datetime,@Dpt varchar(50),@CDTEST int output,@CHKDAT INT OUT)
AS
BEGIN
DECLARE @err int
DECLARE @CD INT
DECLARE @CID INT
DECLARE @cnt INT
DECLARE @DTCHK INT
DECLARE @CHKDATA INT
DECLARE @CD1 INT
DECLARE @cnt1 INT
DECLARE @cnt2 INT
DECLARE @cnt3 INT
DECLARE @CD2 INT
DECLARE @led smalldatetime
DECLARE @csd smalldatetime
DECLARE @LYsdate SMALLDATETIME
DECLARE @LYedate SMALLDATETIME
DECLARE @LYSD SMALLDATETIME
DECLARE @LYED SMALLDATETIME
DECLARE @WK INT
DECLARE @intErrorCode INT
DECLARE @C INT
SET @CD1 = 0
SELECT @CD=CID, @cnt=count(*) FROM criteria
WHERE SDate=@SD AND EDate=@ED
AND Dept=@Dpt
GROUP by CID
IF @cnt > 0
BEGIN
SET @CDTEST =@CD
SET @CHKDAT = 1
END
ELSE
EXEC sp_check_dates @SD, @ED,@DTCHK=@DTCHK OUTPUT
IF
@DTCHK= 1
BEGIN
INSERT Criteria(SDate,EDate,Dept, UID)
VALUES (@SD,@ED,@Dpt,Host_Name())
SELECT @c = SCOPE_IDENTITY()
EXEC get_ly_sd @SD,
@csd = @csd OUTPUT
EXEC get_ly_ed @ED,
@led = @led OUTPUT
EXEC insert_SEDates1 @SD,@ED,@csd,@led,@c
EXEC sp_CSales @SD,@ED,@Dpt,@c
EXEC sp_LYear @csd,@led,@Dpt,@c
EXEC sp_dept_wise_total_Cyear @c
EXEC sp_dept_wise_total_lyear @c
EXEC sp_dept_total @c
EXEC pull_Totsales @c
EXEC UPDATE_TOTSALES @c
-- EXEC OLD_LOC @CID
EXEC TEST_DEPTS @c
EXEC sp_comp_store @c
EXEC sp_st_total @c
EXEC CLEAR_CSALES_Lyear
SET @CDTEST = @c
SET @CHKDAT = 1
END
ELSE IF @DTCHK= -1
BEGIN
SET @CDTEST= -1
SET @CHKDAT = -1
END
END
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
my main sp " insert_criteria " is accepting 3 parameters
this main SP is calling series of inner stored procedures after completing initial condition checking
when i call this "insert_criteria" SP from VB.NET giving dates of 1 week
example
SD = " 04/03/06 "
ED = " 04/09/06 "
Dpt = "All"
it is working fine
but when i give date range starting from start if Fiscal year till last last day of last week this fails.
example
SD = " 01/30/06 "
ED = " 04/09/06 "
Dpt = " All "
it is not working when i gives full Year to Date range.
the interestign thing is
when i run same stored procedure with in Query Analyzer it is runnign fine without any errors.
and last impt thing is when i try to DEBUG this SP
i got message " Invalid character value for cast specification "
i am stuck here please help me out .
Regards
bilal
I've got very serious and strange problem . and need to fix as soon as possible
let me paste my series of stored procedures.
--------------------------
ALTER proc insert_criteria (@SD datetime,@ED datetime,@Dpt varchar(50),@CDTEST int output,@CHKDAT INT OUT)
AS
BEGIN
DECLARE @err int
DECLARE @CD INT
DECLARE @CID INT
DECLARE @cnt INT
DECLARE @DTCHK INT
DECLARE @CHKDATA INT
DECLARE @CD1 INT
DECLARE @cnt1 INT
DECLARE @cnt2 INT
DECLARE @cnt3 INT
DECLARE @CD2 INT
DECLARE @led smalldatetime
DECLARE @csd smalldatetime
DECLARE @LYsdate SMALLDATETIME
DECLARE @LYedate SMALLDATETIME
DECLARE @LYSD SMALLDATETIME
DECLARE @LYED SMALLDATETIME
DECLARE @WK INT
DECLARE @intErrorCode INT
DECLARE @C INT
SET @CD1 = 0
SELECT @CD=CID, @cnt=count(*) FROM criteria
WHERE SDate=@SD AND EDate=@ED
AND Dept=@Dpt
GROUP by CID
IF @cnt > 0
BEGIN
SET @CDTEST =@CD
SET @CHKDAT = 1
END
ELSE
EXEC sp_check_dates @SD, @ED,@DTCHK=@DTCHK OUTPUT
IF
@DTCHK= 1
BEGIN
INSERT Criteria(SDate,EDate,Dept,
VALUES (@SD,@ED,@Dpt,Host_Name())
SELECT @c = SCOPE_IDENTITY()
EXEC get_ly_sd @SD,
@csd = @csd OUTPUT
EXEC get_ly_ed @ED,
@led = @led OUTPUT
EXEC insert_SEDates1 @SD,@ED,@csd,@led,@c
EXEC sp_CSales @SD,@ED,@Dpt,@c
EXEC sp_LYear @csd,@led,@Dpt,@c
EXEC sp_dept_wise_total_Cyear @c
EXEC sp_dept_wise_total_lyear @c
EXEC sp_dept_total @c
EXEC pull_Totsales @c
EXEC UPDATE_TOTSALES @c
-- EXEC OLD_LOC @CID
EXEC TEST_DEPTS @c
EXEC sp_comp_store @c
EXEC sp_st_total @c
EXEC CLEAR_CSALES_Lyear
SET @CDTEST = @c
SET @CHKDAT = 1
END
ELSE IF @DTCHK= -1
BEGIN
SET @CDTEST= -1
SET @CHKDAT = -1
END
END
--------------------------
my main sp " insert_criteria " is accepting 3 parameters
this main SP is calling series of inner stored procedures after completing initial condition checking
when i call this "insert_criteria" SP from VB.NET giving dates of 1 week
example
SD = " 04/03/06 "
ED = " 04/09/06 "
Dpt = "All"
it is working fine
but when i give date range starting from start if Fiscal year till last last day of last week this fails.
example
SD = " 01/30/06 "
ED = " 04/09/06 "
Dpt = " All "
it is not working when i gives full Year to Date range.
the interestign thing is
when i run same stored procedure with in Query Analyzer it is runnign fine without any errors.
and last impt thing is when i try to DEBUG this SP
i got message " Invalid character value for cast specification "
i am stuck here please help me out .
Regards
bilal
Dim sqlstr2 As New SqlCommand("insert_criteri a", cnsqlserver)
sqlstr2.commandtype = commandtype.storedprocedur e
with sqlstr2.parameters
.add("@SD", sqldbtype.datetime, 8).value = stdate
.add("@ED", sqldbtype.datetime, 8).value = enddate
.add("@Dpt", sqldbtype.varchar, 50).value = me.txtall.text --you'll need to adjust the varchar size to match your procedure parameter size
.add("@CDTEST", sqldbtype.int, 4)
.add("@CHKDAT", sqldbtype.int, 4)
end with
sqlstr2.parameters("@CDTES T").direct ion = parameterdirection.output
sqlstr2.parameters("@CHKDA T").direct ion = parameterdirection.output
try
sqlstr2.connection.open
sqlstr2.ExecuteNonQuery()
criteria_id = sqlstr2.parameters("@CDTES T").value
chk_date = sqlstr2.parameters("@CHKDA T").Value
catch ex as exception
messagebox.show(ex.message )
finally
if sqlstr2.connection.state = connectionstate.open
sqlstr2.connection.close
end if
end try
sqlstr2.commandtype = commandtype.storedprocedur
with sqlstr2.parameters
.add("@SD", sqldbtype.datetime, 8).value = stdate
.add("@ED", sqldbtype.datetime, 8).value = enddate
.add("@Dpt", sqldbtype.varchar, 50).value = me.txtall.text --you'll need to adjust the varchar size to match your procedure parameter size
.add("@CDTEST", sqldbtype.int, 4)
.add("@CHKDAT", sqldbtype.int, 4)
end with
sqlstr2.parameters("@CDTES
sqlstr2.parameters("@CHKDA
try
sqlstr2.connection.open
sqlstr2.ExecuteNonQuery()
criteria_id = sqlstr2.parameters("@CDTES
chk_date = sqlstr2.parameters("@CHKDA
catch ex as exception
messagebox.show(ex.message
finally
if sqlstr2.connection.state = connectionstate.open
sqlstr2.connection.close
end if
end try
FYI, you didn't really need the size parameter on the parameter definition for the datetime and int parameters...I just put that in there by reflex...but it is necessary for string type datatypes.
ASKER
hi There,
it is solved.
only problem is timeout parameter
i didnt change it from default value which was set to 30 secs
as i was compiling records for Year to date so it was taking more time than 30 secs.
anyway
as soon as i changed the tiem out parameter it start working
thanks for reply
regards
bilal
it is solved.
only problem is timeout parameter
i didnt change it from default value which was set to 30 secs
as i was compiling records for Year to date so it was taking more time than 30 secs.
anyway
as soon as i changed the tiem out parameter it start working
thanks for reply
regards
bilal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this is my code in VB.NET
but as i mentioned above.
SP is running fine in Query analuzer regardless of date range.
but if i run in Debug mode i got error as mentioned in title.
--------------------------
cnsrver.Open()
Dim sqlstr2 As New SqlCommand("insert_criteri
Dim sd As New SqlParameter
Dim ed As New SqlParameter
Dim Dpt As New SqlParameter
Dim Ans As New SqlParameter
Dim chk_dat As New SqlParameter
sqlstr2.CommandType = CommandType.StoredProcedur
sqlstr2.Connection = cnsqlserver
sd.DbType = DbType.DateTime
ed.DbType = DbType.DateTime
Dpt.DbType = DbType.String
Ans.DbType = DbType.Int16
chk_dat.DbType = DbType.Int16
Ans.Direction = ParameterDirection.Output
chk_dat.Direction = ParameterDirection.Output
sd.Value = stdate
ed.Value = enddate
Dpt.Value = Me.Txtall.Text
sd.ParameterName = "@SD"
ed.ParameterName = "@ED"
Dpt.ParameterName = "@Dpt"
Ans.ParameterName = "@CDTEST"
chk_dat.ParameterName = "@CHKDAT"
sqlstr2.Parameters.Add(sd)
sqlstr2.Parameters.Add(ed)
sqlstr2.Parameters.Add(Dpt
sqlstr2.Parameters.Add(Ans
sqlstr2.Parameters.Add(chk
sqlstr2.ExecuteNonQuery()
criteria_id = Ans.Value
chk_date = chk_dat.Value
cnsqlserver.Close()
--------------------------