data from two tables 1

[nonprogrammer]

Hello All,

I am writing a script that requires me to use two different tables so what I did I copied the connection from a script that I had and modified a bit

<%


'Dimension variables
Dim adoConpoints 			'Holds the Database Connection Object
Dim rsGuestbookpoints			'Holds the recordset for the record to be updated
Dim strSQLpoints			'Holds the SQL query for the database
		'Holds the record number to be updated

'Read in the record number to be updated


'Create an ADO connection odject
Set adoConpoints = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoConpoints.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ncaa.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rsGuestbookpoints = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQLpoints = "SELECT gmpoints.* FROM gmpoints " 

'Open the recordset with the SQL query 
rsGuestbookpoints.Open strSQLpoints, adoConpoints
%>


<%


'Dimension variables
Dim adoCon 			'Holds the Database Connection Object
Dim rsGuestbook			'Holds the recordset for the record to be updated
Dim strSQL			'Holds the SQL query for the database
Dim lngRecordNo			'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("ID"))

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ncaa.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT gm.* FROM gm WHERE ID=" & lngRecordNo

'Open the recordset with the SQL query 
rsGuestbook.Open strSQL, adoCon
%>

and then I did this to show my data

<td width="120" rowspan="2" align="center"><%= rsGuestbook ("B1R1G1")%><% if rsGuestbook ("B1RG1") = rsGuestbookpoints     ("atlar1g1") then rsGuestbookpoints ("atl1g1p") = "3" %> </td>

I am trying to verify if B1R1G1 is equal to atlar1g1 then insert a number 2 in atl1g1p

when I try this I get an error

Error Type:
Provider (0x80004005)
Unspecified error
/marchmadness/viewtest.asp, line 17


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)

help please!!

 

[Tarwn]

This is likely something to do with the fact that yuo tried to open two simultaneous connections to an Access database, which isn't something Access can handle.

Once you have a connection open, you can re-use that connection to execute any other queries on that database you might need to do.

So, for instance, you could modify your code like so:

<%


'Dimension variables
Dim adoConpoints             'Holds the Database Connection Object
Dim rsGuestbookpoints            'Holds the recordset for the record to be updated
Dim strSQLpoints            'Holds the SQL query for the database
        'Holds the record number to be updated

'Read in the record number to be updated


'Create an ADO connection odject
Set adoConpoints = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoConpoints.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ncaa.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rsGuestbookpoints = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQLpoints = "SELECT gmpoints.* FROM gmpoints "

'Open the recordset with the SQL query
rsGuestbookpoints.Open strSQLpoints, adoConpoints

'Dimension variables
[red][s]Dim adoCon             'Holds the Database Connection Object[/s][/red]
Dim rsGuestbook            'Holds the recordset for the record to be updated
Dim strSQL            'Holds the SQL query for the database
Dim lngRecordNo            'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("ID"))

[red][s]'Create an ADO connection odject[/s][/red]
[red][s]Set adoCon = Server.CreateObject("ADODB.Connection")[/s][/red]

[red][s]'Set an active connection to the Connection object using a DSN-less connection[/s][/red]
[red][s]adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ncaa.mdb")[/s][/red]

[red][s]'Set an active connection to the Connection object using DSN connection[/s][/red]
[red][s]'adoCon.Open "DSN=guestbook"[/s][/red]

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT gm.* FROM gm WHERE ID=" & lngRecordNo

'Open the recordset with the SQL query
rsGuestbook.Open strSQL, [highlight]adoConpoints[/highlight]
%>

Access cannot handle multiple connections just as it cannot handle two people having a single file open simultaneously or even an ADO connection while a file is already open. However, even with databases that can handle multiple connections, it is often easier and more efficient to re-use your existing open connection, rather than to create another.

-T

 

[nonprogrammer]

Sorry for the delay reply I have been very sick. I just got back to work. I tried it and at first it did not work but after changing a couple of things it worked.


I really appreciate it. Thank you