Building a Query Interface with VB6...

[FLL]

Hello, all...I have a question, maybe simple in its nature, about building a query interface within VB6 using ADO...actually one big question and a couple of requests for suggestions. Maybe some of you have programmed these kinds of apps before...let me explain:

I'm a relatively inexperienced programmer with VB6 and database access. I've been asked to build an application for my dad's company (thank goodness a quick development and flawless program is not expected in the next week or two) that will track customers as well as inventory. For the customer database, I have two tables: 1) records customer properties...name, address, email, unique customer ID (generated by the database), etc 2) records of purchases through the store. The two are related through customer ID. But this is just background info and is beside the point of what I need to know and can't find anywhere. I have searched high and low for examples on the code to build a query form for the company to use. I want the form to allow the user to search on any of the many fields in the database..probably through text boxes, drop down lists, etc. How can I generate the code for this? I do know SQL, but I don't know what kind of code to put in the program...I know that often some fields will be left blank, like if they just wanted to search by name and not customer ID. I have no idea where to start. Is there a wizard for this in VB? I know about the query builder, but, from what I can see, I cannot incorporate this into the user interface where the user can enter info into a variety of fields and get the results...I guess the bottom line of the problem is I don't know how to build the code from multiple fields when all of them aren't filled in. Any guidance or direction toward a good manual or example would be appreciated. Also, for those who are experienced, what kind of database should I use for speed? Access? Oracle, etc...? The database will be large. Also, any tips on what to do with the record set would be helpful...how to have it clear for a new search...with the option to save, etc....I realize this is a large question, and I am hoping that VB in itself offers a simple solution...maybe a query builder for the user interface? I don't know...can't find any help on the subject (all I keep coming up with is the query bulider option which seems more applicable to the developer than the user). Any help or example code would be GREATLY appreciated. Thank you. Have a wonderful day.

FLL

[Ianpbaker]

Hi FLL

What you are asking is not to hard. First of all I would have two forms for searching, one for your main customer table, and one for your purchase table. Then have several list boxes and text boxes to get the information the user wants to search by (List or Text depend on how much searchability you want to give to the user for each of the fields). Then when the user clicks on the search button, using ADO or DAO create a connection to the database and build a SQL statement depending on what search criteria the user has entered. Below is something like what you will need


Dim ObjCon As ADODB.Connection
Dim ObjRec As ADODB.Recordset
Dim strSql As String
Dim strCon As String

Set ObjCon = New ADODB.Connection
Set ObjRec = New ADODB.Recordset

Strcon = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=nwind.mdb;" & _
"DefaultDir=C:\programfiles\devstudio\b;" & _
"Uid=Admin;Pwd=;"


ObjCon.Open Strcon

StrSql = "SELECT * FROM Customer"

if Text1.value <> "" Then
StrSql = StrSql & " WHERE customerid = " & Text1.value
End If
'etc etc for your different fields, you will need to add a
'little function to see whether to put in a WHERE or an AND
'in the code

ObjRec.Open strSql, ObjCOn

'Then you can use the recordset to populate what ever you want the search to be displayed in

ObjRec.Close
ObjCon.Close

Set ObjCon = Nothing
Set ObjRec = Nothing



Hope this helps

Ian

[FLL]

Thanks, lan...just picked up your reply, but am here at work. I am going to go home and take a look at it...seems very simple...what kind of function can be generated to determine the AND and WHERE part...that sounded interesting. But, again, I do thank you for the taking the time to type out that code...I am sure it will be a big help.

FLL

[FLL]

OK...another question for you more experienced users...I'm writing this application, and I'm wondering exactly how I should write the SQL statement with a drop down list box, where the user has more than one option to select...I know it is going to be with the SQL OR command...does the SQL language in itself support the order of operations with the string...such as with parenthesis around the stuff in the OR? Can someone give me an example or, better yet, point me in the direction of an example complete with code of a fully working searchable database using VB6 and ADO? lan's code works like a charm...I'm just so, well, inexperienced when it comes to database development with VB6...or any language for that matter. My experience has been so 2 dimensional...thanks so much for the help!