passing listbox values to query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

passing listbox values to query.

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2012-09-22 : 18:16:59

[code]USE [ReplacementSchedule]
GO
/****** Object: StoredProcedure [dbo].[GetReplacementStatusReport] Script Date: 09/22/2012 12:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReplacementStatusReport]
@snapShotID int,
@bidOpStatusID int,
@fromType int,
@toType int,
@fromYear int,
@toYear int,
@divisionCode nvarchar(max),
@deparmentCodes nvarchar(max),
@repStatusIDs varchar(500)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT VehicleNumber = CASE WHEN ISNUMERIC(RTAFleet.dbo.vehfile.vehicle) = 1 AND

RTAFleet.dbo.vehfile.vehicle IS NOT NULL THEN

RTAFleet.dbo.vehfile.vehicle ELSE RTAFleet.dbo.vehfile.veh_xref_num END,

ReplacementSchedule.dbo.Units.LinkNumber AS RPLinkNumber,

RTAFleet.dbo.vehfile.link_number AS LinkNumber,

ReplacementSchedule.dbo.Departments.DeptDescription AS Department,

Model= CASE WHEN RTAFleet.dbo.vehfile.vehicle_model IS NULL OR

RTAFleet.dbo.vehfile.vehicle_model = ' ' THEN 'N/A' ELSE

RTAFleet.dbo.vehfile.vehicle_model END,

Make = CASE WHEN RTAFleet.dbo.vehfile.vehicle_make IS NULL OR

RTAFleet.dbo.vehfile.vehicle_make = ' ' THEN 'N/A' ELSE

RTAFleet.dbo.vehfile.vehicle_make END,

RTAFleet.dbo.vehfile.vehicle_location AS ClassCode,

ReplacementSchedule.dbo.Units.BudgetYear AS BudgetYear,

ReplacementSchedule.dbo.Units.RevisedYear AS RevisedYear,

(SELECT ReplacementSchedule.dbo.Types.RepCost FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
AS RepCost,

(SELECT ReplacementSchedule.dbo.Types.TypeDescription FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
As TypeDes,

BidOpStatus = CASE WHEN ReplacementSchedule.dbo.BidOppStatus.BidOffStatu IS NULL THEN 'N/A'
ELSE ReplacementSchedule.dbo.BidOppStatus.BidOffStatu END FROM ReplacementSchedule.dbo.Departments,
ReplacementSchedule.dbo.Units INNER JOIN ReplacementSchedule.dbo.XrefNum ON
ReplacementSchedule.dbo.Units.LinkNumber = ReplacementSchedule.dbo.XrefNum.RPLinkNumber LEFT JOIN
ReplacementSchedule.dbo.BidOppStatus ON
ReplacementSchedule.dbo.Units.BidOpStatusID = ReplacementSchedule.dbo.BidOppStatus.BidOffStatuID,
RTAFleet.dbo.vehfile WHERE
(ReplacementSchedule.dbo.Units.BidOpStatusID = @bidOpStatusID) AND
(ReplacementSchedule.dbo.Units.Type BETWEEN @fromYear AND @toYear) AND
(RTAFleet.dbo.vehfile.vehicle NOT LIKE 'JC%') AND
(ReplacementSchedule.dbo.Units.DisposalOrderID = 0) OR
(ReplacementSchedule.dbo.Units.DisposalOrderID IS NULL) AND
(ReplacementSchedule.dbo.Units.SnapShotID = @snapShotID) AND
(ReplacementSchedule.dbo.Units.RevisedYear BETWEEN @fromType AND @fromType) AND
(ReplacementSchedule.dbo.Units.ReplacementStatusID in (' + @repStatusIDs + ')) AND
(RTAFleet.dbo.vehfile.link_number = ReplacementSchedule.dbo.XrefNum.RTALinkNumber) AND
(ReplacementSchedule.dbo.Departments.DeptNumber = RTAFleet.dbo.vehfile.division AND
((ReplacementSchedule.dbo.Departments.DeptCode IN (' + @deparmentCodes + ')) OR
(RTAFleet.dbo.vehfile.division = @divisionCode)
)) ORDER BY VehicleNumber
END

[/code]

Msg 245, Level 16, State 1, Procedure GetReplacementStatusReport, Line 20
Conversion failed when converting the varchar value ' + @repStatusIDs + ' to data type int.

Not sure what to do

Any help would be awesome.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 19:04:50

The error is coming from the part shown below.

	       AND (
	               ReplacementSchedule.dbo.Units.ReplacementStatusID IN (' + @repStatusIDs + ')
	           )

As a quick fix, you can change that code to what I am showing next - but that may not be the real solution you want, because it is likely to be inefficient.

	       AND (
	               ','+ @repStatusIDs +',' LIKE
	               '%,'+CAST(ReplacementSchedule.dbo.Units.ReplacementStatusID AS VARCHAR(256)) + ',%' 
	           )

You will need to do a similar change the @departmentcodes as well.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 23:12:37

Run that query from an SSMS query window and see if you get any output. If you don't, remove the where conditions one at a time to see what is causing the results to be filtered out.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2012-09-23 : 01:08:33

I made an error, I am going to try it again.
Can you please verify
<code>
ALTER PROCEDURE [dbo].[GetReplacementStatusReport]
@snapShotID int,
@bidOpStatusID int,
@fromType int,
@toType int,
@fromYear int,
@toYear int,
@divisionCode varchar(500),
@deparmentCodes varchar(500),
@repStatusIDs varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT VehicleNumber = CASE WHEN ISNUMERIC(RTAFleet.dbo.vehfile.vehicle) = 1 AND RTAFleet.dbo.vehfile.vehicle IS NOT NULL THEN
RTAFleet.dbo.vehfile.vehicle ELSE RTAFleet.dbo.vehfile.veh_xref_num END,
ReplacementSchedule.dbo.Units.LinkNumber AS RPLinkNumber,
RTAFleet.dbo.vehfile.link_number AS LinkNumber,
ReplacementSchedule.dbo.Departments.DeptDescription AS Department,
Model= CASE WHEN RTAFleet.dbo.vehfile.vehicle_model IS NULL OR
RTAFleet.dbo.vehfile.vehicle_model = ' ' THEN 'N/A' ELSE RTAFleet.dbo.vehfile.vehicle_model END,
Make = CASE WHEN RTAFleet.dbo.vehfile.vehicle_make IS NULL OR RTAFleet.dbo.vehfile.vehicle_make = ' ' THEN
'N/A' ELSE RTAFleet.dbo.vehfile.vehicle_make END,
RTAFleet.dbo.vehfile.vehicle_location AS ClassCode,
ReplacementSchedule.dbo.Units.BudgetYear AS BudgetYear,
ReplacementSchedule.dbo.Units.RevisedYear AS RevisedYear,

(SELECT ReplacementSchedule.dbo.Types.RepCost FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
AS RepCost,

(SELECT ReplacementSchedule.dbo.Types.TypeDescription FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
As TypeDes,

BidOpStatus = CASE WHEN ReplacementSchedule.dbo.BidOppStatus.BidOffStatu IS NULL THEN 'N/A'
ELSE ReplacementSchedule.dbo.BidOppStatus.BidOffStatu END FROM ReplacementSchedule.dbo.Departments,
ReplacementSchedule.dbo.Units INNER JOIN ReplacementSchedule.dbo.XrefNum ON
ReplacementSchedule.dbo.Units.LinkNumber = ReplacementSchedule.dbo.XrefNum.RPLinkNumber LEFT JOIN
ReplacementSchedule.dbo.BidOppStatus ON
ReplacementSchedule.dbo.Units.BidOpStatusID = ReplacementSchedule.dbo.BidOppStatus.BidOffStatuID,
RTAFleet.dbo.vehfile WHERE
(ReplacementSchedule.dbo.Units.BidOpStatusID = @bidOpStatusID) AND
(ReplacementSchedule.dbo.Units.Type BETWEEN @FromType AND @ToType) AND RTAFleet.dbo.vehfile.vehicle NOT LIKE 'JC%' AND
(ReplacementSchedule.dbo.Units.DisposalOrderID = 0 OR ReplacementSchedule.dbo.Units.DisposalOrderID IS NULL) AND
ReplacementSchedule.dbo.Units.SnapShotID = @SnapShotID AND (ReplacementSchedule.dbo.Units.RevisedYear BETWEEN @FromYear AND @ToYear) AND
(','+ @repStatusIDs +',' LIKE '%,'+ CAST(ReplacementSchedule.dbo.Units.ReplacementStatusID AS VARCHAR(256)) + ',%') AND
RTAFleet.dbo.vehfile.link_number = ReplacementSchedule.dbo.XrefNum.RTALinkNumber AND
ReplacementSchedule.dbo.Departments.DeptNumber = RTAFleet.dbo.vehfile.division AND
(((','+ @deparmentCodes +',' LIKE '%,'+CAST(ReplacementSchedule.dbo.Departments.DeptCode AS VARCHAR(256)) + ',%')
OR (RTAFleet.dbo.vehfile.division = @divisionCode))) ORDER BY VehicleNumber
END
<\code>

Any help would be awesome.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2012-09-23 : 01:35:49

<code>
USE [ReplacementSchedule]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetReplacementStatusReport]
@snapShotID = 1,
@bidOpStatusID = 1,
@fromType = 101,
@toType = 999,
@fromYear = 1997,
@toYear = 2024,
@divisionCodes = N'PW14040403,PW14040405,PW14040406,PW17010905,PW17020903,PW17030906,PW17040902,PW17050101,PW17060901,PW17070907,PW17080907,PW17090907,PW17100907,PW17110907,PW17140902,PW17160901',
@deparmentCode = PWSMT,
@repStatusIDs = N'1,2,3,4,5,6,7,8,9,10,11,12'

SELECT 'Return Value' = @return_value

GO

ALTER PROCEDURE [dbo].[GetReplacementStatusReport]
@snapShotID int,
@bidOpStatusID int,
@fromType int,
@toType int,
@fromYear int,
@toYear int,
@divisionCodes varchar(500),
@deparmentCode varchar(500),
@repStatusIDs varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT VehicleNumber = CASE WHEN ISNUMERIC(RTAFleet.dbo.vehfile.vehicle) = 1 AND RTAFleet.dbo.vehfile.vehicle IS NOT NULL THEN
RTAFleet.dbo.vehfile.vehicle ELSE RTAFleet.dbo.vehfile.veh_xref_num END,
ReplacementSchedule.dbo.Units.LinkNumber AS RPLinkNumber,
RTAFleet.dbo.vehfile.link_number AS LinkNumber,
ReplacementSchedule.dbo.Departments.DeptDescription AS Department,
Model= CASE WHEN RTAFleet.dbo.vehfile.vehicle_model IS NULL OR
RTAFleet.dbo.vehfile.vehicle_model = ' ' THEN 'N/A' ELSE RTAFleet.dbo.vehfile.vehicle_model END,
Make = CASE WHEN RTAFleet.dbo.vehfile.vehicle_make IS NULL OR RTAFleet.dbo.vehfile.vehicle_make = ' ' THEN
'N/A' ELSE RTAFleet.dbo.vehfile.vehicle_make END,
RTAFleet.dbo.vehfile.vehicle_location AS ClassCode,
ReplacementSchedule.dbo.Units.BudgetYear AS BudgetYear,
ReplacementSchedule.dbo.Units.RevisedYear AS RevisedYear,

(SELECT ReplacementSchedule.dbo.Types.RepCost FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
AS RepCost,

(SELECT ReplacementSchedule.dbo.Types.TypeDescription FROM ReplacementSchedule.dbo.Types
WHERE (CAST(ReplacementSchedule.dbo.Types.Type AS NVARCHAR(255)) =
LEFT(RTAFleet.dbo.vehfile.vehicle_location, 3)))
As TypeDes,

BidOpStatus = CASE WHEN ReplacementSchedule.dbo.BidOppStatus.BidOffStatu IS NULL THEN 'N/A'
ELSE ReplacementSchedule.dbo.BidOppStatus.BidOffStatu END FROM ReplacementSchedule.dbo.Departments,
ReplacementSchedule.dbo.Units INNER JOIN ReplacementSchedule.dbo.XrefNum ON
ReplacementSchedule.dbo.Units.LinkNumber = ReplacementSchedule.dbo.XrefNum.RPLinkNumber LEFT JOIN
ReplacementSchedule.dbo.BidOppStatus ON
ReplacementSchedule.dbo.Units.BidOpStatusID = ReplacementSchedule.dbo.BidOppStatus.BidOffStatuID,
RTAFleet.dbo.vehfile WHERE
(ReplacementSchedule.dbo.Units.BidOpStatusID = @bidOpStatusID) AND
(ReplacementSchedule.dbo.Units.Type BETWEEN @FromType AND @ToType) AND RTAFleet.dbo.vehfile.vehicle NOT LIKE 'JC%' AND
(ReplacementSchedule.dbo.Units.DisposalOrderID = 0 OR ReplacementSchedule.dbo.Units.DisposalOrderID IS NULL) AND
ReplacementSchedule.dbo.Units.SnapShotID = @SnapShotID AND (ReplacementSchedule.dbo.Units.RevisedYear BETWEEN @FromYear AND @ToYear) AND
(','+ @repStatusIDs +',' LIKE '%,'+ CAST(ReplacementSchedule.dbo.Units.ReplacementStatusID AS VARCHAR(256)) + ',%') AND
RTAFleet.dbo.vehfile.link_number = ReplacementSchedule.dbo.XrefNum.RTALinkNumber AND
ReplacementSchedule.dbo.Departments.DeptNumber = RTAFleet.dbo.vehfile.division AND
((',' + @divisionCodes + ',' LIKE '%,' + RTAFleet.dbo.vehfile.division + ',%')
OR (ReplacementSchedule.dbo.Departments.DeptCode = @deparmentCode)) ORDER BY VehicleNumber
END
</code>
thank you!!!!

Any help would be awesome.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-23 : 10:38:46

Since I (or anyone else who may want to respond) don't have the data and the tables that you are querying against, there isn't a way to figure out why you are getting no rows when you run the query. You will have to debug it, and one way to debug it is to alter the stored procedure by removing one where clause at a time to see which one is causing the elimination of the rows from the result set.

I am surprised that in your statement where you are calling the stored procedure, the PWSMT that I have marked in red below isn't generating an error when you run it.


     @deparmentCode = PWSMT,
     @repStatusIDs = N'1,2,3,4,5,6,7,8,9,10,11,12'

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2012-09-24 : 17:39:19

hi sunitabeck I posted another question,

Any help would be awesome.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-24 : 18:50:30

What version of SQL are you using? You should really be using Table-Valued Parameters for this sort of thing.

http://msdn.microsoft.com/en-us/library/bb510489.aspx
http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources