Reordset not working with WHERE clause in SQL (1 Viewer)

Dear Friends,
SQL query is working with Multi Colum list Rowsource, but not with the ADO Recordset. SELECT without where work fine.
I have used Select with 'WHERE' several times but this is the first instance of the problem.

Can you guys help me out?
.......................


Private Sub LstAddressController_Click()
On Error Resume Next

MsgBox LstAddressController.Column(0) & "-" & LstAddressController.Column(1)
Dim m As String
m = "Select PointType, ObjectName, ExpandedID from HrdSch where Address like '" & LstAddressController.Column(0) & "*' And Controller Like '" & LstAddressController.Column(1) & "*';"
MsgBox m

ControllersDetails.RowSource = m

ControllersDetails.Requery
ControllersDetails.SizeToFit

m = "Select distinct Panel from HrdSch where Address like '" & LstAddressController.Column(0) & "*' And Controller Like '" & LstAddressController.Column(1) & "*';"

PanelName1.RowSource = m
PanelName1.Requery



'.......Test Recordset Select with 'WHERE' query

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open m, CurrentProject.Connection
MsgBox rst1.RecordCount


rst1.Close
Set rst1 = Nothing

End Sub

but not with the ADO Recordset

Click to expand...

in what way is it not working? code errors? recordcount=0? something else?

in order to get a recordcount, you have to load the entire recordset first - so need to use a movelast before using recordcount

CJ_London said:

in what way is it not working? code errors? recordcount=0? something else?

in order to get a recordcount, you have to load the entire recordset first - so need to use a movelast before using recordcount

Click to expand...

Record count zero.

Tried again with move last.

Private Sub LstAddressController_Click()
On Error Resume Next

'.......This section working..

Dim m As String
m = "Select PointType, ObjectName, ExpandedID from HrdSch where Address like '" & LstAddressController.Column(0) & "*' And Controller Like '" & LstAddressController.Column(1) & "*';"
MsgBox m

ControllersDetails.RowSource = m

ControllersDetails.Requery
ControllersDetails.SizeToFit

m = "Select distinct Panel from HrdSch where Address like '" & LstAddressController.Column(0) & "*' And Controller Like '" & LstAddressController.Column(1) & "*';"

PanelName1.RowSource = m
PanelName1.Requery



'.......This section not working..

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open m, CurrentProject.Connection

'Added few lines to track the flow

Dim paneltxt As String
paneltxt = rst1(0)
MsgBox "Panel:-" & paneltxt

'Added Move last as suggested.

rst1.MoveLast
MsgBox "Record count:-" & rst1.RecordCount



rst1.Close
Set rst1 = Nothing

End Sub


PS: My objective is to load the selected rows to another table, where they will feed data to another file, so I need selected data in table, display in list is not enough.

Thanks in advance

take out your on error resume next and see what errors are created

I'm now out for the rest of the day, so someone else can perhaps help

1. did you actually select anything from the list?
2. or you just need to include all the items in the list?

for 1, you need to actually select an item on the list:

"Select distinct Panel from HrdSch where Address like '" & LstAddressController.Column(0, LstAddressController.ListIndex) & "*' And Controller Like '" & LstAddressController.Column(1, LstAddressController.ListIndex) & "*';"


For 2:

Dim i As Integer
Dim strInclude1 As String
Dim strInclude2 As String
For i = 0 To LstAddressController.ListCount-1
strInclude1 = strInclude1 & Chr(34) & LstAddressController.Column(0, i) & ","
strInclude2 = strInclude2 & LstAddressController.Column(1, i) & ","
Next
If strInclude1<>"" Then
strInclude1 = Left(strInclude1, Len(strInclude1)-1)
strInclude2 = Left(strInclude2, Len(strInclude1)-1)

"Select distinct Panel from HrdSch where Address IN (" & strInclude1 & ") And Controller IN (" & strInclude2 & ")"

CJ_London said:

take out your on error resume next and see what errors are created

I'm now out for the rest of the day, so someone else can perhaps help

Click to expand...

Error I get is for BOF/EOF.

arnelgp said:

1. did you actually select anything from the list?
2. or you just need to include all the items in the list?

for 1, you need to actually select an item on the list:

"Select distinct Panel from HrdSch where Address like '" & LstAddressController.Column(0, LstAddressController.ListIndex) & "*' And Controller Like '" & LstAddressController.Column(1, LstAddressController.ListIndex) & "*';"


For 2:

Dim i As Integer
Dim strInclude1 As String
Dim strInclude2 As String
For i = 0 To LstAddressController.ListCount-1
strInclude1 = strInclude1 & Chr(34) & LstAddressController.Column(0, i) & ","
strInclude2 = strInclude2 & LstAddressController.Column(1, i) & ","
Next
If strInclude1<>"" Then
strInclude1 = Left(strInclude1, Len(strInclude1)-1)
strInclude2 = Left(strInclude2, Len(strInclude1)-1)

