Assigning SQL SELECT query to array

[BlackIce]

Hi this is my first thread so I'm hoping someone can help me

Scenario: Writing a scanning program, thing is I can't have duplicate file names(which I generate). The img file paths r saved in a database.

I'm trying to write a function that will return all the records I need using a SQL SELECT. To make this work I need to put all the retrieved records(just the one field from the database) into an array.

My Problem: I have no idea how to put the retrieved SQL query into an array and then return it. Thanks appreciate any help I can get

This is the Function

Private Function fileNameAcquisition(ByVal path As String, ByVal id As Integer) As String
Dim conn As New SqlConnection
Dim dr As SqlDataReader
Dim cmd As SqlCommand
Dim sql As String
Dim serve As String = CStr(x.Element("server"))
Dim cat As String = CStr(x.Element("catalog"))
Dim use As String = CStr(x.Element("userid"))
Dim pwd As String = CStr(x.Element("pwd"))
Dim dataBasePath() As String
Dim i As Integer

Try
conn.ConnectionString = ("server=" + serve + ";" + "initial catalog=" + cat + ";" + "user id=" + use + ";" + "pwd=" + pwd + ";")
conn.Open()

sql = "SELECT FilePathName FROM [Scans] WHERE ID = " & id & " AND FilePathName LIKE '%" & path & "%'"
cmd = New SqlCommand(sql, conn)
dr = cmd.ExecuteReader()

While dr.Read()
ReDim Preserve dataBasePath(i)
dataBasePath(i) = Convert.ToString(dr("FilePathName"))
MessageBox.Show(dataBasePath(i))
i += 1
Return dataBasePath(i)
End While

dr.Close()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function