I have a query that takes as a parameter a list of dates. In VB.NET the dates are in a string
ArrayList and I am using the
String.Join() method to get them in a comma delimited list. The problem is when I do that double quotes are put at the start and end of the string and SQL complains about that (I think; see below). How can I get a list of date from a string
ArrayList without the quote.
My arraylist contains these values:
'2020-08-30' '2020-08-27' '2020-09-28' '2020-09-09' '2020-08-31' '2020-08-29'
when I join them using
String.Join(",", sDates) I get the following:
and when I use that in a parameter query it gets rejected.
comm.Parameters.AddWithValue("@dates", String.Join(",", sDates))
sql contains the following"
...where pj.ProjectName =@projectname And tcd.Date in (@dates)
Exact error I get is
System.Data.SqlClient.SqlException HResult=0x80131904 Message=Incorrect syntax near ','. Source=.Net SqlClient Data Provider
This error message:
Incorrect syntax near ','
Is not caused by the way you've done your IN. It is caused by something else, such as a misplaced comma in a select block:
SELECT , a FROM x ^
The way you've done your IN won't work either, because it's conceptually the same as writing this:
SELECT * FROM table WHERE dateColumn IN ('''2000-01-01'',''2000-02-01''')
There is no such date with a string value of
If you want to use IN in the style you're attempting here, you have to add a parameter per date value and set it up accordingly:
sqlCommand.CommandText = "SELECT * FROM table WHERE dateCol IN(" Dim p = 0 For Each d as DateTime in MyDateList sqlCommand.CommandText &= "@p" & i & "," 'concat param placeholder on sqlCommand.Parameters.AddWithValue("@p" & i, d) i += 1 Next d sqlCommand.CommandText = sqlCommand.CommandText.TrimEnd(","c) & ")" 'get rid of trailing comma and close the IN brackets
This will generate an sql like
SELECT * FROM table WHERE dateCol IN (@p0,@p1,@p2)
with 3 parameters, and a populated parameters collection. You've already been pointed to Joel's blog about AddWithValue, so I won't repeat it.. But i did want to say that the way you've presented your question implies you have a list of strings, not datetimes. You should definitely make sure your list has DateTimes in, and your db column should be a date based type, not a string based type
User contributions licensed under CC BY-SA 3.0