Hi,
I have a a SQL Server stored procedure that is used in a vb.net winforms search form.
@JobNumber nvarchar(12) = NULL,
@CompanyID integer = NULL,
@Title nvarchar(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT a.Contact,
a.Company_ID,
a.Job,
a.Enquiry,
a.Start,
a.Closed,
a.Title,
b.Company
FROM Job_List a
INNER JOIN Company b ON a.Company_ID = b.Company_ID
WHERE (a.job LIKE @JobNumber + '%' OR @JobNumber IS NULL)
AND (a.Company_ID = @CompanyID OR @CompanyID iS NULL)
AND (a.title LIKE '%' + @Title + '%'OR @Title IS NULL)
ORDER BY Job
If I execute the stored procedure from SSMS and only supply one parameter it returns the results I would expect.
However, when trying to execute the procedure from within my application using this:
Using con As New SqlConnection(strConnection)
Using cmd As New SqlCommand("dbo.AA_JobSearch")
cmd.Connection = con
cmd.Parameters.AddWithValue("@JobNumber", txtJobNumber.Text)
cmd.Parameters.AddWithValue("@CompanyID", CompanyID)
cmd.Parameters.AddWithValue("@Title", txtTitleSearch.Text)
Using sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable
sda.Fill(dt)
(The CompanyID variable is an integer derived from another query) This is data source for a data grid view. Regardless of how many parameters I provide the data grid view is populated with every record in the table, i.e. no filtering takes place.
What I have tried:
I have tried rewriting the VB parameters statements with Add, rather than AddWithValve, but this just results in a string to integer exception. I have spent hours searching the internet for a solution, but haven't yet found anything at works.
If is frustrating as the SQL side works as intended but I can't get the VB.net side to do the same.
Any help would be gratefully received.