Multiple value parameter passing in a dataset to fill

Question

5.00/5 (1 vote)

See more:

Hi
I've been struggling for a long time about this, normally user have a multiple choices which is select by checkboxes and based upon these checkboxes we make the query parameter like Select * from classes where class_id in (23,56,78) The multiple choices will be passed in a query like the above Statement. I am really unable to pass "23,56,78 altogether.

I have edited my dataset in designer and changed its string to like this "Select * from classes where class_id in (" & paramstr & ")" and set the paramstr = 23,56,78 whenever I call the dataset to be filled. But problem is whenever in open dataset in designer form it gets reset to my original query Select * from classes where class_id in (23,56,78) When I go through it I found there is an xsd file in xml format it updates the dataset to it original from here. But I dont have any idea how to change its commandtext to accept a variable.

I want two solutions here:
a. Whether what I am doing is right or not? if not what is the right way to pass multiple values
b. How can I set the variable in so that it works permanent for me.

I am using vb.net 2005 winforms and access database
Thanks & Best Regards
Jay Khatri

Posted 29-Sep-10 23:12pm

JayKhatri

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Henry Minute · Answer 1 · 2010-09-30T00:23:00

Solution 1

Take a look at Passing an array or DataTable into a stored procedure[^] for some ideas.

The code is C# but hopefully you will be able to follow the principles.

Posted 30-Sep-10 0:23am

Henry Minute

Henry Minute · Answer 2 · 2010-09-30T01:08:00

The only way I can think of to do this is to build your SQL Query in stages.

Get the users selections into an array (paramArray), as you are doing.

Set a string variable (queryString) for your Query to "SELECT * FROM classes WHERE class-id = " + paramArray[0].ToString()

then loop through your array and for each entry (after the first) add**

queryString = querystring + " OR class-id = " + paramArray[x].ToString()

If you are familiar with StringBuilder then use that instead of a String, if not then get it working first then study StringBuilder and convert your code to use it.

**Check the Access SQL syntax for this, as I am doing it off the top of my head.

Good luck! :)

JayKhatri · Answer 3 · 2010-09-30T01:25:00

I fully understood your view, and yes I use StringBuilder at many places. But I said I designed the dataset using Dataset Designer and even the query is so complicated, let see how I will add a text piece to or condition. You are also aware about the limitation of sql query in ms-access.

I will try to use the loop and oring. I would like to ask if it is still a chance for this trick to alter the code that has been developed by Dataset Designer in xml format. and passing variable to it so that my code not get altered.

Thank you very much for all of your support.

Jay Khatri

Multiple value parameter passing in a dataset to fill

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Multiple value parameter passing in a dataset to fill

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us