Solved

Error converting varchar to number

Posted on 2012-04-11
3
368 Views
Last Modified: 2012-04-11
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
Comment
Question by:imstac73
3 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 250 total points
ID: 37834794
rdpenddate.SelectedDate.Value.ToShortDateString returns the date formatted with /.  Try using rdpenddate.SelectedDate.Value.ToString("yyyyMMdd") instead.
0
 
LVL 20

Assisted Solution

by:BuggyCoder
BuggyCoder earned 250 total points
ID: 37834878
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
 

Author Comment

by:imstac73
ID: 37835041
Thank you guys..this worked.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now