Access :: Modify Select Statement To Show All Jobs In Database

Jan 11, 2011

My students are working on an 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: [URL] 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: [URL] 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: [URL] We use this information for the dataset we created (we're using Dreamweaver, VB and and Access Dbase). We made a dataset SQL as follows:

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
FROM jobs, businesses
WHERE jobs.contactlogin=businesses.contactlogin
ORDER BY jobs.dateposted DESC
FROM jobs, businesses
WHERE jobs.contactlogin=businesses.contactlogin and jobcategory = ?
ORDER BY jobs.dateposted DESC

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:

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.

View 6 Replies

Similar Messages:

Access :: IIS_WPG Write/modify Access To The Folder Where MS-Access Database Is Located - Insert An Error Pop-up?

Feb 17, 2010

Is there anything missing in IIS 6.0 that prevents me from (Insert into table) using MS-Access?

Explain: The application works fine under Visual Studio 2008 IDE the insert into table works fine with no error, Also I tested with hosting provider and works fine with no problem. but now I have published the same exact app in a dedicated server windows 2003 with
IIS 6.0 .NET framework 2.0 with latest service pack I gave IIS_WPG write/modify access to the folder where MS-Access database is located and database but at the time of insert an error pop-up. I need to install in the Server or settings in the IIS to recognize my MS-Access db is it some office runtime that I am missing. (BTW I am using OLEDB connection string in my C# )

Using System.Data.OleDb;

I can retrieve data off of it with no problem but when I try to insert is when it fails I thought the problem was Access Rights but I do not think is the case.

View 4 Replies

Access :: Two Databases In One Select Statement?

Jun 10, 2010

"SELECT a.fibunr, " & _

View 14 Replies

Access :: Create A Select Subquery In Insert Statement?

Feb 2, 2010

I am wondering there is no one asking this question on the internet even I have used google. (May be I am using a strange way on this case)

Here is the insert statement with select statement

Insert into a_table (ref, user, pw) values ((Select max(ref)+1 from reference_table), txtUser.text, txtPw.text);

Ignore the sql injection first, this statement is incorrect. Any idea? thx.

View 6 Replies

ADO.NET :: Select Statement To Called Queries Table In Database?

Sep 2, 2010

anyone know that can we used select statement to called queries table in database or not?usually people used select statement to called common table in database.

View 4 Replies

Create A Simple String Array With Data Retrieved From A Select Statement(SELECT Firstname FROM Customers?

May 29, 2010

i'm trying to to create a simple string array with data retrieved from a select statement(SELECT firstname FROM customers , for ex.)

View 6 Replies

Access :: Select Last Record From Database?

Oct 7, 2010

I'm having a problem trying to get the last record from my database.. I'm doing this: myCommand.Connection.Open()

View 2 Replies

Make Select Statement Select Row With Nearest Values?

Oct 23, 2010

How would i make a select statement select the row with the nearest values? e.g.: I have 3 labels, labels1 2 and 3, with values of 1.2, 2 and 5.8 In my table i have 4 columns, first is the data im after, and the next 3 relate to the three labels. I want to select one row, where the values of labels match it the closest.......

View 8 Replies

ADO.NET :: Sql Query Select Inside A Select Statement?

Dec 2, 2010

i have two tables named

1.LookupPageInfo and (LookupPageInfoId,pathmessageID,pagemessageID)

2.Messages (MessageId,MessageText,ChannelID)

Now i need to write a query something like the query i need to pass the pathmessageid and needs to get messagetext from Messages Table,i am not sure how i need to write the below query..can someone guide me how can i write a below..

select LookupPageInfoId,
(select messagetext from Messages where MessageID=PathmessageId) as TEXT from LookupPageInfo

View 5 Replies

Access :: Show Image From Database To Web Form

Dec 12, 2010

i have uploaded an image in database using file upload control in visual studio 2005 using c# i want to show this image saved in database to the web form. how can i do so?

View 5 Replies

Access :: Every User To Modify Specific Id In Access Db?

Jan 17, 2010

I have an acess db which has primal key ID - Customer, and personal data of the customer .

In another db OR the same, to have the username and login of the customers.

I want to make a login form so if u/n & pass of a customer is correct, to have access to modify (insert, delete, update) his data of the db - his ID's only.

View 5 Replies

Access :: Show Only Filled Data In Dropdownlist From Database?

May 28, 2010

I have some field in datadase which is filled through checkboxlist , only selected checkbox has save to fields otherwise they remain blank: My problem is I want to show only filled value in label text using ";" , but when i am going to display it id display like:


View 4 Replies

Using ADO.NET - To Modify The Table To Make It More Easy To Select Data From It

Apr 27, 2010

I'm trying to use ADO .NET to select data from this table in excel. I'm not sure at this point if I need to modify the table to make it more easy to select data from it. Here's what the excel table looks like

I guess I don't know where to really start. I'm pretty much trying to grab the numbers in the middle(design pressure) based on Mull length in inches and opening width. so the user will input the height and width of window. then if it's going into concrete or wood.. if it;s wood then in the clip to opening row if he uses (2)A or 3(b) he will have a DEsign pressure of 170 if mull length is 42 and opening width is 70. in the same 70 column if he uses (3)a 0r 3(b) his DP will be also 170 in this case.. So I will have a user interface and ask the user for a width of window and height of window which will be the Mull Length and what substrate(wood or concrete) based on that I want to display if u use this amount of screws A or B u will get this DP.

View 2 Replies

Membership Select Provider / Unable To Modify The Connection

Apr 12, 2010

I've done this before but can't remember how for the life of me. I used aspnetreg_sql.exe to create the membership tables in my database. But now i cant seem to be able to point my web app to the correct database. In the provider settings in management interface i only see a radio button with the label "AspNetSqlProvider" but I can only test it (in which it always fails). I can't modify the connection.

View 3 Replies

Access :: Microsoft JET Database Engine Error "80040e14" Syntax Error In INSERT INTO Statement

Jan 14, 2010

I created a database with all text fields. I am using asp to enter the data into access INSERT INTO Form (fname, lname, sID, fl, email, job, employer, Position, grade, degreename, degreetime, degreePlace ) VALUES ('chris','v','12323','cv','','Yes','Uc','Developer','Yes','test','2010','Uc') Above is the sql I am trying to insert [ Response.write(sSql)] But it is failing and I am not able to insert the data in the table. I am getting Microsoft JET Database Engine error '80040e14' Syntax error in INSERT INTO statement Error.

View 2 Replies

ADO.NET :: SELECT Statement Using AND?

Mar 1, 2011

I have two dropdown list, which is 'names' and 'types' and it's being populated by a dataset. It contains an additional item "-- ALL --" to indicate that all items of a particular field are being shown. I have an SQL select statement to filter the records according to the selected item of the dropdowns. I used a select statement to bind the records to a datagrid with regards to the selected items.

SELECT * FROM table where name = ddlnameselecteditem AND type = ddltypeselecteditem

The problem now is when I select a name from the names dropdownlist and types dropdownlist has a selection of -- ALL --, there would be no return records because there's no such thing as '-- ALL --' record in column 'types' i'm querying. I need to display rows of the selected 'name' while still showing all the 'types'. How could I achieve this? I think I need to avoid the '-- ALL --' item from the dropdownlist.

View 7 Replies

Using If Statement In Select?

Apr 26, 2010

I'm pretty new at ASP. I've got a sheet I'm trying to modify using an IF statement. I tried putting them in, in different ways, but it either shows up in the form or I get the options multiple times


i have a variable picked up elsewhere strArea, so I was trying to do
something like


I have tried putting the if's in different places, as well as case statement
but usually I end up with my drop down option list as

View 3 Replies

SQL Server :: Trying To Use An AND In A Select Statement?

Nov 9, 2010

I'm trying to pull a list of clients that have both of two codes in the code table. Here is what I have tried and it does not return any rows. I know that there are clients that have both codes.


View 7 Replies

ADO.NET :: Get Values From Select Statement?

Dec 28, 2010

I am working on LINQ I m feeling difficult with getting the value from the LIST. Here is the following code

ccDataContext context = new ccDataContext();

View 1 Replies

ADO.NET :: Retrieving A Value With Select Statement?

Jan 16, 2011

I have the following select statement :

string Statement = "SELECT * FROM Car WHERE brand = Bmw;

how is it possble to display the result from the statement, because when i display the result exp: LblInfo.Text = Statement;

The result is : SELECT * FROM Car WHERE Brand = Bmw

View 5 Replies

Using Datatable In Select Statement?

Oct 4, 2010

I have records in the datatable which I have retreived from xml file.

Dim docDataSet As New DataSet
Dim docReader As New XmlDocument
Dim xnl As XmlNodeList
xnl = docReader.SelectNodes("/Users/user")
For Each xn As XmlNode In xnl
Dim xr As New XmlNodeReader(xn)
Next xn
Dim Dm_table As DataTable = ds.tables(0)

Now what I want to do is execute a select query like below:

Select * from user where userid in (select ID from Dm_table)

Retreive the records from the User table based on the ID's in the datatable.

View 4 Replies

ADO.NET :: Calculated Field In Select Statement?

Feb 3, 2011

I thought this would be easy but cannot now find out of to do it. All I want to do is execute an sql query on a table and in addition to the table's columns I want to also select/output a field that is calculated within the query. The table has a column "Owner" and I want to compare this to the UserId to produce a bool column "mine". Effectively doing:Select *, (Owner=@UserId) as mine where.....however, this doesn't work so how can I calculate a column in the select statement?

View 3 Replies

SQL Server :: Pulling One By One And Using Select Statement

Sep 27, 2010

I have to pull up a list of people by their branch number which could vary, and give a detailed report on there performance using a select queries. I have the select queries but how can i create a script to look similar to this one. Ive got the branch drop downlist already created just need to know how i can go about pulling all the users in this group and doing this select to all of them. someone told me I might need to use cursors to loop thru a select with every person in the list but im not sure how to go about that.

select fldusername from tbluser where fldio='o' and

is going to give me the names of the people i need to query. but how can i run a query on each of them seperate?

View 3 Replies

SQL Server :: How To Return A Value From A SELECT Statement In SP

Jan 29, 2011

Here is the deal. I need to create a Stored Procedure that has 2 SELECT statements. Now in ASP.NET, I know that a DataSet can have multiple tables and the DataAdapter can do just that. I am stuck with a scenario where I need to get a value from the first SELECT statement which will later be used in the second SELECT statement. I am creating a SP that passes one parameter (@AnswerID). Which should give me 2 resultsets. See below SELECT statements.


So how do I get the QuestionID from the first SELECT statement?

Basically I am creating a Questionnaire that has Question Dependacies. So from the above SQL statements I would like to display the Question Title with it's relevant answer options but depending on the previous Answer selected hence the @AnswerID parameter.

The reason why I want to use 2 SELECT statements is because I don't want to have 2 roundtrips to th server. So in my code the DataAdapter should return 2 resultsets and fill the DataSet.

If there is any another solution that can prevent 2 roundtrips to the server, I would most definitely like to know how to do it.

View 10 Replies

SQL Server :: How To Proof SQL Select Statement

Jul 21, 2010

Here's my SQL Statement


Unfortunately, I get the following error message:Message="The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

View 3 Replies

Copyrights 2005-15, All rights reserved