Asp

reading and writing databases

[jackal]

anyone know how i read and write to two different tables in the same databases via the same asp page?

[txbakers]

Just have two insert statements and execute them in succession.

If you used Dreamweaver, it looks like you can't do more than one, but you can. Use the same ADODB connection, and just create different queries, then execute them.

If you need a code example, let me know.

[Dreamquick]

It's quite easy really - to read two tables at the same time you create two recordsets and use them indepently, to write to the table you can use the execute method of the connection to run SQL statements.

Sorry it's a bit ugly but these forums aren't really designed to insert code snippets, but hopefully this should you out a little. And before anyone asks the first line is an alternative to using the adovbs.inc file...

This is just an ugly cludge of code which gets each fictional test user, then retrieves all their transaction names and then write them out into their user record in sequence. But it does access two tables at once and writes some data back.

<!-- METADATA TYPE="TypeLib" FILE="C:\Program Files\Common Files\System\ADO\MSADO15.dll" -->
<%
Dim conDB, rstA, rstB
Dim sSQL

Set conDB = Server.CreateObject("ADODB.Connection")
Set rstA = Server.CreateObject("ADODB.Recordset")
Set rstB = Server.CreateObject("ADODB.Recordset")

'Outer loop
sSQL = "SELECT ID, Name FROM TonyUsers WHERE TestUser = 1"
rstA.Open sSQL, conDB, adOpenForwardOnly, adLockReadOnly
Do Until rstA.EOF

'Inner loop
sSQL = "SELECT MyReference FROM TonyUsers_Orders WHERE UserID = " & rstA("ID").Value
rstA.Open sSQL, conDB, adOpenForwardOnly, adLockReadOnly
Do Until rstB.EOF

sSQL = "UPDATE TonyUsers SET Notes = Notes + '" & rstB("MyReference") & ", ' WHERE ID = " & rstA("ID").Value
conDB.Execute sSQL

rstB.MoveNext
Loop
rstB.Close

sSQL = "UPDATE TonyUsers SET Notes = LEFT( Notes, LEN( Notes ) - 2 ) WHERE RIGHT( Notes, 2 ) = ', ' AND ID = " & rstA("ID").Value
conDB.Execute sSQL

rstA.MoveNext
Loop
rstA.Close

Set rstB = Nothing
Set rstA = Nothing
Set conDB = Nothing
%>

- Tony