Adding additional data adapter parameters at run time

Posted on 2007-07-26
Medium Priority
Last Modified: 2013-11-07
I'm working on a report form which just contains a data adapter and a listview.  The backend is Microsoft SQL server.  The SQL for the report is

SELECT        Contact.ContactID, Contact.ContactDisplayName, Contact.ContactAddressCSV, Contact.ContactAdviserID, Contact.ContactLocalAuthorityID,
                         Contact.ContactAgeGroupID, Contact.ContactSexID, Contact.ContactEmploymentStatusID, Contact.ContactEnquirySourceID, Contact.ContactDOB,
                         Contact.ContactEthnicity, Contact.ContactBHEOfficeID, Session.SessionDisplayName, Session.SessionSatisfactionID, Session.SessionLocation,
                         Session.SessionNotes, Session.SessionAdvisorID, Session.SessionType, Session.SessionPrice, Session.SessionBackground
FROM            Contact INNER JOIN
                         ContactSession ON Contact.ContactID = ContactSession.ContactSessionContactID INNER JOIN
                         Session ON ContactSession.ContactSessionSessionID = Session.SessionID
WHERE        (Session.SessionDate >= @FromSessionDate) AND (Session.SessionDate <= @ToSessionDate) AND (Session.SessionType = @SessionType) AND
                         (Contact.ContactLocalAuthorityID = @LocalAuthorityTypeID)

The parameters are specified at run time as the users pick options from drop downs and then click run and it then runs the above SQL code using a data adapter.  This works fine but there's one problem, the LocalAuthorityTypeID could be one of up to 50 choices.  So I need the ability to add additional OR statements to the data adapter that contains this SQL command text at run time.

Does anybody have any ideas of a way to go about the above?  OR even better still is there an easier way to achieve the above result?
Question by:noel58
LVL 12

Expert Comment

ID: 19573259
Sorry not to understand. Do you want Contact.ContactLocalAuthorityID = @LocalAuthorityTypeID be changet to something as  @LocalAuthorityTypeID IN (<list of possible IDs>) ?
Or you want to fill dropdown by walues from database?

Accepted Solution

ToFro earned 2000 total points
ID: 19573293
You could specify the Parameters of the SqlCommand. Have a look at this:


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question