I am moving my site to ASP.NET 2.0 from 1.1...in doing so I am moving some functionality over verbatim. One of them is a search feature for our job board. The code to call the stored proc has not changed...it basically looks for values that are provided on a search page and passes them to the database. If a field is not specified in the UI, a System.Db.Null is passed...in the past I did not even have to pass this back as my query worked as is...now I am getting the above exception and I cannot figure out what it is! If I pass all nulls back it works...as soon as I specify a value bam it fails. I have removed the parameter definitions defaulting to null...no difference...any ideas??
ALTER PROCEDURE MyCompany_SearchForJobs
(
@.state int = null,
@.city NVarChar (75) = null,
@.serviceArea int = null,
@.jobType int = null,
@.dateSearch DateTime = null
)
AS
SELECT
MyCompany_JobPostings.*, MyCompany_OrgInfo.OrgName AS OrgName, MyCompany_States.State AS DisplayState
FROM
MyCompany_JobPostings, MyCompany_OrgInfo, MyCompany_States
WHERE
MyCompany_JobPostings.State = IsNull(@.state , MyCompany_JobPostings.State)
AND
MyCompany_JobPostings.City Like IsNull(@.city, MyCompany_JobPostings.City)
AND
MyCompany_JobPostings.PositionType = IsNull(@.jobType, MyCompany_JobPostings.PositionType)
AND
MyCompany_JobPostings.OrgId IN (SELECT OrgId FROM MyCompany_OrgInfo WHERE ServiceArea = IsNull(@.serviceArea, ServiceArea) OR ServiceArea2 = IsNull(@.serviceArea, ServiceArea2))
AND
MyCompany_JobPostings.Status = 5
AND
MyCompany_JobPostings.JobPostedDate = IsNull(@.dateSearch, MyCompany_JobPostings.JobPostedDate)
AND
MyCompany_JobPostings.OrgId = MyCompany_OrgInfo.OrgId
AND
MyCompany_JobPostings.State = MyCompany_States.StateId
ORDER BY
MyCompany_JobPostings.JobPostedDate DESC
I figured it out...:) It was my lovely assignment code that was messing it up. I had code that chose which stored procedure to call...one with date ranges and one without. I moved the line of code that tells what type of Command it was above the assignment of the Command object and thus reinitializing it to a "text" proc. Goofy but it works now thankfully. :)
No comments:
Post a Comment