• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Error converting varchar to number

I have code behind a button on my website that runs a stored procedure.  The user uses a date picker control to insert a date and then clicks the button to run the stored procedure.  When I click the button I get an error 'SQLException: error converting varchar to number' .

Below is my code for my button. I have also attached my stored procedure code. When I execute the procedure in SQL Management Studio with @enddate = N'20120331' it works fine.

Not sure what I need to change on my code behind my button click.



 Dim oConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CMSConnectionString").ConnectionString)
        Dim sql As New System.Data.SqlClient.SqlCommand()
        sql.Connection = oConn
        oConn.Open()

        'Run stored Procedure sp_Update_Job_PO_Amt_Adhoc

        sql.CommandText = "sp_Update_Job_PO_Amt_Adhoc"
        sql.CommandType = Data.CommandType.StoredProcedure
        'Declare parameters
        'sql.Parameters.AddWithValue("@begdate", Data.SqlDbType.Char)
        sql.Parameters.AddWithValue("@enddate", Data.SqlDbType.Char)
        'Assign control values to parameters
        'sql.Parameters("@begdate").Value = rdpBegDate
        sql.Parameters("@enddate").Value = rdpenddate.SelectedDate.Value.ToShortDateString
        oConn.Close()

        Try
            'open connection
            oConn.Open()
            sql.ExecuteNonQuery()
            oConn.Close()
        Catch ex As Exception
            'handle error
            Throw (ex)

        End Try

Open in new window

Poamtsp.sql
0
imstac73
Asked:
imstac73
2 Solutions
 
wdosanjosCommented:
rdpenddate.SelectedDate.Value.ToShortDateString returns the date formatted with /.  Try using rdpenddate.SelectedDate.Value.ToString("yyyyMMdd") instead.
0
 
BuggyCoderCommented:
Change your code as:-
sql.Parameters("@enddate").Value = rdpenddate.SelectedDate.Value.ToString("yymmdd");

also make a change to your procedure ensuring that any datetime comparisons are done by converting the date passed to sql datetime object like this:-

convert(datetime,@enddate,112)
0
 
imstac73Author Commented:
Thank you guys..this worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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