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.ClosePlease 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.