none
SQL call not authenticating the record RRS feed

  • Question

  • I verify that a record does not exist already in the database by using a SQL statement where I pass in a unique date variable. However, it appears that the validation is not working correctly.  The filed which the SQL select statement is referencing is formatted as a DateTime declaration in the SQL table. 

    Any help or suggestions would be appreciated.  The variable recFile is assigned to a row in a DataTable

    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 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", recFile)
    
                        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


    Tuesday, November 6, 2018 4:36 AM

Answers

  • If [Injection Date] is a datetime column, then try using a corresponding variable in VB:

       Dim recFile2 As DateTime = CDate(recFile)

    If the time part is not needed, then:

       refFile2 = recFile2.Date

    Then use this value in your query.

    • Marked as answer by Cam Evenson Tuesday, November 6, 2018 3:07 PM
    Tuesday, November 6, 2018 5:29 AM

All replies

  • If [Injection Date] is a datetime column, then try using a corresponding variable in VB:

       Dim recFile2 As DateTime = CDate(recFile)

    If the time part is not needed, then:

       refFile2 = recFile2.Date

    Then use this value in your query.

    • Marked as answer by Cam Evenson Tuesday, November 6, 2018 3:07 PM
    Tuesday, November 6, 2018 5:29 AM
  • I don't see how a timestamp can be used as some kind of indication that a record can be duplicated in a database table. 
    Tuesday, November 6, 2018 10:56 AM
  • I am sorry, I did not clarify how the system works.  So the timestamps are generated by a laboratory instrument during the test.  The instrument data is then written to a database table using the application I am building.

    But what happens is the Laboratory analyst copy some of the past tests into the current test folder and use them to fill in their test sheets at night so apparently they can sleep.  And thus why we have to check to see if a injection time already exists.

     

    Tuesday, November 6, 2018 2:58 PM