Visual Studio 2010 and getting data from a microsoft access DB

Question

I hope this is posted in the correct section of the forum, if not please accept my apologies and point me in the direction of the right forum.

 

The problem:

 

I am using Visual Studio (VS) 2010 and an Access DB, I am trying to get specific data from the DB into a gridview on a web page in VS.

 

Visual Studio can see the DB and it is on view in the Server Explorer and I have tested connection when setting up the two together.

 

In my DB I have three tables, Bookings, Clients, Rooms. The information I want the gridview to show is:

 

Room Number , Last Name , Start Time, End Time, Subject for todays date, so whenever the web page is loaded the data will show relevant to the current date.

 

Solution: This is what I have tried and has not worked.

 

I tried to use the query builder but will not allow me to include WHERE in the statement.

 

Using the following code I tried to write that statement myself:

 

SELECT Room Number, Start Time, End Time, Subject FROM Bookings

WHERE Booking Date = “Todays Date”

 

Before I even finished the sql statement it was full of squiggly lines, can you please help

Answer 1

Hello,

You SQL Statement do not look valid. If field name contains spaces (no good idea for design), you have to set them in brackets like

SELECT [Room Number], [Start Time], [End Time], Subject
FROM Bookings
WHERE [Booking Date] = “Todays Date”

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

Thank you for your reply

I will start by changing the fields of my database so that hey contain no spaces, this is a big school boy error on my behalf but it has been a long time since I have layed with a database.

The document I was using fo the sql did not mention brackets, I will have a look into this, again thank you.

 

Answer 3

If I remind it right, MS Access accept also apostrophe like

SELECT 'Room Number', 'Start Time', 'End Time', Subject

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de