Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

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


0
bsheikh
Asked:
bsheikh
  • 2
  • 2
1 Solution
 
bsheikhAuthor Commented:
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()
----------------------------------------------------------------------------------------------------------------------------------------
0
 
Brian CroweDatabase AdministratorCommented:
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



0
 
Brian CroweDatabase AdministratorCommented:
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.
0
 
bsheikhAuthor Commented:
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
0
 
GranModCommented:
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now