locked
SQL If Statement on MS Access Dbase RRS feed

  • Question

  • User-1103934217 posted

    My students are working on an ASP.net VB application for a Web Programming competition and we are having problems with a page where we want an URL string to return all values from our database.

    Here is the page:

    http://www.gbeastfbla.com/showjobsbycategory.aspx 

    If you use the jobs by category menu on the left side, if you select a job category (ie retail) it will pull up all the jobs from that category.  By using a query in the URL like this:

    http://www.gbeastfbla.com/showjobsbycategory.aspx?jobcategory=Retail

    What we need is an option that would pull up all of the records regardless of category.  So we made a menu option that passes the value "all".  Like this:

    http://www.gbeastfbla.com/showjobsbycategory.aspx?jobcategory=all

    We use this information for the dataset we created (we're using Dreamweaver, ASP.net VB and and Access Dbase).  We made a dataset SQL as follows:

    SELECT *
    FROM jobs, businesses
    WHERE jobs.contactlogin=businesses.contactlogin and jobcategory = ?
    ORDER BY jobs.dateposted DESC

    This works fine if a value is passed.  For example if "retail" is picked from the menu it passes the value just fine and returns all jobs that have "retail" as the job category in the database.

    My problem is, how do I modify the above Select statement to show all jobs in the database if the value "all" is passed for the jobcategory.  My first thought was to create an If statement that says something to the effect of:

    If jobcategory = all THEN
      SELECT *
      FROM jobs, businesses
      WHERE jobs.contactlogin=businesses.contactlogin
      ORDER BY jobs.dateposted DESC
    ELSE
      SELECT *
      FROM jobs, businesses
      WHERE jobs.contactlogin=businesses.contactlogin and jobcategory = ?
      ORDER BY jobs.dateposted DESC
    END IF

    But I haven't been able to figure out the correct syntax. 

    I also tried making this work by using a query in MS Access:

    SELECT *
    FROM jobs, businesses
    WHERE jobs.contactlogin=businesses.contactlogin
      AND (jobcategory = @jobcategory or @jobcategory = 'all')
    ORDER BY jobs.dateposted DESC;

    (which does work within MS Access) and calling the query in the Dreamweaver dataset but apparently MS Access can't do that.

    This seems like it should be easy but so far this problem is kicking my butt.  Can someone help me figure out how to make this work?

    Thanks in advance for anyone willing to help us.

     

    Tuesday, January 11, 2011 6:30 PM

Answers

  • User-1199946673 posted

    SELECT *
    FROM jobs, businesses
    WHERE jobs.contactlogin=businesses.contactlogin
      AND (jobcategory = @jobcategory or @jobcategory = 'all')
    ORDER BY jobs.dateposted DESC;

     

    This is correct

    calling the query in the Dreamweaver dataset but apparently MS Access can't do that.

     

    You need to set the CommandType Property to StoredProcedure, and the CommandText Property to the name of the Stored Query, or enter the above SELECT statement in the CommandText Property and set the CommandType to its default value Text.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 12, 2011 5:07 AM

All replies

  • User-821857111 posted

    SELECT *
    FROM jobs, businesses
    WHERE jobs.contactlogin=businesses.contactlogin
      AND (@jobcategory IS NULL  OR jobcategory = @jobscategory)
    ORDER BY jobs.dateposted DESC;


    Wednesday, January 12, 2011 1:12 AM
  • User-642343902 posted

     Try this and let me know if it is helpfull for u.

    SELECT *
    FROM jobs, businesses
    WHERE jobs.contactlogin=businesses.contactlogin 

    and jobcategory =case @jobcategory when 'all' then jobcategory else @jobcategory end
     ORDER BY jobs.dateposted DESC;

    Wednesday, January 12, 2011 1:24 AM
  • User-821857111 posted

    case @jobcategory when 'all' then jobcategory else @jobcategory end

    MS Access doesn't support that syntax.


    Wednesday, January 12, 2011 2:36 AM
  • User-1199946673 posted

    SELECT *
    FROM jobs, businesses
    WHERE jobs.contactlogin=businesses.contactlogin
      AND (jobcategory = @jobcategory or @jobcategory = 'all')
    ORDER BY jobs.dateposted DESC;

     

    This is correct

    calling the query in the Dreamweaver dataset but apparently MS Access can't do that.

     

    You need to set the CommandType Property to StoredProcedure, and the CommandText Property to the name of the Stored Query, or enter the above SELECT statement in the CommandText Property and set the CommandType to its default value Text.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 12, 2011 5:07 AM
  • User-1103934217 posted

    I'll give it a try.  THANKS! 

    Wednesday, January 12, 2011 7:56 AM
  • User1867929564 posted

    try this,
     SELECT * FROM jobs, businesses WHERE jobs.contactlogin=businesses.contactlogin AND (jobcategory = @jobcategory or @jobcategory is null ) ORDER BY jobs.dateposted DESC;

    Saturday, January 15, 2011 3:31 AM