Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help to connect to a Access DataBase

Status
Not open for further replies.
rdrunner40

rdrunner40

MIS
Apr 25, 2002
156
GB
Hello,

I have a simple query

I want to connect to a database which contains multiple tables and i want to run the following SQL to produce a ASP

Here is what i got so far.... my access databse is called PCInventory... and there are the following tables inside the database, SampleCompany,CurrentSoftware,Office&outlook,OperatingSystemSoftware & Application Info.


' SQL = "SELECT SampleCompany.PCName, SampleCompany.Ram, SampleCompany.HDDrive, SampleCompany.CDType, SampleCompany.Monitor, SampleCompany.CurrentUser, SampleCompany.PortNo, SampleCompany.JMCKit, SampleCompany.ServiceTAG, SampleCompany.CPU, SampleCompany.[Dept Used], SampleCompany.PcType, ApplicationInfo.Application, ApplicationInfo.LicenseNumber, OperatingSystemSoftware.OperatingSystem, OperatingSystemSoftware.OEMNumber, [Office&Outlook].OfficeVersion, [Office&Outlook].Office_OEMNumber, [Office&Outlook].OutLookVersion, [Office&Outlook].OutLookOEMNumber FROM ((SampleCompany INNER JOIN OperatingSystemSoftware ON SampleCompany.ServiceTAG = OperatingSystemSoftware.ServiceTAG) LEFT JOIN ApplicationInfo ON SampleCompany.ServiceTAG = ApplicationInfo.ServiceTag) INNER JOIN [Office&Outlook] ON SampleCompany.ServiceTAG = [Office&Outlook].ServiceTAG;"


I have got the following from another ASP i wrote a while ago but this only had a single table and it seems to work fine - the difference here is i want to connect to multiple tables contained within the database. I am stumped. I can only find to open a RecordSet is there a way i can open a SQL Query made up of the output from the four tables ?

'Create and Open Connection Object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "PCInventory"


'Create and Open Recordset Object
Set RsPcInventory = Server.CreateObject("ADODB.Recordset")

RsPcInventory.ActiveConnection = OBJdbConnection
RsPcInventory.CursorType = adOpenKeyset
RsPcInventory.LockType = adLockOptimistic

RsPcInventory.Source = "Maintenance"

RsPcInventory.Open(sql)


I am stumped as to what to do - i am knew to ASP but are keen to do more. Any help appreciated and i would also like there to be a login form as well to log a user on at a later stage as this will be a secure part of our company intranet.

Any links or help much appreciated.

Regards

Murray
 
Sort by date Sort by votes
Hi Murray,

Here is a template you can use. To use it you just have to plug your own DATABASE and SQL statement into it...
Code: 
<%
Dim x_ConnStr, x_Conn, x_rs, x_sql

'Here enter the FULL path to the database file
x_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\[URL unfurl="true"]wwwdata\database.mdb;"[/URL]

Set x_Conn = Server.CreateObject("ADODB.Connection")
Set x_rs = Server.CreateObject("ADODB.Recordset")
'Here enter your own sql statement
x_sql = "SELECT * FROM [sometable]"

'This opens the Connection using the Connection String
x_Conn.Open x_ConnStr

'Here we open the record set with x_sql.  It also sets x_Conn as the active connection and sets the CursorType and LockType
x_rs.Open x_sql, x_Conn, 1, 2

'Now you can reference anything in your query
%>
<html>
<body>
<center>
   The PC Name is: <%= x_rs("SampleCompany.PCName") %>

Hope this helped...

LL
 
Upvote 0 Downvote
The easiest way is to create an Access query that links the tables to produce the output in the way that you need, then use a simple SELECT * FROM QueryName.

Queries are more efficient than ad hoc SQL queries and are easier to maintain and document.
 
Upvote 0 Downvote
Hi there

I have done as you suggested and when i do the following i get the message as below....

' The PC Name is:
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

What am i missing ?

how can i see if the DSN is correct on the server and the connection is valid ?


This is what i pasted in

'Here enter the FULL path to the database file
x_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\srv01\PBIntranet\ITInventory.mdb;"

Set x_Conn = Server.CreateObject("ADODB.Connection")
Set x_rs = Server.CreateObject("ADODB.Recordset")

'Here enter your own sql statement
x_sql = "SELECT SampleCompany.PCName, SampleCompany.Ram, SampleCompany.HDDrive, SampleCompany.CDType, SampleCompany.Monitor, SampleCompany.CurrentUser, SampleCompany.PortNo, SampleCompany.JMCKit, SampleCompany.ServiceTAG, SampleCompany.CPU, SampleCompany.[Dept Used], SampleCompany.PcType, ApplicationInfo.Application, ApplicationInfo.LicenseNumber, OperatingSystemSoftware.OperatingSystem, OperatingSystemSoftware.OEMNumber, [Office&Outlook].OfficeVersion, [Office&Outlook].Office_OEMNumber, [Office&Outlook].OutLookVersion, [Office&Outlook].OutLookOEMNumber FROM ((SampleCompany INNER JOIN OperatingSystemSoftware ON SampleCompany.ServiceTAG = OperatingSystemSoftware.ServiceTAG) LEFT JOIN ApplicationInfo ON SampleCompany.ServiceTAG = ApplicationInfo.ServiceTag) INNER JOIN [Office&Outlook] ON SampleCompany.ServiceTAG = [Office&Outlook].ServiceTAG;"

'This opens the Connection using the Connection String
x_Conn.Open x_ConnStr

'Here we open the record set with x_sql. It also sets x_Conn as the active connection and sets the CursorType and LockType
x_rs.Open x_sql, x_Conn, 1, 2

'Now you can reference anything in your query
%>

</font></center>
<p>
<center>
' The PC Name is: <%= x_rs("SampleCompany.Ram")%>

All i got for this was the above error.
 
Upvote 0 Downvote
Hi Double02

I have pasted the ammended piece of code in and i am still getting the same error i am wondering if my connection to the DSN i made on the windows 2003 server is correct.

Is there a way i can see that i am connected and that the sql is correct ?

I copied the sql from within Access and it works fine there.

Am i being to optomistic in what i am wanting to do here ?

Thank you for the prompt replies but i still need to have my hand held on this one.

Regards

Murray
 
Upvote 0 Downvote
If your connection didn't work, you wouldn't get the Recordset error, you'd get a different one :)

Well, again, that's one of the advantages from creating and using the view - it works like a single table and is also much easier to test. Specifying all those individual fields is very inefficient from both a coding and server POV.

Unfortunately the Access SQL can be somewhat proprietary, so be careful if just trying to copy and paste. I can't see an obvious error, but didn't look too closely.

Sounds like you need to do some response.writes to see what's going on.

You could use the For Each construction to iterate through the fields collection and output the names of those returned, if you know how to use that, or just use the ordinal numbers, eg
Response.Write x_rs.FIELDS(0).Name & "<BR>"
Response.Write x_rs.FIELDS(1).Name & "<BR>"
etc...
 
Upvote 0 Downvote
Try changing this line
Code: 
x_rs.Open x_sql, x_Conn, 1, 2

to this
Code: 
x_rs.Open x_sql, x_ConnStr, 1, 2

Paul

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
237
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
166
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
190
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
181
leifoet
leifoet
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
486
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top