dates again

dhoward · August 10th, 2007, 01:35 PM

I'm getting this error and I don't know where it's coming from.

System.InvalidCastException: Cast from string "" to type 'Date' is not valid.

  Here's the code...I know it has something to do with the code that highlighted red, because when I comment it out, it works, but I just can't see where it's going wrong. Can someone please help me?

Sub InsertTempTables()

        'Sub proc that inserts info into temp tables

        'Production connection
        Dim DBConn As New SqlConnection(Application("DBConn")) 'Open a new connection
        Dim cmdInsertTempTables As New SqlCommand("spInsertTempFWPermitTables", DBConn)
        Dim sqldatenull As SqlDateTime
        sqldatenull = SqlDateTime.Null
        cmdInsertTempTables.CommandType = CommandType.StoredProcedure

        'Stored proc that inserts record into temp tables

        If lstApplicantMI.SelectedItem.Value = "Select" Then
            lstApplicantMI.SelectedItem.Value = ""
        End If

        Try
            strrdate = txtrdate.Text
            strddate = txtddate.Text
            strdisplaydate = txtdisplaydate.Text
            strlname = txtApplicantLastName.Text
            strfname = txtApplicantFirstName.Text
            strMI = lstApplicantMI.SelectedItem.Value
            strhomeph = txtHomePhone1.Value + txtHomePhone2.Value + txtHomePhone3.Value
            strworkph = txtBusPhone1.Value + txtBusPhone2.Value + txtBusPhone3.Value
            strPCPhone = txtPurchaseCoPhone1.Value + txtPurchaseCoPhone2.Value + txtPurchaseCoPhone3.Value
            dtdob = txtDOB1.Value + "/" + txtDOB2.Value + "/" + txtDOB3.Value
            strReadyDate = txtreadystand.Text
            strSSN = txtSSN1.Value + txtSSN2.Value + txtSSN3.Value
            strAddr1 = txtMailingAddress1.Text
            strAddr2 = txtMailingAddress2.Text
            strCity = txtMailingCity.Text
            strState = lstMailingState.SelectedItem.Value
            strZip = txtMailingZip.Text
            strConame = txtBusinessName.Text
            strCoAddr1 = txtBusAddress1.Text
            strCoAddr2 = txtBusAddress2.Text
            strCoCity = txtBusCity.Text
            strCostate = lstBusState.SelectedItem.Value
            strCoCounty = lstBusCounty.SelectedItem.Value
            strClass = ddlFWClass.SelectedItem.Value
            strSalesID = txtSalesTaxNo.Text
            'If ddlFWClass.SelectedItem.Value = "CP" Then
            ' If ddlFWClass.SelectedItem.Value = "PD" Then
            ' strReason = ""
            ' Else
            ' strReason = optAppReason.SelectedItem.Value.ToString
            ' End If
            'End If
            strPCName = txtPurchaseCoName.Text
            strPCAddr1 = txtPurchaseCoAddress1.Text
            strPCaddr2 = txtPurchaseCoAddress2.Text
            strPCCity = txtPurchaseCoCity.Text
            strPCState = lstPurchaseCoState.SelectedItem.Value
            strPCZip = txtPurchaseCoZip.Text
            strrtime = txtrtime.Text
            strdtime = txtdtime.Text
            strdisplaytime = txtdisplaytime.Text
            strsponsor = txtsponsor.Text
            strconductor = txtconductor.Text
            strFWUserID = Session("FWUserID")
            strTransactionNumber = Session("TransactionNumber")

            DBConn.Open()

            With cmdInsertTempTables.Parameters

                .Add("@vchmiddleInitial", strMI)
                .Add("@vchrtime", strrtime)
                .Add("@vchdtime", strdtime)
                .Add("@vchdisplaytime", strdisplaytime)
                .Add("@vchLastname", strlname)
                .Add("@vchfirstname", strfname)
                .Add("@dtdob", dtdob)
                .Add("@vchSSN", strSSN)
                .Add("@vchApplicantAddress1", strAddr1)
                .Add("@vchApplicantAddress2", strAddr2)
                .Add("@vchApplicantCity", strCity)
                .Add("@vchApplicantState", strState)
                .Add("@vchApplicantZip", strZip)
                .Add("@vchApplicantHomePhone", strhomeph)
                .Add("@vchApplicantWorkPhone", strworkph)
                .Add("@vchcompanyname", strConame)
                .Add("@vchcompanyaddress1", strCoAddr1)
                .Add("@vchcompanyaddress2", strCoAddr2)
                .Add("@vchcompanycity", strCoCity)
                .Add("@vchcompanystate", strCostate)
                .Add("@vchcounty", strCoCounty)
                .Add("@vchFWClass", strClass)
                .Add("@vchSalesID", strSalesID)
                '.Add("@vchApplicationreason", strReason)
                .Add("@vchsponsor", strsponsor)
                .Add("@vchconductor", strconductor)
                .Add("@vchFWUserID", strFWUserID)
                .Add("@vchTransactionNumber", strTransactionNumber)
                .Add("@vchPurCoName", strPCName)
                .Add("@vchPurcoaddress1", strPCAddr1)
                .Add("@vchPurcoaddress2", strPCaddr2)
                .Add("@vchPurcocity", strPCCity)
                .Add("@vchPurcoState", strPCState)
                .Add("@vchPurcoZip", strPCZip)
                .Add("@vchPurcophone", strPCPhone)
                '.Add("@dtentered", txttodaydate.Text)

                If txttodaydate.Text = "" Then
                    .Add("@dtentered", sqldatenull)
                Else
                    .Add("@dtentered", txttodaydate.Text)
                End If

                If strrdate = "" Then
                    .Add("@dtrdate", sqldatenull)
                Else
                    .Add("@dtrdate", strrdate)
                End If

                If strReadyDate = "" Then
                    .Add("@dtStandReadyDate", sqldatenull)
                Else
                    .Add("@dtStandReadyDate", strReadyDate)
                End If

                If strdisplaydate = "" Then
                    .Add("@dtdisplaydate", sqldatenull)
                Else
                    .Add("@dtdisplaydate", strdisplaydate)
                End If

                If strddate = "" Then
                    .Add("@dtddate", sqldatenull)
                Else
                    .Add("@dtddate", strddate)
                End If

                If strReason = "" Then
                    .Add("@vchApplicationReason", "")
                Else
                    .Add("@vchApplicationReason", strReason)
                End If

                Dim strAmount As String
                Dim date1 As Date
                Dim date4 As Date
                Dim date3 As Date
                Dim strDateDiff, strdatediff2
                date1 = txttodaydate.Text
                date4 = txtddate.Text
                date3 = txtdisplaydate.Text
                strDateDiff = (DateDiff("d", date1, date4))
                strdatediff2 = (DateDiff("d", date1, date3))
                If ddlFWClass.SelectedItem.Value = "D" Then
                    strAmount = 2000
                End If

                If ddlFWClass.SelectedItem.Value = "M" Then
                    strAmount = 2000
                End If

                If ddlFWClass.SelectedItem.Value = "R" Then
                    strAmount = 200
                End If

                If ddlFWClass.SelectedItem.Value = "W" Then
                    strAmount = 700
                End If

                If ddlFWClass.SelectedItem.Value = "SR1" Then
                    strAmount = 100
                End If

                If ddlFWClass.SelectedItem.Value = "SR2" Then
                    strAmount = 200
                End If

                .Add("@mAmount", CDbl(strAmount))

                If ddlFWClass.SelectedItem.Value = "PD" Then
                    If strDateDiff < 10 Then
                        strAmount = 100
                    Else
                        strAmount = 50
                    End If
                End If

                If ddlFWClass.SelectedItem.Value = "CP" Then
                    If strdatediff2 < 10 Then
                        strAmount = 200
                    Else
                        strAmount = 100
                    End If
                End If
            End With
            cmdInsertTempTables.ExecuteNonQuery()
            DBConn.Close()

        Catch er As Exception
            LogError(er.ToString, "Application")
            Exit Try
        Finally
            DBConn.Close()
        End Try
    End Sub

