Using an array in a sql statement

[warpped]

Is it possible to use a vb array in the where clause of an sql statement? I have a datagrid that is populated with data from a table. I want to have the ability to select one to several records from the grid storing the values of one of the columns into an array. Then use that array in the where clause of antoher sql statement to populate a different recordset. This will allow me to load the selected data into a form so the user can edit the records one at a time or do bulk updates.

Does anyone know the correct syntax?

Thanks

 

[Foada]

I am not sure if this is what you are looking for but here goes.

Private arr(4) As String
Private rsData As New ADODB.Recordset
Private hDB As New ADODB.Connection

Private Sub Form_Initialize()
arr(0) = "Field1"
arr(1) = "Field2"
arr(2) = "Field3"
arr(3) = "Field4"
arr(4) = "Field5"
End Sub

Private Function BuildSQL(ByVal Mode As Long) As String
Dim iX As Long
Dim sSQL As String

sSQL = "Select "
For iX = 0 To Mode
sSQL = sSQL & arr(iX) & ", "
Next

sSQL = sSQL & "From myTable"
BuildSQL = sSQL
End Function

Private Function ConnectDB() As Boolean
Dim sTmp As String

sTmp = "C:/myDB.mdb"

On Error GoTo ErrHnd

hDB.ConnectionString = sTmp
hDB.Mode = adModeReadWrite
hDB.Open
If rsData.State = adStateOpen Then
rsData.Close
End If

sTmp = BuildSQL(4)
With rsData
.ActiveConnection = hDB
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open sTmp
End With

rsData.Close
ConnectDB = True
Exit Function

ErrHnd:
MsgBox Err.Number & " " & Err.Description & " Error Generated By " & Err.Source, vbCritical, "System Error Trap !"
ConnectDB = False
End Function


Hope this helps
Anything is possible, the problem is I only have one lifetime.

 

[kmanning]

vinny,
What database are you using?

You can build a string using the record ids (or another unique field) from the table. Then you can use the IN keyword.

 

[warpped]

I am using VB6 and MS SQL Server 7. Here is what I am trying to do...

My table looks similar to this - ID, Product, Desc, ...+ 20 other fields.

I populate the grid with the following sql - "select ID,Product,Desc from process_table order by ID".

I want the user to have the ability to select one or more records from the grid, capturing the ID field into an array. I have it working up to this point. Then, based on the records selected, I want to create another recordset similar to the following...

"select * from process_table where ID in ('1a','1b','2c')"

but instead of the above, replace "'1a','1b','2c'" with the information captured in the array.

Then the new recordset will be used to populate data entry forms.