Avatar of flukester
flukester

asked on 

Assign multiple values to sqldataadapter parameter

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

.NET ProgrammingASP.NET

Avatar of undefined
Last Comment
joechina
Avatar of Odessa_Tech
Odessa_Tech

hello flukester,
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.Parameters.Add("@ID", ).Value =

see it working...thanks
Avatar of joechina
joechina

You can pass multiple values to your SP with one parameter.

One way to work around this is to change the type of @Status parameter to int of your stored procedure.

In your code,

You just put
(status = @Status or @Status = -1)

When you want to both, just pass -1
Avatar of flukester
flukester

ASKER

Odessa_Tech, the else statement in the sp is for a different page that loads everything no ID needed, another page that has details specific to the record uses the else part of the statement and an ID querystring.

joechina, not quite sure what you mean. If I was to change it to an int datatype then I would have to change my insert and update sp's. and instead of true(1), false(0), I would have to come up with another type of scheme.

What this sp does is get job postings and the status indicates whether or not they should be visible on the page, but I also have another admin page that allows the admin to edit information and turn the records on  and off (status)

Any other ideas?
ASKER CERTIFIED SOLUTION
Avatar of flukester
flukester

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of joechina
joechina

Hi, flukester

There is no need to change the type of the table.
Just change the type of the input parameter of the stored procedure
.NET Programming
.NET Programming

The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.

137K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo