none
SQL Reader Error "Specified cast is not valid." RRS feed

  • Question

  • This error is received while trying read the column birth_date which is configured as datetime2.  None of the other columns produce this error.

     Dim Select1 = "SELECT address1, address2, city, state, zip, liv_since, " &
                          "addr_mail, add2_mail, city_mail, state_mail, zip_mail, " &
                          "addr_prev, add2_prev, city_prev, state_prev, zip_prev, since_prev, to_prev, " &
                          "language, bil_home, langspok, langspok_o, ss_no, ss_no_o, birth_date, birth_da_o, religion, relig_o, " &
                          "race, race_o, gender, gender_o, education, educ_s, mar_status, income " &
                          "FROM applicant " &
                          "WHERE acct_no = " & "'" & Acct_No & "'"
    
            ' Get data from applicant and save in the foster parent table
            Dim SQLDataReader1 As SqlDataReader
    
            Dim HasRows = False
            Dim MoreRows = True
    
            Dim Connection1 As New SqlConnection(ConnectionString)
            Dim Command1 As New SqlCommand(Select1, Connection1)
    
            ' Open connection if closed
            If Connection1.State = ConnectionState.Closed Then Connection1.Open()
    
            SQLDataReader1 = Command1.ExecuteReader()
    
            ' Determine if reader contains any rows, if not do not read.
            HasRows = SQLDataReader1.HasRows
    
            SQLDataReader1.Read()
    
            Do While MoreRows = True
    
                If IsDBNull(SQLDataReader1("address1")) Then Address1 = String.Empty Else Address1 = SQLDataReader1("address1")
                If IsDBNull(SQLDataReader1("address2")) Then Address2 = String.Empty Else Address2 = SQLDataReader1("address2")
                If IsDBNull(SQLDataReader1("city")) Then City = String.Empty Else City = SQLDataReader1("city")
                If IsDBNull(SQLDataReader1("state")) Then State = String.Empty Else State = SQLDataReader1("state")
                If IsDBNull(SQLDataReader1("zip")) Then Zip = String.Empty Else Zip = SQLDataReader1("zip")
                If IsDBNull(SQLDataReader1("liv_since")) Then Liv_Since = sqlDateNull Else Liv_Since = SQLDataReader1("liv_since")
    
                If IsDBNull(SQLDataReader1("addr_mail")) Then Addr_Mail = String.Empty Else Addr_Mail = SQLDataReader1("addr_mail")
                If IsDBNull(SQLDataReader1("add2_mail")) Then Add2_Mail = String.Empty Else Add2_Mail = SQLDataReader1("add2_mail")
                If IsDBNull(SQLDataReader1("city_mail")) Then City_Mail = String.Empty Else City_Mail = SQLDataReader1("city_mail")
                If IsDBNull(SQLDataReader1("state_mail")) Then State_Mail = String.Empty Else State_Mail = SQLDataReader1("state_mail")
                If IsDBNull(SQLDataReader1("zip_mail")) Then Zip_Mail = String.Empty Else Zip_Mail = SQLDataReader1("zip_mail")
    
                If IsDBNull(SQLDataReader1("addr_prev")) Then Addr_Prev = String.Empty Else Addr_Prev = SQLDataReader1("addr_prev")
                If IsDBNull(SQLDataReader1("add2_prev")) Then Add2_Prev = String.Empty Else Add2_Prev = SQLDataReader1("add2_prev")
                If IsDBNull(SQLDataReader1("city_prev")) Then City_Prev = String.Empty Else City_Prev = SQLDataReader1("city_prev")
                If IsDBNull(SQLDataReader1("state_prev")) Then State_Prev = String.Empty Else State_Prev = SQLDataReader1("state_prev")
                If IsDBNull(SQLDataReader1("zip_prev")) Then Zip_Prev = String.Empty Else Zip_Prev = SQLDataReader1("zip_prev")
    
                If IsDBNull(SQLDataReader1("since_prev")) Then Since_Prev = sqlDateNull Else Since_Prev = SQLDataReader1("since_prev")
                If IsDBNull(SQLDataReader1("to_prev")) Then To_Prev = sqlDateNull Else To_Prev = SQLDataReader1("to_prev")
    
                If IsDBNull(SQLDataReader1("language")) Then Language = String.Empty Else Language = SQLDataReader1("language")
                If IsDBNull(SQLDataReader1("bil_home")) Then Bil_home = String.Empty Else Bil_home = SQLDataReader1("bil_home")
                If IsDBNull(SQLDataReader1("langspok")) Then Langspok = String.Empty Else Langspok = SQLDataReader1("langspok")
                If IsDBNull(SQLDataReader1("langspok_o")) Then Langspok_o = String.Empty Else Langspok_o = SQLDataReader1("langspok_o")
                If IsDBNull(SQLDataReader1("ss_no")) Then SS_no = String.Empty Else SS_no = SQLDataReader1("ss_no")
                If IsDBNull(SQLDataReader1("ss_no_o")) Then SS_no_o = String.Empty Else SS_no_o = SQLDataReader1("ss_no_o")
    
                ' Line below produces error
                If IsDBNull(SQLDataReader1("birth_date")) Then Birth_Date = sqlDateNull Else Birth_Date = SQLDataReader1("birth_date")
                If IsDBNull(SQLDataReader1("birth_da_o")) Then Birth_da_o = sqlDateNull Else Birth_da_o = SQLDataReader1("birth_da_o")
                If IsDBNull(SQLDataReader1("religion")) Then Religion = String.Empty Else Religion = SQLDataReader1("religion")
                If IsDBNull(SQLDataReader1("relig_o")) Then Relig_O = String.Empty Else Relig_O = SQLDataReader1("relig_o")
                If IsDBNull(SQLDataReader1("race")) Then Race = String.Empty Else Race = SQLDataReader1("race")
                If IsDBNull(SQLDataReader1("race_o")) Then Race_O = String.Empty Else Race_O = SQLDataReader1("race_o")
                If IsDBNull(SQLDataReader1("gender")) Then Gender = String.Empty Else Gender = SQLDataReader1("gender")
                If IsDBNull(SQLDataReader1("gender_o")) Then Gender_O = String.Empty Else Gender_O = SQLDataReader1("gender_o")
                If IsDBNull(SQLDataReader1("education")) Then Education = 0 Else Education = SQLDataReader1("education")
                If IsDBNull(SQLDataReader1("educ_s")) Then Educ_S = 0 Else Educ_S = SQLDataReader1("educ_s")
                If IsDBNull(SQLDataReader1("mar_status")) Then Mar_Status = String.Empty Else Mar_Status = SQLDataReader1("mar_status")
                If IsDBNull(SQLDataReader1("income")) Then Income = 0 Else Income = SQLDataReader1("income")
    
                ' If no more rows to read in reader1, exit do loop
                MoreRows = SQLDataReader1.Read
            Loop
    


    ISV using VB.net and SQL Server

    Monday, September 16, 2019 2:27 PM

