Programming in ASP

[AlastairM]

In a similar vein to Alans thread on Access, I'm happy to have a stab at any ASP/Access/ADO questions, or at the very least be able to pass you onto forums who can help you :D.

Ditto general HTML/CSS questions.

cheers

alastair

[Alan]

I've got one for you AlastairM!

What is the ADO equivalent (using Jet Engine) of:

Dim DB as Database, rst as RecordSet

Set DB = DBEngine(0)(0)

Set rst = DB.OpenRecordSet ("Table1")

...

(Operations on recordset, eiother updating or adding records)

I've forgotten what I did learn about ADO but all the samples I saw didn't seem to tie in with the DAO ones I used. The particular problem was in updating or adding records.

[AlastairM]

bugger, bluff's been called already :D


Hmm. Never used DAO myself but in ASP i'd do:


<%
dim objConn
dim objCmd
dim sqlString
dim varConn

'connectionstring using the JET OLEDB provider
varConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\your_db.mdb;"

'SQL string
'(skipped code to get these values from user input etc.)
sqlString = "INSERT INTO myTable (field1, field2) VALUES ('" & value1 & "','" & value2 & "')


'create a connection object and open it
set objConn = server.createObject("ADODB.connection")
objConn.open varConn

;create a command object
set objCMD = server.createObject("ADODB.command")

objCmd.activeConnection = objConn

objCmd.commandText = sqlString

'execute query
objCmd.execute

'clean up
set objCmd = nothing

objConn.close
set objConn = nothing

%>


Thats usually how I do it, using SQL strings and the ADO command object. It can be done in less code by using an implicit connection but I prefer the slightly verbose method when it comes to debugging

You can also do it using by calling the .addnew and .update methods of the recordset object which might look more similar to the DAO way you'd do it. Haven't used that in a while (I find it more flexible to work with SQL) and my books are at work :)

if what if suggested is a mile off from what you were expecting I'll look up the recordset method tomorrow.

cheers

alastair

[AlastairM]

OK, this might look a bit more familiar:


<%

dim objConn
dim rsResults

'create recordset and connection
set objConn = server.createObject("ADODB.connection")
set rsResults = server.createObject("ADODB.recordset")

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\your_database.mdb;"

'some ADO constants skipped, command types and lock types
rsResults.open "tblProjects", objConn

'adding new records field at a time.
'can also use an array of field names and an array of values

rsResults.addnew
rsResults("fieldName") = "value1"
rsResults("fieldname2") = "value2"

'...and so on...

'similar for updating records, only you'd need to loop through the recordset to find the record you need first


'commit changes
rsResults.update

'clean up

rsResults.close
set rsResults = nothing

objConn.close
set objConn = nothing


%>

[Alan]

Thank you for that. It's not far off. The reason I'm asking is that I'm doing a large database in a new job and the company uses Access 2002 and although the DAO reference library is still there, I fear I might have to go to ADO.

Your right about using the full explicit connection string, that way, the shortcut being removed in a later revision doesn't hurt you.

If I do need to progress on ADO I'll come back with explicit questions (as long as you don't mind) rather than a general purpose catch all.

Cheers

[AlastairM]

No bother :D

cheers

alastair

[MTD]

or try this

Create a file called dsn.inc

Add the header <!--#INCLUDE FILE="DSN.inc"--> to the top of your pages and that will add the database connection properties to that page :)

Code example below.

<%
'Dim sDBName
Session.timeout = 3
If IsObject(Session("Rma_conn")) Then
Set conn = Session("Rma_conn")
Else


sDBName = "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\InetPub\DB\FLS\WSL_INVENTORY.mdb"
Set conn = Server.CreateObject("ADODB.Connection")
conn.open sDBName
End If
%>

[Clarksy]

Caching connections and recordsets, infact storing any objects in a session or application variable is actually pretty heavy on the server rescources. I guess thats why you have set you session to time out after 3 mins.

As you have the code to create a recordset object included in every page i would just use that line of code everytime. Also if the visitor has cookies disabled you wont sustain a session and your code will have to create the object each time anyway.


Just my thoughts on it.

[hypnotic_monkey]

In a similar vein to Alans thread on Access, I'm happy to have a stab at any ASP/Access/ADO questions, or at the very least be able to pass you onto forums who can help you :D.

Ditto general HTML/CSS questions.

cheers

alastair
Ok, i have a question for you to have a stab at, or atleast give me a link.

I have listed all the data (ado/sql) in a table (asp). But i want to hyperlink each peice of data to a different page with ID. e.g.

Colum1
data1 > showdata.asp?id=1
data2 > show data.asp?id=2

Colum2
asd1 > showasd?id=1
asd2 > showasd?id=2

Please help me on this one.

[AlastairM]

holy thread resurection batman :D

Right let's see, If i understand it correctly you have a table containging some information and you want to show the relevant table row based on the ID in the url??

I'm not sure what column 1 and 2 represent, are they part of the same table?? Could you show me a sceenshot of your table in access, because I think you might be making a more fundament mistake with your table layout, either that or I'm picking you up wrong :D

You can also google 'asp master detail -.net' to get some examples.

cheers

alastair

[Clarksy]

hypnotic_monkey, i've edited your post to remove the font size tags. This question has been answered here:

http://forums.kustompcs.co.uk/showthread.php?t=32040

Please don't double post.