passing a string through to SQL via stored procedure using datetime

Question

Not quite sure what I am doing wrong here. I have a string from a text box which is a date (22/10/2013) and I want to use it to select a value from a SQL DB using a stored procedure.

The VB code is:

    'gets the values for the daily prices from DB if they exist.
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Optimiser_TestConnectionString").ConnectionString)
        conn.Open()
        Using cmd As SqlCommand = conn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "get_dailypricing"
            cmd.Parameters.Add("@datedisplay", SqlDbType.DateTime).Value = date_select.Text
            Dim sqlrd As SqlDataReader = cmd.ExecuteReader

            If sqlrd.HasRows Then
                sqlrd.Read()
                Me.date_select.Text = sqlrd.Item("price")
            Else
                Me.date_select.Text = "N/A"
            End If
        End Using
    End Using

The stored procedure is :

ALTER procedure [dbo].[get_dailypricing]
    @DateDisplay date
AS
Begin 
   select price 
   from dailyPricing
   where dateSubmitted = @DateDisplay
end

I am using Visual Studio 2012 and SQL Server 2012. The date in SQL Server is of type datetime in the format of 2013-10-22 11:37:49.727

I get an error

The string was not recognized as a valid DateTime. There is an unknown word starting at index 0

If you google the error that you got there are no end of SO questions and answers to this. [link](http://stackoverflow.com/questions/10625292/the-string-was-not-recognized-as-a-valid-datetime-there-is-an-unknown-word-star) [link](http://stackoverflow.com/questions/16462510/the-string-was-not-recognized-as-a-valid-datetime-there-is-an-unknown-word-star) [link](http://stackoverflow.com/questions/17492269/the-string-was-not-recognized-as-a-valid-datetime-there-is-an-unknown-word-star) — Squirrel5853, Oct 22 '13 at 11:15

score 0 · Answer 1 · answered Oct 22 '13 at 11:01

0

Your Date in SQL is in DateTime format but your stored procedure parameter is in Date datatype. you need to take parameter datatype to DateTime like this.

@DateDisplay date

answered Oct 22 '13 at 11:01

SanketS

963
1
13
36

score 0 · Answer 2 · answered Oct 22 '13 at 11:15

The problem is in your .NET code, not in SQL Server

In line:

cmd.Parameters.Add("@datedisplay", SqlDbType.DateTime).Value = date_select.Text

You are passing the date value as a string instead of a DateTime object. .NET will try to parse this string using the current thread's culture, which may not be able to parse the user's input.

You should validate the user's string before parsing it yourself, then pass the parsed value to the stored procedure. Better yet, use a DatePicker-like control to return only valid addresses.

Assuming you do want to use the thread's locale, you should use this code:

Dim theDate=DateTime.Parse(date_select.Text)
cmd.Parameters.AddWithValue("@datedisplay",theDate)

A safer option is to require the user to enter the text in a specific format, then parse using this format. Assuming you want the user to use the InvariantCulture, you should write:

Dim theDate=DateTime.Parse(date_select.Text,CultureInfo.InvariantCulture)
cmd.Parameters.AddWithValue("@datedisplay",theDate)

score 0 · Answer 3 · answered Oct 22 '13 at 12:20

Thanks pointing out the error in my stored procedure but the real problem was I was using the date_select textbox to display the price when it should of been another textbox. I have a chart on the page that uses the date from the date_select text box so when it changed the whole page crashed. I have now fixed this.

passing a string through to SQL via stored procedure using datetime

3 Answers3