Passing multiple values through form into SQL in keyword

Question

0.00/5 (No votes)

See more:

Hello guys,

I have a form with 3 fields. 1 is card number which is dynamic that is user can add more card numbers when they click on add more button. And the other 2 fields are start date and end date.
What i want is when the user clicks submit button after filling up all details the values will pass through parameters in backend database and fetch the records and will display in frontend gridview.
But the problem is i am unable to pass multiple values through parameter.
Like if 3 card numbers and dates are submitted.
Sql query should be like
select * from table where cards in (@card) and date between @startdate and @enddate.
So in above example how can i pass multiple card numbers in a single parameter(@card) and i do not know that exactly how many card numbers will be submitted. They can be 3, 4, 5 and so on.
Please help.

What I have tried:

Googled alot about this and people have posted to use table valued parameters. But i am not sure how to use them and also do not know if they will work in this scenario as the number of cards is not fixed in this case.

Posted 24-Oct-16 0:22am

Faran Saleem

Updated 25-Oct-16 1:47am

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2016-10-24T00:47:00

In your case it's numbers, so you could just pass it as a comma separated list:

C#

List<int> data = new List<int>() {1, 2, 3};
string sqlCmd = string.Format("SELECT * FROM MyTable WHERE Cards IN ({0}) AND [Date] BETWEEN @startdate AND @enddate", String.Join(",", data));

But that's not a good approach in general as it leaves you open to SQL injection.
This is one way round it: Using SqlParameter with SQL's IN Clause in C# | SvenBit - Sebastian Hadinata[^]

Rahul_Biswas · Answer 2 · 2016-10-25T00:08:00

1. Create a SQLParameter @cardxml of XML type. Place all the user selected cards into the parameter in the format <value>1<value>2.....etc.
2. Use a query like this
'select * from table
where cards in (
select n.value('./text()[1]','int') from @cardsxml.nodes('./value') as v(n)
)
and date between @startdate and @enddate'
3. Preferably use a SP to pull this off. I am assuming you are using SQL Server as backend.

Other approaches-
Send a comma separated list of card values to the backend and then parse it in the backend using techniques described sql server - Split function equivalent in T-SQL? - Stack Overflow[^]

Keviniano Gayo · Answer 3 · 2016-10-25T01:47:00

Here's how to use table-valued parameters. (above solutions can work as well)
1. Create a table type in your database:

CREATE TYPE CardTableType AS TABLE   
( Card VARCHAR(50)  )

2. You create a stored procedure that accepts table-valued parameter:

SQL

CREATE PROCEDURE dbo. usp_GetCards 
    @Cards CardTableType READONLY  
    AS   
    SET NOCOUNT ON  
    Select ... Where Card in (Select card From @Cards)..

3. In your .net code:

C#

param = new SqlParameter("@Cards", dataTableCardType);
                param.TypeName = "CardTableType";
                param.SqlDbType = SqlDbType.Structured;
                cmd.Parameters.Add(param);

For more information: Use Table-Valued Parameters (Database Engine)[^]

SanthoshBabu Mahimairaj · Answer 4 · 2016-10-25T21:20:00

Solution 4

Hello,

I think you were using Varchar Data type for CardID, so try to form query like this

SQL

Select * from table where cards in (@card) and date between @startdate and @enddate

@card should be '1','2','3'

this will works

Posted 25-Oct-16 21:20pm

SanthoshBabu Mahimairaj

Passing multiple values through form into SQL in keyword

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0