Link to home
Start Free TrialLog in
Avatar of bsheikh
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


Avatar of bsheikh
bsheikh

ASKER

Hi Again .
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_criteria", cnsqlserver)
        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.StoredProcedure
        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_dat)



        sqlstr2.ExecuteNonQuery()

        criteria_id = Ans.Value
        chk_date = chk_dat.Value



        cnsqlserver.Close()
----------------------------------------------------------------------------------------------------------------------------------------
Avatar of Brian Crowe
Dim sqlstr2 As New SqlCommand("insert_criteria", cnsqlserver)

sqlstr2.commandtype = commandtype.storedprocedure
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("@CDTEST").direction = parameterdirection.output
sqlstr2.parameters("@CHKDAT").direction = parameterdirection.output

try
   sqlstr2.connection.open
   sqlstr2.ExecuteNonQuery()
   criteria_id = sqlstr2.parameters("@CDTEST").value
   chk_date = sqlstr2.parameters("@CHKDAT").Value
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.
Avatar of bsheikh

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
ASKER CERTIFIED SOLUTION
Avatar of GranMod
GranMod

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial