Find the answer to your Linux question:
Results 1 to 8 of 8
I've always got the answers i need here so i wonder if you can help with this. The people at visualbasicforum.com are either too ignorant (they have been before) to ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Linux Newbie
    Join Date
    Feb 2003
    Location
    Swansea (UK)
    Posts
    221

    VB: a different kind of advanced database search


    I've always got the answers i need here so i wonder if you can help with this. The people at visualbasicforum.com are either too ignorant (they have been before) to reply or dont know the answer. So:

    I have been expanding on my contact manager, at the moment i am changing the search to an advanced search and having some trouble.

    I have developed the search so that the user can tick the fields they want to be searched using the tick box and then enter the search criteria. All record with fields containing the criteria will be shown on a dbgrid. (Search form is shown in the image below)

    At the moment i have an SQL statement that obviously will not work because it doesn't have anything that considers what check boxes are ticked.

    Here is my SQL statement at the moment (it wont work).
    Code:
    SELECT * FROM Contacts WHERE (Surname = 'frmAdSearch.txtCriteria.Text') OR (`Forename(s)` = 'frmAdSearch.txtCriteria.Text') OR (`Email address` = 'frmAdSearch.txtCriteria.Text') OR (Street = 'frmAdSearch.txtCriteria.Text') OR (District = 'frmAdSearch.txtCriteria.Text') OR (City = 'frmAdSearch.txtCriteria.Text') OR (Country = 'frmAdSearch.txtCriteria.Text') OR (Postcode = 'frmAdSearch.txtCriteria.Text') OR (`Telephone number` = 'frmAdSearch.txtCriteria.Text') OR (`Fax number` = 'frmAdSearch.txtCriteria.Text') OR (`Mobile number` = 'frmAdSearch.txtCriteria.Text') OR (Comments = 'frmAdSearch.txtCriteria.Text') ORDER BY Surname
    What changes would i need to make to this to have a fully working search?

    P S : i will also attach the full program so you can see for yourself

    Sorry if the SQL statement is a bit messy above, its long so the formatting of it is untidy

    Image: Search form
    Program: Contact Manager

    Thanks

  2. #2
    don
    don is offline
    Linux Newbie
    Join Date
    Apr 2004
    Posts
    101
    first of all if ur using VB ur SQL string is all wrong... if u need to to work on any data access object on VB(ADO, DAO, ...) u need to get the string right. And the string is unusually complex as it isnt requred. here is an alternative which i believe would be error prone. Also a point to note is that you are using checkboxes instead of radio buttons so multiple check boxes can be ticked... that would really get you wierd results.

    Code:
    Dim SQLString as String
    
    if chkSurname.value = vbChecked then 
    SQLString = "SELECT * FROM Contacts WHERE (Surname = ' " + txtCriteria.Text + " ' )"
    end if
    
    if chkForeName.value = vbChecked then 
    SQLString = "SELECT * FROM Contacts WHERE ( ForeName = ' " + txtCriteria.Text + " ' )"
    end if
    .
    .
    .
    .

    now pass on the SQL string at the end of the sequence to your Data object and execute the query.

    that would keep your code simple and easily debuggable.

    if u decide that users cannot select more than 1 criteria to search on then you can put the if blocks in a function that returns an SQL string and exit sub within each if block.
    I\'m just a simple fisherman blessed with a lot of friends

  3. #3
    Linux Newbie
    Join Date
    Feb 2003
    Location
    Swansea (UK)
    Posts
    221
    Yes i may only allow one search criteria option to be selected, makes it alot simpler

    Edit: If you look at the project attached you'll see i am using a data environment for this search. How can i pass the SQL string as the SQL statement in the command named "AdvancedSearch" in the data environment using code?

    Nevermind:
    Code:
    'Pass the string
    DEnviron1.Commands("AdvancedSearch").CommandText = strSQL
    Also
    Is it possible to check if a record exists using SQL? For example check if there is at least one record with "Jones" as the surname. Or would i have to do what i've got for my other search (loop through the recordset and compare my search criteria to the fields, if it matches then RECORD FOUND)?

  4. #4
    don
    don is offline
    Linux Newbie
    Join Date
    Apr 2004
    Posts
    101
    You can do that.. fetch a recorset with a query containing ur criteria, and check if recorset.recordcount = 0

    Code:
    dim rs as ADODB.RecordSet
    
    set rs = myAdoObject.OpenRecordset("SELECT * FROM myTable WHERE (surname = ' " + txtCriteria.Text + " ' )")
    
    if rs.RecordCount = 0 then
     ' there is no record
    end if
    I\'m just a simple fisherman blessed with a lot of friends

  5. #5
    Linux Newbie
    Join Date
    Feb 2003
    Location
    Swansea (UK)
    Posts
    221
    Quote Originally Posted by don
    You can do that.. fetch a recorset with a query containing ur criteria, and check if recorset.recordcount = 0

    Code:
    dim rs as ADODB.RecordSet
    
    set rs = myAdoObject.OpenRecordset("SELECT * FROM myTable WHERE (surname = ' " + txtCriteria.Text + " ' )")
    
    if rs.RecordCount = 0 then
     ' there is no record
    end if
    I am using ADODC not ADODB. I think i'll loop through the recordset using DO...UNTIL and compare the search criteria to the fields, if there is a match then RECORD FOUND

  6. #6
    Linux User
    Join Date
    Jan 2003
    Location
    Cardiff, Wales
    Posts
    478

    dao

    i alway's used DAO although its older i found it simpler to work with.
    now using ado.net which is very different.

    sql = "SELECT * FROM [myTable] WHERE "

    you can loop through the check boxes building the criteria string. then run something like

    dim db as dao.database
    set db = dao.opendatabase( "Path to database.mdb" )
    dim rs as dao.recordset
    set rs = db.openrecordset(sql)

    then do what you need with it.
    if you still want to do this - send me the code and dbase and I'll knock something up for you.
    No trees were harmed during the creation of this message. Its made from a blend of elephant tusk and dolphin meat.

  7. #7
    Linux Newbie
    Join Date
    Feb 2003
    Location
    Swansea (UK)
    Posts
    221

    Re: dao

    Quote Originally Posted by kpzani
    i alway's used DAO although its older i found it simpler to work with.
    now using ado.net which is very different.

    sql = "SELECT * FROM [myTable] WHERE "

    you can loop through the check boxes building the criteria string. then run something like

    dim db as dao.database
    set db = dao.opendatabase( "Path to database.mdb" )
    dim rs as dao.recordset
    set rs = db.openrecordset(sql)

    then do what you need with it.
    if you still want to do this - send me the code and dbase and I'll knock something up for you.
    i dont really have time at the moment but i may sort it later this year, thanks for offering

  8. #8
    don
    don is offline
    Linux Newbie
    Join Date
    Apr 2004
    Posts
    101
    if u are using ADO data control then check out the myDataControl.Recordset.RecordCount property for number of records in the data control, where MyDataControl is ur ADO data control object. And IMHO avoid using DAO. its a deprecated technology and has no parallels in future devlopments of VB like the .NET
    I\'m just a simple fisherman blessed with a lot of friends

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •