Return value from access db

[TotalAddict]

Hi there,

I am stuck with returning a record from an Access database using VB.NET

I want to return a value from the database but I am having a bit of trouble with the method on how to do it, done some searching but not sure if I am looking for the right thing.

Here is an example:

I have Locations which I am using for a stock control system and I have a set of locations such as

A1
A2
A3
A4
etc...

My database is set up like this

LocationID Occupied
A1 True
A2 False
A3 False
A4 False

I want to be able to run a query which returns the value of a Location which Occupied = False and

tell me the location i.e A2, A3 etc, but only want it to return 1 record as I want it to tell me the Location and then when confirmed by the user and its booked in I want to change the value to True.

I was thinking something along the lines of:

strSQL = "SELECT ([LocationID]) FROM [Locations] LIMIT 1 WHERE
([Occupied] = FALSE')"

and then want to be able to tell me which LocationID it has selected and return that LocationID to the user but I am unsure of the method on how to do this.

I hope this made some sort of sense.

Thank you so much in advance for any help, it would be greatly appreciated.

[alliancetekinc]

You can use the OldeDBCommand and related classes to execute the required Query. Your query is correct. You will get the single locationID thru that Query. display this result to user on the screen thru any GUI controls. On click event of the Confirm button you can execute the Update Query like this.

Update Locations
Set Occupied = true
Where LocationID = "SelectedLocation"

Where seletedLocation will be the location returned by your query.

Let me know if you still have any problem .