gbianchi · August 10th, 2007, 01:47 PM

Hi there.. the error is telling you what is wrong:

Cast from string "" to type 'Date' is not valid. Means that one of the string that you are trying to convert to date is empty, therefor is can't be converted to date... you will have to check for empty string before convert.. also you should trying a cast it in a try catch (or any other method) to avoid bad syntaxis string...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

dhoward · August 10th, 2007, 02:25 PM

The thing that makes it difficult is that I have 3 date textboxes and depending on what I choose from the dropdown list, I'll either enter dates for one or two textboxes. I don't know what's wrong with the datediff code but it's really throwing me off. If it's commented out, it works perfectly but the datediff code throws an error. And I've entered breakpoints, rewritten codes, and everything. I did notice that it breaks at the data the not needed and throws the error. Like if I select PD, it'll break at date3= txtdisplaydate.text. If I select CP, it'll break at date4 = txtddate.text.

gbianchi · August 10th, 2007, 02:31 PM

well... you will have to execute the datediff depending on which values you are inserting.. if you don't insert all the dates all the time, how do you expect the Vb to cast the values???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

dhoward · August 15th, 2007, 01:34 PM

But what if I don't need them all the time? What if I just need two dates for a particular selection? shouldn't the other go in as null?

gbianchi · August 15th, 2007, 01:47 PM

you will have to add the logic to cast the dates you need and to avoid casting invalid ones..
I don't remember if date object has a tryparse...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

dhoward · August 15th, 2007, 01:55 PM

Could you give me an example please?

gbianchi · August 15th, 2007, 02:07 PM

well.. according to your code, date 1 always come and date 3 and 4 only comes sometimes, you could check when they come and execute the code according to the date that has arrive.. what kind of example are you looking for??

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

dhoward · August 20th, 2007, 01:37 PM

so you don't think this part would be good enough.

If strrdate = "" Then
                    .Add("@dtrdate", sqldatenull)
                Else
                    .Add("@dtrdate", strrdate)
                End If
                If strReadyDate = "" Then
                    .Add("@dtStandReadyDate", sqldatenull)
                Else
                    .Add("@dtStandReadyDate", strReadyDate)
                End If

                If strdisplaydate = "" Then
                    .Add("@dtdisplaydate", sqldatenull)
                Else
                    .Add("@dtdisplaydate", strdisplaydate)
                End If

                If strddate = "" Then
                    .Add("@dtddate", sqldatenull)
                Else
                    .Add("@dtddate", strddate)
                End If

I meant an example using either my code or your code.

gbianchi · August 21st, 2007, 07:20 AM

Don't your problems where in the datediff functions???
all the code you show me is how to save the data to the parameters.. your logic there looks good...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========