How can I have a variable number of parameter values in a dataset?

I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:

For iCount = 0 To UBound(sArray)
strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "
Next

Can I do this is a dataset method? How?

If I can't, what are my options?

Diane

make use of temp table or table variable

declare @.word table

(

word varchar(100)

)

select *

from sometable t inner join @.word w

on t.Description like '%' + w.word + '%'

|||

Hi,

From the question you mentioned, do you want to make a query with multiple input variables? If so, I just want to know how to split them in your solution?

Actually, the user input the keywords in the textbox and they use space on the keyboard to split each word. And when we recieve the request from the user,

we may use split method to separate each words into array.

string s = "keyword1 keyword2 keyword3";
string[] myar = s.Split(' ');

for (int i = 0; i < myar.Length; i++) {

strSQL = strSQL & "Description LIKE '%" & myar(i) & "%' OR "
}

After the sql statement is created, you may put it into the SqlDataAdapter and fill into a DataSet. So you can get the query result which matches the multiple keywords.

SqlDataAdapter myadpt = new SqlDataAdapter(strSQL, myconn);
DataSet myds = new DataSet();
myadpt.Fill(myds);

If this does not answer your question, please feel free to reply. Thank you!

|||

Hi,

You can search more efficiently using 'Contains' in SQL server. For this you need to enable Full-Text search in sql server for a field.

Contains(FieldName, '+''''+@.GroupKeyWord+''''+')'

@.GroupKeyWord is the keyword you can directly pass, i.e. the user input string. See SQL Server help for more details. It is really interesting. If you don't want to use this feature then you can go for split. But split needs a delimiter string, i.e. a single space or a particular character. The resultant array can be looped from lower bound to upper bound to create Sql statement.

The advantage of using contains is its flexibility. User can create Boolean search himself and you just have to pass the value user entered into the textbox directly to the sql statement.

Don't forget to mark this as Answer if this post helps you

|||

KH, you have me totally confused <grin>!

Michael, I'm not having a problem spliting the variables. Say I have a table with fields name, company, address, description. I know how to use a parameter in a strongly typed dataset, say to search for Name=@.name in a method that expects a name parameter. What I need is a method that returns all records where the description field contains one or more of the words the user entered. I can build the select string, but I don't know how to implement this in a dataset method.

Hamlin, this would be optimal. Unfortunately, full text search isn't enabled, and I can't get it enabled. I asked my host, and they won't permit it.

I'm wondering if I should maybe use an SqlDataSource instead of the dataset?

Diane

|||

Hi Mainship,

Sorry for my misunderstanding. Now I know that your problem is focused on how to implement the select string in a dataset method cause you don't know the number of parameter values.

Actually you can use DataSet.TableName.Select(SelectString) Method instead of inputing the select string in the TableAdapter configuration wizard. For example:

northwindDataSet.Products.Select(string);

For more details about DataTable.Select Method ,pls check:http://msdn2.microsoft.com/en-us/library/system.data.datatable.select(VS.80).aspx

If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thank you!

Diane