Answers

  •  


    Found the solution to this cast error:

    The code below produces an error when birth_date contains a date.

      If IsDBNull(SQLDataReader1("birth_date")) Then Birth_Date = sqlDateNull Else Birth_Date = SQLDataReader1("birth_date")

    Modifying the code using the convert function resolves the issue.

     If IsDBNull(SQLDataReader1("birth_date")) Then Birth_date = sqlDateNull Else Birth_date = Convert.ToDateTime(SQLDataReader1("birth_date"))

    Hoping that some one can benefit from what I have learned.


    ISV using VB.net and SQL Server

    • Marked as answer by Jeff07 Monday, October 14, 2019 4:42 PM
    Monday, October 14, 2019 4:42 PM

All replies

  • Hello,

    You would not have this exception when using parameters for a command object.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, September 16, 2019 2:55 PM
    Moderator
  • IMO, the variable or the data coming from the table column is not a true date type. Is that table column a true date type and the variable s true date type?

    If the date is null data  is the date variable allowing null data to be assigned to it?

    Also, you should learn how to use the tennary operator and get rid of the if/else.

    https://www.c-sharpcorner.com/blogs/ternary-operator-in-vb-net1


    • Edited by DA924x Monday, September 16, 2019 3:19 PM
    Monday, September 16, 2019 3:09 PM
  • All of the date columns are datetime2(7) and all of the date variables are SqlDateTime.  Only the birth_date column produces the error. I don't get it.

    Thanks for the tip about ternary operator.


    ISV using VB.net and SQL Server

    Monday, September 16, 2019 3:26 PM
  • "You would not have this exception when using parameters for a command object."

    Ok, but what should I use?  Can you please provide a simple example?


    ISV using VB.net and SQL Server

    Monday, September 16, 2019 3:30 PM
  • "You would not have this exception when using parameters for a command object."

    Ok, but what should I use?  Can you please provide a simple example?


    ISV using VB.net and SQL Server

    Parameter usage https://github.com/karenpayneoregon/WindowsFormsMasterDetailWithSqlServer/blob/master/DataOperations_vb/Operations.vb#L106

    Then for reading data with a data reader.

    https://github.com/karenpayneoregon/WindowsFormsMasterDetailWithSqlServer/blob/master/DataOperations_vb/Operations.vb#L33

    Note the use of methods to read data in a while statement for GetInt32, there is GetDateTime here


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Jeff07 Monday, September 23, 2019 9:45 PM
    • Unmarked as answer by Jeff07 Monday, October 14, 2019 4:42 PM
    Monday, September 16, 2019 4:21 PM
    Moderator
  • Thank you.  Will investigate and report back.


    ISV using VB.net and SQL Server

    Monday, September 16, 2019 4:33 PM
  • Maybe, you need to do a Directcast.

    https://forums.asp.net/t/1235714.aspx?Why+Specified+cast+is+not+valid+

    Monday, September 16, 2019 4:58 PM
  • Jeff,

    .Net does not know a type DateTime2, it was one of the newer things in 2007 with SQL server to allow dates older than 1753,

    In Net DateTime is a struct which never can be nothing (Null in C type languages). You have to initialize it with a valid DateTime for instance BirthDate = new Date(1,1,1). Some use for this a nullable DateTime, however, that takes in practise even more code, but that is your choice.  

    DBNull.Value is a member to test the Null Allowed situation in SQL Server if there is no value used. 


    Success
    Cor





    • Edited by Cor Ligthert Wednesday, September 18, 2019 2:47 PM mistake 1857 had to be 1753
    Monday, September 16, 2019 5:06 PM
  • All date fields in my sample code work fine except for Birth_Date.  Not sure if your comments about .NET are applicable to my situation.  Also, my app rejects any date prior to 1753 which is why I changed to DateTime2.

    Thank you for your thoughts.


    ISV using VB.net and SQL Server

    Tuesday, September 17, 2019 6:37 PM
  • All date fields in my sample code work fine except for Birth_Date.  Not sure if your comments about .NET are applicable to my situation.  Also, my app rejects any date prior to 1753 which is why I changed to DateTime2.

    Thank you for your thoughts.


    ISV using VB.net and SQL Server

    Have you tried dummying up a query with the date value which is failing in code and run it either via Server Explorer (create new query) or from SSMS (SQL-Server Management Studio)? If it fails there it will fail in code and if it works there it will work in code.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 18, 2019 2:18 AM
    Moderator
  • All date fields in my sample code work fine except for Birth_Date.  Not sure if your comments about .NET are applicable to my situation.  Also, my app rejects any date prior to 1753 which is why I changed to DateTime2.

    Thank you for your thoughts.


    ISV using VB.net and SQL Server

    Can you show where you did change in VB the DateTime to DateTime2. 

    I'm unable to do that in Visual Studio 2019. 

    Or did you not read my reply? 


    Success
    Cor

    Wednesday, September 18, 2019 2:47 PM
  • SSMS was used to change date fields from DateTime to DateTime2.


    ISV using VB.net and SQL Server

    Wednesday, September 18, 2019 3:43 PM
  • SSMS was used to change date fields from DateTime to DateTime2.


    ISV using VB.net and SQL Server

    Yes but that is not VB. DateTime2 in SQL Server is exactly the same as DateTime in VB.

    However, I tried to describe it in my message, if it is unclear, than tell me what is unclear.

    I guess that when you put Option Strict On you get a clear message on this 2 code rows.

              If IsDBNull(SQLDataReader1("birth_date")) Then Birth_Date = sqlDateNull Else Birth_Date = SQLDataReader1("birth_date")
              If IsDBNull(SQLDataReader1("birth_da_o")) Then Birth_da_o = sqlDateNull Else Birth_da_o = SQLDataReader1("birth_da_o")
    


    Success
    Cor


    • Edited by Cor Ligthert Wednesday, September 18, 2019 3:50 PM
    Wednesday, September 18, 2019 3:47 PM
  • I have decided to take a different approach along the lines of the code below.

     Command1.CommandText = "INSERT INTO FPRecertTemp (" & ColumnsCopied & ")  " &
                          "Select " & ColumnsCopied & " " &
                          "FROM AppCert " &
                          "WHERE FACCT = " & "'" & Acct_NO & "'"


    ISV using VB.net and SQL Server

    Monday, September 23, 2019 9:45 PM
  •  


    Found the solution to this cast error:

    The code below produces an error when birth_date contains a date.

      If IsDBNull(SQLDataReader1("birth_date")) Then Birth_Date = sqlDateNull Else Birth_Date = SQLDataReader1("birth_date")

    Modifying the code using the convert function resolves the issue.

     If IsDBNull(SQLDataReader1("birth_date")) Then Birth_date = sqlDateNull Else Birth_date = Convert.ToDateTime(SQLDataReader1("birth_date"))

    Hoping that some one can benefit from what I have learned.


    ISV using VB.net and SQL Server

    • Marked as answer by Jeff07 Monday, October 14, 2019 4:42 PM
    Monday, October 14, 2019 4:42 PM