error in update command

Question

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

Vulpes · Answer

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 = '12102011@gmx.com' '23kj4h2@0sx.ru' '543gfgfhj@gmail.com' 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 Kumar · Answer

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 Jalali · Answer

Dear Vulpes it contains every emails that users add in their registration and some users add wrong emails. something like 12102011@gmx.com or 23kj4h2@0sx.ru or 543gfgfhj@gmail.com and so on.

tan hongyou · Answer

Let try this one... Dim com As New SqlCommand(Update Newsletter_Emails Set Active='False' Where Email IN (' & Error_Email & ') con)

brunda k · Answer

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 & ')

Vulpes · Answer

What does the variable Error_Email contain?

Bahar Jalali · Answer

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 '''''.

Vulpes · Answer

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 hongyou · Answer

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