none
Checking for existing date record failing RRS feed

  • Question

  • I am using a function which is to pass in a string DateTime to a SQL Command and return if there are records or not.  The date string which I pass in is formatted like this: "05-Sep-18, 16:24:58".  The table in SQL which I am selecting the data from has the field set as a DateTime field. The code I am using is as follows:

     Public Function SqlExists() As Boolean
            ' What if that records already exists in the database?
    
            Dim recFile As String = ProcessFiles.HeaderTable.Rows(7).Item("Values")
            Dim cleaned As String = recFile.Replace(",", " ")
            Dim tmpDate As DateTime = DateTime.Parse(cleaned)
    
            'Dim recFile As String = dataFile
            If recFile <> Nothing Then
                Dim builder1 As New SqlConnectionStringBuilder(ConnectionString)
                builder1.InitialCatalog = "ctc_custom"
                Dim cn As New SqlConnection(builder1.ConnectionString)
    
                ' Need to build the TestID as a unique value and save it somehow
                Try
    
                    cn.Open()
                    Using cmd As New SqlCommand("SELECT * FROM [ctc_custom].[dbo].[HPLC_Params] WHERE [Injection Date] = @recFile", cn) With {.CommandType = CommandType.Text}
    
                        cmd.Parameters.AddWithValue("@recFile", tmpDate)
    
                        Try
                            Using reader As SqlDataReader = cmd.ExecuteReader()
                                If reader.HasRows Then
                                    ' Record exists do not add
                                    Return True
                                Else
                                    Return False
                                End If
                            End Using
                        Catch ex As Exception
                            ErrorLogger.WriteToErrorLog("Errors", ex.Message, ex.StackTrace)
                        End Try
                    End Using
                    cn.Close()
                Catch ex As Exception
                    ErrorLogger.WriteToErrorLog("Errors", ex.Message, ex.StackTrace)
                End Try
            End If
        End Function

    I am wondering if this code will work properly or do you see any issues with it where it would always return a True value?  I can get the validation to work 3 times in a row and then it returns true always.  Please suggest better ways to do this validation.
    Wednesday, September 12, 2018 6:09 PM

Answers

  • If the field [Injection Date] is a DateTime field in SQL-Server and culture is EN I'm surprised it even works. The conversion should fail.

    While this would be fine which excludes the comma.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Cam Evenson Thursday, September 13, 2018 1:49 PM
    Wednesday, September 12, 2018 6:38 PM
    Moderator

All replies

  • If the field [Injection Date] is a DateTime field in SQL-Server and culture is EN I'm surprised it even works. The conversion should fail.

    While this would be fine which excludes the comma.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Cam Evenson Thursday, September 13, 2018 1:49 PM
    Wednesday, September 12, 2018 6:38 PM
    Moderator
  • When I run the application the value which tmpDate receives is #7/26/2018 02:57:13 AM  if the passed in date is "26-Jul-18, 2:57:13"

    So technically all I would need to do is use the value that the cleaned variable returns rather than the value which tmpDate returns and it should work? 
    Wednesday, September 12, 2018 7:14 PM