cannot convert string to date

Question

Hello stack overflow residents! this is my first post and i'm hoping to receive some help. I've searched but because I'm still very new, i was not able to full find/understand my answer.

I keep encountering this error:

Message: Conversion from string "" to type 'Date' is not valid. File: ~/reports/pendingshipments.aspx Function: btnExportXls_Click Stack Trace: at Microsoft.VisualBasic.CompilerServices.Conversions.ToDate(String Value) at reports_default.btnExportXls_Click(Object sender, EventArgs e) in C:\Users\jet.jones\Documents\ERIRoot\ERITitan\ERITitan.ssa\Web Application\reports\pendingshipments.aspx.vb:line 75

Here is my code:

on App_code

**Public Function Reports_PendingShipments(ByVal intClientID As Integer, ByVal strMinDate As Date?, ByVal strMaxDate As Date?, ByVal xmlSiteID As String) As DataTable
        '=================================================================================
        ' Author:       Jet Jones
        ' Create date:  2013.05.28
        ' Description:  Returns a data table with pending shipments for the sites specified
        '=================================================================================
        Dim objConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Titan").ToString)
        Dim cmdGet As New SqlCommand("spReports_PendingShipments", objConn)
        Dim parClientID As New SqlParameter("@ClientID", SqlDbType.Int)
        Dim parMinDate As New SqlParameter("@MaxDate", IIf(Not strMinDate.HasValue, DBNull.Value, strMinDate))
        Dim parMaxDate As New SqlParameter("@MaxDate", IIf(Not strMaxDate.HasValue, DBNull.Value, strMaxDate))
        Dim parSiteID As New SqlParameter("@Sites", SqlDbType.Xml)
        Dim objAdapter As New SqlDataAdapter(cmdGet)
        Dim objTable As New DataTable
        parClientID.Value = intClientID
        parMinDate.Value = strMinDate
        parMaxDate.Value = strMaxDate
        parSiteID.Value = xmlSiteID
        'set up the command object
        cmdGet.Connection = objConn
        cmdGet.CommandType = CommandType.StoredProcedure
        'add the parameters
        cmdGet.Parameters.Add(parClientID)
        cmdGet.Parameters.Add(parMinDate)
        cmdGet.Parameters.Add(parMaxDate)
        cmdGet.Parameters.Add(parSiteID)
        'open the connection
        objConn.Open()
        'execute the query and fill the data table
        objAdapter.Fill(objTable)
        'return the data table
        Reports_PendingShipments = objTable
        'clean up
        objConn.Close()
        objConn = Nothing
    End Function**

my aspx.vb page calls this function this way (Get the values from the query):

objTable = Reports_PendingShipments(ucClientSearch.Value,
    txtMinDate.Text, txtMaxDate.Text, strSites)

I'm passing the variable strSites because the website permissions allow for users to have access to one or more site locations, and if a report is run and the user selects "All Sites" from the dropdown, I only want to send the sites they have permissions to via XML.

If I'm missing any information please let me know! anyone's prompt response is so greatly appreciated.

Giving us a date format that you're trying to convert would be a start. — Gabe
– Gabe, Commented May 30, 2013 at 15:36
The error message is pretty specific. Somewhere you have an empty string you are trying to make into a date... What is the exact contents of line 75 in the source code? — Sparky
– Sparky, Commented May 30, 2013 at 15:38
Yes. the problem is that i don't get the error if the user enters dates. if they leave the date fields blank it tries to pass a blank string in as a date and i cant figure out how to solve that — JettyJetty
– JettyJetty, Commented May 30, 2013 at 15:39
The error message seems to suggest the function with the problem is btnExportXls_Click but you seem to have given us a different function. Also it gives a line number and letting us know which line of the code you have given is that line would be useful... — Chris
– Chris, Commented May 30, 2013 at 15:40

Sparky · Accepted Answer · 2013-05-30 15:43:03Z

3

The problem is that your code is expecting empty dates to be NULL, it doesn't check for empty strings. You need something like this:

if len(strMinDate)=0 then
   strMinDate = "01/01/1980"
end

Not sure what you want to default the minimum date to, but you need to add code similar to the IF statement above

Be sure to add this code prior to using the variable a few lines later...

answered May 30, 2013 at 15:43

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

You are almost certainly right though I note that the problem doesn't look like it is in the above code so not the "strMinDate" variable above (which looks like it is confusingly actualyl a nullable date).

IUnknown · Accepted Answer · 2013-05-30 15:42:20Z

0

First of all, you adding MaxDate parameter twice:

Dim parMinDate As New SqlParameter("@MaxDate", IIf(Not strMinDate.HasValue, DBNull.Value, strMinDate))
Dim parMaxDate As New SqlParameter("@MaxDate", IIf(Not strMaxDate.HasValue, DBNull.Value, strMaxDate))

And moreover, then you setting parameters values wuthout check for HasValue:

parMinDate.Value = strMinDate
parMaxDate.Value = strMaxDate

Remove these lines and fix min date parameter name

answered May 30, 2013 at 15:42

IUnknown

22.5k4 gold badges41 silver badges71 bronze badges

Collectives™ on Stack Overflow

cannot convert string to date

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related