W.E.B
asked on
VBA - SQL SELECT
Hello,
I'm using a cnn = New ADODB.Connection, to get some data from SQL SERVER 2008R2
When I run my simple select statment below, I'm getting thousands of records back. (in the sample attached, I should have received back the first 3 records back only).
if I run the same script inside SQL, I get the proper results. (one record per Tracking).
Any help is appreciated.
Dim cnt As Integer
Dim i As Integer
For i = 2 To Range("A2").End(xlDown).Ro
cnt = cnt + 1
' Start
cnn.Open "Provider=SQLOLEDB.1;User ID= xxxxxxxxxxxxxxxxxxxx
sql = "Select OrderNo, " & _
"NoteNumber from FinalizedOrderNotes " & _
"where NoteText Like ('%" & sh.Cells(i, 1) & "%') "
rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
sh.Cells(i, 2).CopyFromRecordset rs
rs.Close
cnn.Close
Next i
Thank you,
SAMPLE.xls
I'm using a cnn = New ADODB.Connection, to get some data from SQL SERVER 2008R2
When I run my simple select statment below, I'm getting thousands of records back. (in the sample attached, I should have received back the first 3 records back only).
if I run the same script inside SQL, I get the proper results. (one record per Tracking).
Any help is appreciated.
Dim cnt As Integer
Dim i As Integer
For i = 2 To Range("A2").End(xlDown).Ro
cnt = cnt + 1
' Start
cnn.Open "Provider=SQLOLEDB.1;User ID= xxxxxxxxxxxxxxxxxxxx
sql = "Select OrderNo, " & _
"NoteNumber from FinalizedOrderNotes " & _
"where NoteText Like ('%" & sh.Cells(i, 1) & "%') "
rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
sh.Cells(i, 2).CopyFromRecordset rs
rs.Close
cnn.Close
Next i
Thank you,
SAMPLE.xls
It looks like that if your target cell is empty your filter evaluates to '%%' with is no filter at all, explaining the too many records problem. Also, it looks like you're pulling from column A instead of column B .
ASKER
Hello,
This is the way it's supposed to be,
Column "A" is the noteText that I'm looking up in SQL
Results will be in column "B".
The main question I guess is why is it not stopping and ending the macro after the last row used in Column "A"
Thanks,
This is the way it's supposed to be,
Column "A" is the noteText that I'm looking up in SQL
Results will be in column "B".
The main question I guess is why is it not stopping and ending the macro after the last row used in Column "A"
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome,
Thank you very much.
It worked.,
Thank you very much.
It worked.,