how to eliminate a list of items in sql query? using vb.net

Question

Hi membres
i want to make a query in vb.net and it dident work for me
in sql server the query work fine like this

 SELECT F_DOCENTETE.DO_Type, F_DOCENTETE.DO_Tiers, F_COMPTET.CT_Intitule, F_DOCENTETE.DO_Date, F_DOCENTETE.DO_Piece, F_DOCENTETE.DO_TotalHT, F_DOCENTETE.DO_TotalHTNet, F_DOCENTETE.DO_TotalTTC, F_COMPTET.CT_Contact, F_COMPTET.CT_Qualite, F_DOCENTETE.DO_Ref, F_DOCENTETE.CA_Num  
FROM F_DOCENTETE INNER JOIN F_COMPTET ON F_DOCENTETE.DO_Tiers = F_COMPTET.CT_Num  
WHERE ***F_DOCENTETE.DO_Tiers not in ('001','0014','0016')*** and F_DOCENTETE.DO_Type=13 order by DO_Tiers asc   

and in vb.net code like this :

 If cn.State = ConnectionState.Open Then  
            cn.Close()  
        End If  
        cn.Open()  
        Dim str As String = ListBox1.Items(0).ToString  
        For i = 1 To ListBox1.Items.Count - 1  
            str = str + "," + ListBox1.Items(i).ToString  
        Next  
  
      
            Dim cmdkk As New SqlCommand("SELECT F_DOCENTETE.DO_Type, F_DOCENTETE.DO_Tiers, F_COMPTET.CT_Intitule, F_DOCENTETE.DO_Date, F_DOCENTETE.DO_Piece, F_DOCENTETE.DO_TotalHT, F_DOCENTETE.DO_TotalHTNet, F_DOCENTETE.DO_TotalTTC, F_COMPTET.CT_Contact, F_COMPTET.CT_Qualite, F_DOCENTETE.DO_Ref, F_DOCENTETE.CA_Num  
FROM F_DOCENTETE INNER JOIN F_COMPTET ON F_DOCENTETE.DO_Tiers = F_COMPTET.CT_Num  
WHERE (F_DOCENTETE.DO_Tiers not in ('" &  str  & "')) AND F_DOCENTETE.do_type =13", cn)  
            Dim str77 As New SqlDataAdapter(cmdkk)  
            Dim tab As New DataTable  
            str77.Fill(tab)  
     
  
        DataGridView1.DataSource = tab  
        cn.Close()  

not work for me and i don't know why my code doesnt work
please help

Answer 1

Compare the two IN clauses. You will find that one has single-quote decorations, and the other does not. Single-quotes are required for text values.