How can I pass several integer values within one sql parameter?

Question

I've got a stored procedure with the following criteria:

WHERE (Transaction_tbl.dtime BETWEEN @fromDate AND @toDate)   
  AND (Location_tbl.Locid IN (@locations))

I've got a ListBox which populates the @locations parameter (an integer), and two DateTimePicker controls for the @fromDate and @toDate.

I took my listbox value like this:

cnt = LSTlocations.SelectedItems.Count
        Dim list As New List(Of Integer)
        Dim locid As Integer
        If cnt > 0 Then
            For i = 0 To cnt - 1
                Dim locationanme As String = LSTlocations.SelectedItems(i).ToString
                locid = RecordID("Locid", "Location_tbl", "LocName", locationanme)
                list.Add(locid)
            Next
        End If

i want to pass this list item value to my stored procedure...how i can do this?

cmd23.Parameters.Add("@startDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value= startdate
cmd23.Parameters.Add("@endDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value = enddate
cmd23.Parameters.Add("@locations", SqlDbType.Int) ' <= ???

How can this code be modified to pass several integer identifiers as the @locations parameter so that I may select several items in my listbox?

Parameters are for single values so when using parameters you would need to create as much parameters as there are in the in the List. — Ralf, Oct 17 '13 at 15:20
Have you checked this: http://stackoverflow.com/questions/5681320/add-listint-to-a-mysql-parameter — igrimpe, Oct 17 '13 at 15:21
i am new in vb.net..can u pleas show how i can do same in vb.net — user2878851, Oct 17 '13 at 15:36

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

Change your locations parameter to a string.

SELECT items INTO #locations FROM dbo.split(@locations,',')

You can find the split function here: Split string by comma in SQL Server 2008

You'll have to change your query to join with the #locations temporary table.

then from your VB pass in a comma delmited string of all the selected items from LSTlocations

something like this:

[String].Join(", ", LSTlocations.Items.Cast(Of ListItem)().Where(Function(i) i.Selected).[Select](Function(i) i.Value).ToArray())

edited May 23 '17 at 10:25

Community

1
1

answered Oct 17 '13 at 15:42

Rob Sedgwick

4,342
6
50
87

actulayy locid is integer...so how i can pass string – user2878851 Oct 17 '13 at 15:51
you said to locations parameter to change string...actulally i have to pass ther integer value – user2878851 Oct 17 '13 at 16:47
how i can change my location parameter..? like u suggested – user2878851 Oct 17 '13 at 16:56
change it to a string like the lines above it. But that's one of a number of changes you need to make. You also need to change the SP and to get all the selected items from the listbox into a string. I've provided details of what to do at every stage. I'm assuming you have a basic level of competence to do these things. – Rob Sedgwick Oct 18 '13 at 09:21

How can I pass several integer values within one sql parameter?

1 Answers1