Using IN to fill DataTableAdapter.

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

I have a set of reports that use the graphically created dataset. In the
SelectCommand, I need to use "IN" in the where (SELECT FullName,
CheckInTime, CheckOutTime, Minutes FROM dbo.CheckIn Where Login IN (@People)
AND CheckInTime > @CheckIn AND CheckOutTime < @CheckOut).

On the form, I have a listbox that should allow the user to select several
names and those names should be passed to the @People parameter. I can't
find a way to get that to work.

I can get a single name to work
(Me.CheckInTableAdapter.Fill(Me.dsClockIn.CheckIn,
lbPeople.SelectedValue.ToString, dtpStart.Value, dtpEnd.Value)).
 
Shawn said:
I have a set of reports that use the graphically created dataset. In the
SelectCommand, I need to use "IN" in the where (SELECT FullName,
CheckInTime, CheckOutTime, Minutes FROM dbo.CheckIn Where Login IN
(@People) AND CheckInTime > @CheckIn AND CheckOutTime < @CheckOut).

On the form, I have a listbox that should allow the user to select several
names and those names should be passed to the @People parameter. I can't
find a way to get that to work.

I can get a single name to work
(Me.CheckInTableAdapter.Fill(Me.dsClockIn.CheckIn,
lbPeople.SelectedValue.ToString, dtpStart.Value, dtpEnd.Value)).
Click to expand...

Create one parameter per selected item (eg named @person1 ... @personN), and
build the SQL like

... Login IN (@person1, @person2, @person3, @person4) ...

Or, if there might be a large number of selectable people, write them into a
temp table and change the sql to "...login in (select ID from
temptable)..."


Armin
 
Shawn said:
I have a set of reports that use the graphically created dataset. In the
SelectCommand, I need to use "IN" in the where (SELECT FullName,
CheckInTime, CheckOutTime, Minutes FROM dbo.CheckIn Where Login IN
(@People) AND CheckInTime > @CheckIn AND CheckOutTime < @CheckOut).

On the form, I have a listbox that should allow the user to select several
names and those names should be passed to the @People parameter. I can't
find a way to get that to work.

I can get a single name to work
(Me.CheckInTableAdapter.Fill(Me.dsClockIn.CheckIn,
lbPeople.SelectedValue.ToString, dtpStart.Value, dtpEnd.Value)).
Click to expand...
Go to Google and type "sql server + passing arrays". You will find many
examples of what you are looking for.
 
Back
Top