Answered by:
Checking for existing date record failing

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
- Marked as answer by Cam Evenson Thursday, September 13, 2018 1:49 PM
Wednesday, September 12, 2018 6:38 PM
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
- Marked as answer by Cam Evenson Thursday, September 13, 2018 1:49 PM
Wednesday, September 12, 2018 6:38 PM -
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