ADODB Connection to SQL server

Microsoft Windows vista home premium w/s...
August 31, 2011 at 21:30:25
Specs: Windows 7
  Dim objConnection = CreateObject("ADODB.Connection")
            Dim strConnect = "Driver={SQL Server};Server=DCH-INF33;Database=SMS_QCS;Trusted_Connection=TRUE"
            objConnection.Open(strConnect)

            Dim strSQLQuery = "SELECT coll.Name " & _
                                "FROM dbo.v_Collection coll " & _
                                "WHERE CollectionID in " & _
                                  "(SELECT parentCollectionID " & _
                                   "FROM dbo.v_CollectToSubCollect csc " & _
                                   "WHERE csc.subCollectionID in " & _
                                     "(SELECT c.CollectionID " & _
                                      "FROM dbo.v_Collection c " & _
                                       " WHERE c.Name = '" & lstAgency.SelectedItem & "' ) ) " & _
                                "AND (coll.Name like 'In_%' Or coll.Name like 'Ti_%') " & _
                                "ORDER BY coll.Name"
            Dim objRecordSet = CreateObject("ADODB.Recordset")
            objRecordSet = objConnection.execute(strSQLQuery)

okay i can return the information from my query using objRecordSet.getstring()
I would like to load each line into an array, and i cannot figure it out.

please help


See More: ADODB Connection to SQL server

Report •

#1
September 1, 2011 at 10:43:09
What do you plan on doing from the array that you cannot do from the record set?

objRecordSet = objConnection.execute(strSQLQuery)
This might need to be set objRecordSet =, depending on which VB you're using.

Dim objRecordSet = CreateObject("ADODB.Recordset")
This line creates an empty Recordset, which is disposed of on the next line.

How To Ask Questions The Smart Way


Report •

#2
September 1, 2011 at 17:12:03
Im only retrieving data from the record set.

The data is then put into a Treeview

I am using Visual Studio 2010

The problem i am having is when i use objRecordSet.getstring() all the entrys in the table come out in one line in my treeview.


Report •

#3
September 2, 2011 at 15:00:48
I am using Visual Studio 2010
Then don't use VBScript syntax. .NET has its own, better DB access tools. Use them.

The problem i am having is when i use objRecordSet.getstring() all the entrys in the table come out in one line in my treeview.
Yes, that's what it does. It makes it easier to dump the table to a flat file. If that's not what you want, don't use it. Maybe Item() is more what you're looking for?

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
September 11, 2011 at 15:57:52
This is how i ended up doing it.

Dim objRecordSetResults = objRecordSet.getstring(, , , ",", )
Dim DatabaseResultsArray() As String = objRecordSetResults.Split(",")


Report •

Ask Question