error in update command

Bahar Jalali

hi
i have a source code that check some email address and deactive emails that are invalid.
it's my update command

Dim com As New SqlCommand("Update Newsletter_Emails Set Active='False' Where (Email IN (" & Error_Email & "))", con)

also i have tested some option like ... Where (Email IN " & Error_Email & ")", con)
and ... Where Email IN '" & Error_Email & "'", con)

but any way it has some error :
Unclosed quotation mark after the character string ''))'.
Incorrect syntax near ''))'.

any suggestion?
thanks in advance

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
As Anil suggests, it probably would be better to split the items in the IN list and use a separate variable (or parameter) for each.

However, it is still possible to do it in a composite way if Error_Email contains something like this:

Dim Error_EMail As String = "'[email protected]', '[email protected]', '[email protected]'"

The following UPDATE statement should then work:

Dim com As New SqlCommand("Update Newsletter_Emails Set Active='False' Where Email IN (" & Error_Email & ")", con)
Anil Kumar14y ago
So Error_Email is containing the list of incorrect email IDs? If there are many IDs then it will give you error as you need to split it to give many items for your IN clause.
Bahar Jalali14y ago
Dear Vulpes, it contains every emails that users add in their registration, and some users add wrong emails. something like [email protected] or [email protected] or [email protected] and so on.
tan hongyou14y ago
Let try this one... Dim com As New SqlCommand("Update Newsletter_Emails Set Active='False' Where Email IN ('" & Error_Email & "')", con)
brunda k14y ago
Dim mystr As String = "Update Newsletter_Emails Set Active='False' Where Email IN '" & Error_Email & "'"
In the above code Email IN '" & Error_Email & "'"

replace with

Email IN ('" & Error_Email & "')"
Vulpes14y ago
What does the variable Error_Email contain?
Bahar Jalali14y ago
no one don't work!
every my try don't work! why?
my last try is :
Dim mystr As String = "Update Newsletter_Emails Set Active='False' Where Email IN" & Error_Email

but don't work

error is :
Incorrect syntax near ''.
Unclosed quotation mark after the character string '''.

also i tried this one :
Dim mystr As String = "Update Newsletter_Emails Set Active='False' Where Email IN '" & Error_Email & "'"

and get this error :
Unclosed quotation mark after the character string '','''.
Incorrect syntax near '','''.
Vulpes14y ago
The error message suggests that you might have two single quote characters, rather than one double quote character, after the double right parentheses:

Unclosed quotation mark after the character string ''))'.

Although I'd have thought this would have been noticeable, I'd check it anyway.
tan hongyou14y ago
Try

Dim com As New SqlCommand("Update Newsletter_Emails Set Active='False' Where Email IN (" & Error_Email & ")", con)