recordset returns incorrect data when using 3 textboxes

Hi!

I have a userform which has multipages. The first page is the one I am having problems with.
I perform a SEARCH in this page against a database in sql server 2000 and want to get specific results back.

Basically I have 3 textboxes:

textbox1  to enter domain

textbox13 to enter law firm

textbox14 to enter Nickname


I had this working when only searching for a domain with the code below.

Here is the code:


'==================================================================================================
'This routine performs a search for a specific domain and displays the results in a listbox
'in tabular form
'==================================================================================================

Private Sub CommandButton1_Click()
     
    Dim ADOCNN As ADODB.Connection
    Dim ADOCMD As ADODB.Command
    Dim ADOrs As ADODB.Recordset
    Dim stConn As String
    Dim stSQL As String
    Dim tocompare As String
    Dim tocompare2 As String
    Dim tocompare3 As String
    Dim fldcount As Variant
    Dim i As Long

   
    'Clear active worksheet columns
    Sheet1.Range("A:M").Select
    Selection.Clear
   
       
        tocompare = CStr(Trim(TextBox1.Value))
   
        tocompare2 = CStr(Trim(TextBox13.Value))
   
        tocompare3 = CStr(Trim(TextBox14.Value))
   
    Set ADOCNN = New ADODB.Connection
     
     
    With ADOCNN
        .CommandTimeout = 0
        .Provider = "SQLOLEDB.1"
        .ConnectionString = "Initial Catalog=PrivBank;Data Source=dr-ny-sql001;INTEGRATED SECURITY=sspi;"
        .Open
    End With
     
    Set ADOCMD = New ADODB.Command
     
    With ADOCMD
        .CommandTimeout = 0
        .CommandType = adCmdText
        .CommandText = "select * from Domain_List_TEST where [domain] like '" & tocompare & "%' or [law_firm] like '" & tocompare2 & "%' or [NickName] like '" & tocompare3 & "%';"
        .ActiveConnection = ADOCNN
    End With
   
    Set ADOrs = ADOCMD.Execute
       
        For i = 0 To ADOrs.Fields.Count - 1
       
               Sheet1.Cells(1, i + 1).Value = ADOrs.Fields(i).Name
       
        Next i
       
        Sheet1.Range("A:M").Select
        Selection.Font.ColorIndex = 2
       
       
        Sheet1.Cells(2, 1).CopyFromRecordset ADOrs
       
         
        Dim rngRowSource As Range
       
                Set rngRowSource = Range("A1").CurrentRegion.Offset(1, 0)
           
    With frmData.ListBox1
   
                .ColumnCount = fldcount - 1
                .ColumnHeads = True
                .RowSource = rngRowSource.Address
       
    End With
       
    Set ADOCNN = Nothing
    Set ADOCMD = Nothing
     
    ADOrs.Close
    Set ADOrs = Nothing
   
    Sheet1.Range("N1").Select
             
     
End Sub


I modified only this part accordingly:



With ADOCMD
        .CommandTimeout = 0
        .CommandType = adCmdText
        .CommandText = "select * from Domain_List_TEST where [domain] like '" & tocompare & "%' or [law_firm] like '" & tocompare2 & "%' or [NickName] like '" & tocompare3 & "%';"
        .ActiveConnection = ADOCNN
    End With



and of course declared the variables at the beginning.


Now what I am getting when I search in either of the 3 textboxes is ALL the records from the database (SQL SERVER 2000). This is not what I want, I only want to get the records that meet the condition in textbox 1 or textbox13 or textbox14 or if i enter in all 3 textbox that meets all. I hope this is clear enough.

Any ideas where I am going wrong with this?



Thanks a lot!

JT
jtammygAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
try this:



   Dim sql as String
   dim sep as string
   sep = " where " 
   sql = "select * from Domain_List_TEST "
   if tocompare <> empty then 
     sql = sql & sep & " [domain] like '" & tocompare & "%' "
     sep = " OR "
   end if
   if tocompare <> empty then 
     sql = sql & sep & "[law_firm] like '" & tocompare2 & "%' "
     sep = " OR "
   end if
   if tocompare <> empty then 
     sql = sql & sep & " [NickName] like '" & tocompare3 & "%' "
     sep = " OR "
   end if 
  
    With ADOCMD
        .CommandTimeout = 0
        .CommandType = adCmdText
        .CommandText = sql
        .ActiveConnection = ADOCNN
    End With

Open in new window

0
jtammygAuthor Commented:
angelll

thank you for your reply.

although that only works when i insert a search for the 3 boxes. i want to be able to have either one or the other or the other or search in the 3 of them.

I hope this is clear enough.

Thanks!

Tammy
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry... copy/paste error
Dim sql as String
   dim sep as string
   sep = " where " 
   sql = "select * from Domain_List_TEST "
   if tocompare <> empty then 
     sql = sql & sep & " [domain] like '" & tocompare & "%' "
     sep = " OR "
   end if
   if tocompare2 <> empty then 
     sql = sql & sep & "[law_firm] like '" & tocompare2 & "%' "
     sep = " OR "
   end if
   if tocompare3 <> empty then 
     sql = sql & sep & " [NickName] like '" & tocompare3 & "%' "
     sep = " OR "
   end if 
  
    With ADOCMD
        .CommandTimeout = 0
        .CommandType = adCmdText
        .CommandText = sql
        .ActiveConnection = ADOCNN
    End With

Open in new window

0
jtammygAuthor Commented:
angelll,

where would u incorporate this in my code?

thanks!

Tammy
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it would replace this from your previous code:
    With ADOCMD
        .CommandTimeout = 0
        .CommandType = adCmdText
        .CommandText = "select * from Domain_List_TEST where [domain] like '" & tocompare & "%' or [law_firm] like '" & tocompare2 & "%' or [NickName] like '" & tocompare3 & "%';"
        .ActiveConnection = ADOCNN
    End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.