passing arrary threw query

[Ravi Singhal]

hi ,
i am writing a programme in vb6. I want to check whether my cities are matching with cities in database.
so i want to ask that how to pass a array to a database by query.
I have written the following code please help me

Dim CITYARRAY(5) As String
CITYARRAY(0) = "MIAMI"
CITYARRAY(1) = "FRED"
CITYARRAY(2) = "INDIAN HILL"
CITYARRAY(3) = "MARTINSVILLE"
CITYARRAY(4) = "MARY"

Dim connection As New ADODB.connection
connection.Open ("Provider =Microsoft.Jet.OLEDB.4.0;Data Source =L:\RDES Training\Geocode World RL6.0\USGEO.MDB;PERSIST SECURITY INFO = FALSE")
Dim QUERY As String
QUERY = "SELECT CITY FROM USCITY WHERE CITY IN ('" & CITYARRAY & "') "
Dim RECORDSET As New ADODB.RECORDSET
RECORDSET.Open QUERY, connection, adOpenDynamic, adLockOptimistic

[DenisOxon]

What is your question ?

[choudhuryshouvi]

Try this code...
'used stuffs :- button(command1), list box(list1) , database,table and fields name remain same
Option Explicit

Dim connection As ADODB.connection
Dim recordset As ADODB.recordset
Dim CITYARRAY(5) As String, QUERY As String

Private Sub Command1_Click()
Dim i, f, nf As Integer

f = 0 'variable for tracking total no. of cities found in the database
nf = 0 'variable for tracking total no. of cities not found in the database

CITYARRAY(0) = "MIAMI"
CITYARRAY(1) = "FRED"
CITYARRAY(2) = "INDIAN HILL"
CITYARRAY(3) = "MARTINSVILLE"
CITYARRAY(4) = "MARY"

'looping through each element of the array,sending its value through the query and checking whether this value does exist in the database or not.
'if match found then increasing the f variable by 1 & printing msg in the listbox otherwise increasing the nf variable by 1 & also displaying a msg in the
'listbox
For i = 0 To 4 Step 1
QUERY = "select city from uscity where city in(" & Chr(34) & CITYARRAY(i) & Chr(34) & ")"
recordset.Open QUERY, connection, adOpenDynamic, adLockOptimistic

If recordset.RecordCount > 0 Then
List1.AddItem CITYARRAY(i) & " [ Found in the database ]"
f = f + 1
Else
List1.AddItem CITYARRAY(i) & " [ Not Found in the database ]"
nf = nf + 1
End If
recordset.Close
Next i

'printing summary of the above query
List1.AddItem ""
List1.AddItem "-=Result of Searching=-"
List1.AddItem "Cities found : " & f
List1.AddItem "Cities not found : " & nf
End Sub

Private Sub Form_Load()
'initiating the connection and recordset objects here. try to avoid doing the same in general declaration section
Set connection = New ADODB.connection
Set recordset = New ADODB.recordset

recordset.CursorLocation = adUseClient
recordset.CursorType = adOpenDynamic

'specifiying location of the database file dynamically so there is no need to mention the path of the database everytime the location of that file is
'changed. app.path automatically detects path of the current working directory of the project file.
connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\usgeo.mdb" & ";PERSIST SECURITY INFO=FALSE"
End Sub

[QVeen72]

Hi ,

Try This :

Dim NewStr As String
NewStr = "'" & Join(CITYARR,"','") & "'"

QUERY = "SELECT CITY FROM USCITY WHERE CITY IN (" & NewStr & ")"

REgards
Veena

[manoshailu]

Hai ravi,
u can use below coding according to ur requirement. Al the best
Try below coding.....
Private Sub Command1_Click()
Dim CITYARRAY(5) As String
CITYARRAY(0) = "MIAMI"
CITYARRAY(1) = "FRED"
CITYARRAY(2) = "INDIAN HILL"
CITYARRAY(3) = "MARTINSVILLE"
CITYARRAY(4) = "MARY"
Set rs = Nothing
For i = 0 To 4
rs.Open "select city from uscity where city = '" & CITYARRAY(i) & "'", cn, adOpenDynamic, adLockOptimistic
If rs.BOF = False And rs.EOF = False Then
Print rs(0)
End If
rs.Close
Next
End Sub

Regards,
Shailaja