I have a stored procedure that I am trying to use on 2 different pages. First page I only want to select records where status is equal to 1 (bit datatype, so true). The second page I have I want to select all records either 0 or 1. Can I have multiple values for the parameter?
Here is the sp............................
@ID numeric(18, 0) = NULL,
@Status bit
AS
IF @ID IS NULL
Select id, company, location, title, description, qualifications, materials, compensation, deadline, org_name, org_description, website, apply, first_name, last_name, comp, address_1, address_2, city, state, zip, country, phone, fax, email, convert(varchar(20),date_added,107) as date_added, status from intern where status=@Status order by ID desc
ELSE
Select * from intern where ID = @ID order by ID desc
Page 1 which displays properly
Function GetDataSource()
Dim myconn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("xxxxConnectionString").ConnectionString)
Dim sqlda As SqlDataAdapter = New SqlDataAdapter("usp_GetInternships", myconn)
sqlda.SelectCommand.CommandType = Data.CommandType.StoredProcedure
sqlda.SelectCommand.Parameters.Add("@Status", SqlDbType.Bit).Value = 1
Dim ds As DataSet = New DataSet()
sqlda.Fill(ds)
On the second page what I was trying to do, but doesn't seem to work...
da.SelectCommand.Parameters.Add("@Status", SqlDbType.Bit).Value = 0 Or 1
Thanks
your problem seems very simple...see the parameter that ur passing in the else part of the query(@ID). In the SqlDataAdapter you are parameterizing @Status and setting the value 0 or 1 but not @ID
If you want parametrization for ID also then add one more statement like this
da.SelectCommand.Parameter
see it working...thanks