I've been working on getting a SQL DB to return records through an ADODB.Connection object and have had zero luck getting it to work in VB6. Whereas I created an ASP form using VBS to produce the exact same functionality with no issues.

In VB6, I've included the following references: Visual Basic for Applications
-Visual Basic Runtime objects and procedures
-Visual Basic objects and procedures (dupe of the above?)
-OLE Automation
-Microsoft ActiveX Data Objects 2.1 Library

DO I also need to include Microsoft Active Server Pages object Library and/or Microsoft Active Server Pages ObjectContext Object Library?

This is what the VB6 code looks like:

'Note this is VB6 Code
Dim oConn As ADODB.Connection 'as a Connection object
Dim rs As ADODB.Recordset

Set oConn = New ADODB.Connection 'instantiate a new instance of

oConn.Open "UID=user;PWD=Pw;DSN=dbdsn" 'These are dummy values, of course
'Returns error saying there is no default driver and no data source name

Set rs = oConn.Execute("Select * from categories")

rs.Close
 
oConn.Close

Please note that I am NOT explicitly printing anything as my proficiency with VB6 is not very good. What I am concerned with is the fact the connection is not going. You'll see why that's a point of interest in the next snippet below (in VBS):

<%@ Language=VBScript %>
<html>
<head>
<title>Test Page of VBS Fail</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
</head>
<body>
<%
Dim conn
Dim rs
Dim rd
Dim count

count = 0

Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Set rd = Server.CreateObject("ADODB.RecordSet")
conn.Open "DSN=dbdsn;UID=user;PWD=Pw" 'ditto on these credentials too

'rs.Execute "Select * from categories",conn

rs.Open "SELECT * from categories",conn

If rs.EOF Then
Response.Write "Nothing found"
Else
Do While NOT rs.EOF
	For Each x in rs.Fields
		Response.Write x.name
		Response.Write " "
		Response.Write x.value
		Response.Write "<br>"
		Next
		Response.Write "<br>"
		rs.MoveNext
		Loop
End If

rd.Open "SELECT COUNT(*) as 'total categories printed: ' from categories",conn

for Each y in rd.Fields
	Response.Write y.name
	Response.Write " "
	Response.Write y.value
	next
rs.Close
Set rs=nothing
rd.Close
Set rd=nothing
conn.Close
Set conn=nothing
%>
</body>
</html>

I've read a little on the difference between using an ADODB.Recordset object compared to using a Command object or simply performing the query and getting a recordset by using ADODB.Connection.Execute. The difference is in what kind of recordset is returned. I've figured that much out on my own. The focus here is on the fact that I'm using the same set of ADODB objects to get that connection open. All sources I've read on doing this through an ADODB object with a System DSN do not specify that the DSN, UID, and PWD need to be in an explicit order (I've tested this with the VBS and the order is inconsequential).

I know, I know. I should be doing some sort of checking when the Open call is made but since that error pops up before that, there isn't much to check except do something like:

If Not isObject(oConn) Then MsgBox "Could not instantiate object"

Right now I'm pretty much baffled why this works in VBS but not in VB6. Any advice on it would be greatly appreciated as I've been working on this issue for more than a day. Even more so when the working VBS took me only 20 minutes of research and 5 minutes to write.

Thanks in advance.

Dim Rs As ADODB.Recordset
Set Rs = New AdoDb.Recordset
Rs.Open..., conn, ...

You have the conn right (set = new), but missed the rs...

Good Luck

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.