Query SQL Table And Store Results In Array

Question

I am wanting to query a SQL Server Table and store the results from that query in an array. I am only returning a Distinct() from one field so it should be pretty straight forward. My syntax produces the error

Invalid Qualifier

On this line of code Set r = conn.Execute and it highlights the word conn How should I re-write this syntax to still use ADO but be able to successfully run this procedure?

Public Function ReturnData()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim f As ADODB.Field
Dim conn As String
Dim arrResults() As Variant
Set c = New ADODB.Connection
With c
.Provider = "sqloledb.1"
With .Properties
    .Item("Data Source") = "ServerName"
    .Item("Initial Catalog") = "Database"
    .Item("PassWord") = "password"
    .Item("User ID") = "user"
End With
.Open
Set r = conn.Execute("SELECT Distinct(Name) from registered where cancelled IS NULL;")
i = 0
r.MoveFirst
Do Until rst.EOF
    arrResults(i) = rst.Fields(0)
    i = i + 1
Loop
rst.Close
Set rst = Nothing
c.Close
End Function

you've got conn is a string there, c is a connection - can you do c.execute — Cato
– Cato, Commented Nov 3, 2016 at 12:40
There are a few problems with this code. Did you copy/paste this from the internet? There are a several occasions where you call on the variable rst. But this isn't defined. Suspect you should replace with r. There two with statements. But only 1 'End With'. The variable i is not declared. Dim i AS Integer to fix. — David Rushton
– David Rushton, Commented Nov 3, 2016 at 12:48

Dick Kusleika · Accepted Answer · 2016-11-03 14:14:53Z

1

Another way to get a recordset into an array is to use Split and Recordset.GetString. It avoids the loop anyway. Here's an example.

Public Sub RsToArray()

    Dim adoCon As ADODB.Connection
    Dim adoRs As ADODB.Recordset
    Dim vaLocations As Variant

    Set adoCon = New ADODB.Connection
    adoCon.Open sConn
    Set adoRs = adoCon.Execute("SELECT DISTINCT LocationName FROM Locations")

    vaLocations = Split(adoRs.GetString, vbCr)

    Debug.Print Join(vaLocations, "|")

    adoRs.Close
    adoCon.Close

    Set adoRs = Nothing
    Set adoCon = Nothing

End Sub

answered Nov 3, 2016 at 14:14

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cato · Accepted Answer · 2016-11-03 12:41:51Z

0

you've got conn is a string there, c is a connection - can you do c.execute

try

Set r = c.Execute(...

I'm not sure if you need a command object, if you do, then see

https://msdn.microsoft.com/en-us/library/ms675065%28v=vs.85%29.aspx

to see how to make a command for your connection and execute it

Anyhow, at the moment you are trying to 'execute' on a string - string is a primitive type and does not execute on the DB - I think you just mixed c and conn up - let us know how it goes

answered Nov 3, 2016 at 12:41

Cato

3,72711 silver badges12 bronze badges

Collectives™ on Stack Overflow

Query SQL Table And Store Results In Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related