ASP and Access

[ianripping]

How do I get asp to look at the first record in an access file: -

EG - Access File - feedback.mdb
Table name - details
Fields - Name, Comments

[buntine]

Hey, luckily, its quite simple to achieve this.

There are a couple of methods, though, this is the easiest and most efficient if your database uses an auto-incrementing primary key.

Add a new field in your datails table and name it rec_id, or something similar. Set the type as 'autonumber' and then set this field as the primary key.

This field will be automatically given a value each time a new record is entered into the database.

Then, simply use this SQL query to extract the first record from the table.

ASP:


sql_query = "SELECT TOP 1 * FROM details ORDER BY rec_id DESC"


Pseudocode:

sql query equals "select top record from details order by rec_id in descending order"



If, for some reason you cannot add a new field, there are other methods of extracting the first record only. Post if you need these other methods.

Regards,
Andrew Buntine,

[ianripping]

Ok this is great but could you give me all the code I need to make an asp page which will achieve this?

Thanks

[buntine]

Yer, no probs.

I will assume you are using the standard MS Access Driver to set your connection string.



dim conn, conn_string
dim sql_query, rs

set conn = server.createObject("ADODB.connectio") '|Set ADO connection object.

'|Construct out connection string.
conn_string = "DBQ=" & server.mapPath("/feedback.mdb") & ";"
conn_string = conn_string & "Driver={Microsoft Access Driver (*.mdb)}"
conn.open(conn_string) '|Finally, activate the connection.

set rs = server.createObject("ADODB.recordSet") '|Set the ADO recordSet object.

sql_query = "SELECT TOP 1 * FROM details ORDER BY rec_id DESC"
rs.open(sql_query) '|Execute the SQL query.

'|Now we print the record out to the screen.
response.write("Name: " & rs("Name") & vbCrLf)
response.write("Comments: " & rs("Comments") & vbCrLf)

'|Last but not least, we close our data objects to free resources.
set rs = nothing
rs.close
set conn = nothing
conn.close


The preceding script should do the trick.

Regards,
Andrew Buntine.

[ianripping]

Excellent, thanks very much!

[buntine]

Your welcome. Post if you happen to run into problems.

[retesh_gondal]

declare ur cursor type as 3 ie:
Dim rs
rs.coursortype=3

this will help u to use functions like rs.movefirst or rs.movelast

Hope this helps..!!
Retesh

[buntine]

ASP has a shortcut to the cursortype property. And i actually forgot a tad of code in my example which may or may not cause an error when the script is executed.

This line:

rs.open(sql_query) '|Execute the SQL query.


Should be changed to:

rs.open(sql_query), conn, 3 '|Execute the SQL query.


This will automatically set the 'activeConnection' and 'cursorType' attributes in the one line.

Regards,
Andrew Buntine.