DataAdapter Parameter for a multiple string to use with IN

what is the difference of it being a ingle string or a concatenation? after the concatenations, if you do it right, the result will be a single string, therefore it should work the same way.

here's an example in vb of how to add parameters to the Sqlcommand:
 

' Add the input parameter and set its properties.
Dim parameter As New SqlParameter()
parameter.ParameterName = "@CategoryName"
parameter.SqlDbType = SqlDbType.NVarChar
parameter.Direction = ParameterDirection.Input
parameter.Value = categoryName

' Add the parameter to the Parameters collection.
 command.Parameters.Add(parameter)

Select allOpen in new window

It doesn't though. I don't know how the parameter sends the string because of the quotes in it.
After all, if it's just one string, the variable has a value of:
Single String
but if it's more than one, it has a value of:
String1','String2','String3

I don't think the parameter is sending things correctly exactly because of the quotes in the string. I think it's converting it to:
String1,String2,String3
 But I don't see another way to do this, other than having one parameter for each value (which isn't a good option).

try performing a breakpoint and adding watches to those variables. also if you are able to, share some of your code, for us to better identify whats wrong with it

best regards

The code is quite big, so I'm presenting just important code lines:

      sqlDA.SelectCommand.Parameters.Add("@sTipo", SqlDbType.VarChar, 256).Value = ""
      Dim Tipo As String = Replace(ddl_Tipo.SelectedValue, "'", "")
      If Tipo = "Todas" Then
        Tipo = Replace(ddl_Tipo.Items(1).Text, "'", "")
        For a = 2 To ddl_Tipo.Items.Count - 1
          Tipo &= "','" & Replace(ddl_Tipo.Items(a).Text, "'", "")
        Next
      End If
      sqlDA.SelectCommand.Parameters("@sTipo").Value = Tipo

Select allOpen in new window

It reports back a value of (some more, but these 3 will do):
Assistência à Família','Baixa Por Acidente de Trabalho','Baixa Por Doença

Hum.. what exatly is not working then? does it build or is it a run time error? "but I can't seem to get it to work with parameters" what is the code failing to accomplysh specifically?

The code works fine. It just returns no values, when it should return values. The problem (I think) is that it should be translating into:
SELECT * FROM Table WHERE Field IN ('Assistência à Família','Baixa Por Acidente de Trabalho','Baixa Por Doença')
And it must be translating into:
SELECT * FROM Table WHERE Field IN ('Assistência à Família'',''Baixa Por Acidente de Trabalho'',''Baixa Por Doença')
or without quotes at all, not sure. Anyway, I can't figure a way around it, other than concatenation, or one variable per item.

Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Table WHERE Field IN (@sTipo)", New SqlConnection)
Dim sda As System.Data.SqlClient.SqlDataAdapter = New SqlDataAdapter(cmd)
Dim parameter As SqlParameter = New SqlParameter
parameter.ParameterName = "@sTipo"
parameter.SqlDbType = SqlDbType.NVarChar
parameter.Direction = ParameterDirection.Input
parameter.Value = "value"
cmd.Parameters.Add(parameter)

Select allOpen in new window

when you are using parameter then there is no problem with sql injection means,no problem with ' (''')

above code will work great and if not work then please give your inputs n your code.


Regards,
nishant joshi

nishantcomp2512, I already did in previous posts. As I said, works fine for just one value, trying to send many, won't work.

so your input are like this..

parameter.Value = "('String1','String2','String3')";
 or a different one?

As stated in https://www.experts-exchange.com/questions/27530388/DataAdapter-Parameter-for-a-multiple-string-to-use-with-IN.html?cid=1575&anchorAnswerId=37412645#a37412645
the parameter is assigned a value of :
Assistência à Família','Baixa Por Acidente de Trabalho','Baixa Por Doença

Surely your code:

If Tipo = "Todas" Then
        Tipo = Replace(ddl_Tipo.Items(1).Text, "'", "")
        For a = 2 To ddl_Tipo.Items.Count - 1
          Tipo &= "','" & Replace(ddl_Tipo.Items(a).Text, "'", "")
        Next
      End If

Select allOpen in new window

Should be

If Tipo = "Todas" Then
        Tipo = "'" & Replace(ddl_Tipo.Items(1).Text, "'", "")
        For a = 2 To ddl_Tipo.Items.Count - 1
          Tipo &= "','" & Replace(ddl_Tipo.Items(a).Text, "'", "")
        Next
        Tipo &= "'"
      End If

Select allOpen in new window

No, that doesn't help. Char parameters are sent to the database enclosed in quotes already. So, if I assign a value of "String" it actually sends 'String'. I used SQL Server Profiler to capture the actual data sent to the server, and the parameter is being sent as:
Assistência à Família'',''Baixa Por Acidente de Trabalho
which is why there is no hit.

Cluskitt, you're exactly right in what's happening, command parameters do not work as suggested above when you're using "in" values.
The single parameter will pass a SINGLE string to the database for the in clause, so it's searching for anything where the field name is 'Assistência à Família'',''Baixa Por Acidente de Trabalho'.

It's a little tricker, basically you have to build a dynamic command object from the number of parameters that are going to be used in the "in" clause, and add them each one by one:

//assuming a sqlconnection called conn...
SqlCommand cmd = new SqlCommand() { Connection = conn };  //NOT setting the command text yet
string[] fields = new string[] { "string 1", "string 2", "string 3" };
List<string> parameterNames = new List<string>();   //this will build up the parameter names (@px) to put into the dynamic command text

for (int i = 0; i < fields.Length; i++)
{
    string name = string.Format("@p{0}", i);
    parameterNames.Add(name);   //create the parameter name
    cmd.Parameters.AddWithValue(name, fields[i]);   //now insert the value from "fields" with the corresponding parameter name
}

cmd.CommandText = string.Format("select * from table where field in ({0})", string.Join(",", parameterNames));  //now construct the dynamic parameterized command text
var dr = cmd.ExecuteReader();

Select allOpen in new window

In that case, it would be easier to just make sure that sql injection can't happen in concatenation (replacing possible quotes and other special characters in the dropdownlist that could have been inserted by javascript injection) and use it with direct concatenation.

Yes, would be far simpler.  Just make sure you catch all of the necessary problems, quotes, semicolons, close brackets, etc...

Excellent, this works great. I was actually already pondering something similar, but all I could think of was something like:
WHERE @sTipo LIKE '%'+Field+'%'
and that seemed to me to be too expensive to use. I believe charindex is a cheaper option by far.