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?)
-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.