"Select distinct Panel from HrdSch where Address IN (" & strInclude1 & ") And Controller IN (" & strInclude2 & ")"

Click to expand...

Yes I have selected on the list, based on selection & click this event sub is triggered.
Successful selection executes the SQL query in above section and 3 column content are loaded into the multi-column list.
another query brings the name of the panel and it also executes correctly and another placeholder gets updated successfully.
Other than this I need to get the rows and fill into another table.

Any reason for specifically using ADODB - not that it should be a problem?

If the second sub is separate it won't be able to reference the same recordset sql string 'm' as it won't exist outside of the original sub.

Put Option Explicit at the top of the module and compile the module.

add CursorType to your recordset.

rst1.CursorLocation = adUseClient
RS1.CursorType=adOpenStatic

arnelgp said:

add CursorType to your recordset.

rst1.CursorLocation = adUseClient
RS1.CursorType=adOpenStatic

Click to expand...

Tried but no success.

I have tried some more stuff.
This is something to do with the datatype of the tables.
1: First test without WHERE, it worked fine.
2: Second test with only one WHERE clause Address: No data retrieved.
3: Third test changed the data type of address from SHORT TEXT to Number, removed ' from WHERE and bingo it worked!
4: Now I have the controller which can't be Number so tried with LONG Text but no data return
5: I have '* & *' wild character but still it is not matching but still no result.

Can't figure out the reason....
:banghead:

your short text datatype would be fine.

add a test code on the List AfterUpdate:

private sub LstAddressController_AfterUpdate()
msgbox LstAddressController.Column(1)
end sub

'*****
now what do you see number or text?

arnelgp said:

your short text datatype would be fine.

add a test code on the List AfterUpdate:

private sub LstAddressController_AfterUpdate()
msgbox LstAddressController.Column(1)
end sub

'*****
now, what do you see number or text?

Click to expand...

This addition gives me the name of the controller which is TEXT.
with datatype as NUMBER for address and only address in WHERE clause gives me 50 records (this is correct).
When I add the controller to where query it gives me zero records (it should be 8-20) based on selection



Private Sub LstAddressController_Click()
'On Error Resume Next

'.......This section working..

Dim m As String
m = "Select PointType, ObjectName, ExpandedID from HrdSch where Address like '" & LstAddressController.Column(0) & "*' And Controller Like '" & LstAddressController.Column(1) & "*';"
MsgBox m

ControllersDetails.RowSource = m

ControllersDetails.Requery

m = "Select PointType, ObjectName, ExpandedID from HrdSch where Address like " & LstAddressController.Column(0) & ";"

PanelName1.RowSource = m
PanelName1.Requery



'.......This section not working..

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open m, CurrentProject.Connection

'Added few lines to track the flow

'rst1.MoveLast
MsgBox "Record count: " & rst1.RecordCount



rst1.Close
Set rst1 = Nothing

End Sub

You've removed the wild card and string delimiter single quotes in the second Sql statement.

Minty said:

You've removed the wild card and string delimiter single quotes in the second Sql statement.

Click to expand...

Yes your observation is correct, I have removed it as Address column changed to NUMBER type and this part is working fine.


Now I have only problem with second part of the WHERE that is to add...

And Controller Like '*" & LstAddressController.Column(1) & "*';"


As Controller is short text tested with ' and *
but no luck

Dear Friends,
Thanks for your help, finally I was able to run query with below code.


Private Sub LstAddressController_Click()
'On Error Resume Next

'.......This section working..

Dim m As String


m = "Select Distinct Panel from HrdSch where Address like '" & LstAddressController.Column(0) & "*' And Controller Like '" & LstAddressController.Column(1) & "*';"

PanelName1.RowSource = m
PanelName1.Requery

m = "Select PointType, ObjectName, ExpandedID from HrdSch where Address like '" & LstAddressController.Column(0) & "*' And Controller Like '" & LstAddressController.Column(1) & "*';"
MsgBox m

ControllersDetails.RowSource = m

ControllersDetails.Requery



'.......This section not working..
Dim txtCtrlSQL As String

txtCtrlSQL = LstAddressController.Column(1)

m = "Select PointType, ObjectName, ExpandedID from HrdSch where Address = " & LstAddressController.Column(0) & " And Controller = '" & txtCtrlSQL & "';"
MsgBox m

Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open m, CurrentProject.Connection

'Added few lines to track the flow

'rst1.MoveLast
MsgBox "Record count: " & rst1.RecordCount



rst1.Close
Set rst1 = Nothing

End Sub


It is working with = instead of LIKE and without wild characters.
also I have put the list selection text to string and then used string in query.

* works in DAO and Access queries, not in ADO. Try % instead.

In addition as the values are being pulled from fixed lists of the correct criteria data, the use of like is actually unnecessary and would slow the execution down.