One more database question and then I'm through for the day... I would like to be able to query two fields, Name and Description from a table called Blocks. The idea is to be able to type something in a textbox and then dump the matching results into a listbox. I *think* I need to use the LIKE parameter (if that's the correct DB terminology) but I'm not sure if my syntax is correct, for starters. Once the syntax is correct, how do I search through the fields to get the results and feed them into a listbox?? Here's what I've got so far... Dim Cnxn As ADODB.Connection Dim strCnxn As String Dim rs As Recordset Dim strSQL As String Dim SQL As String strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\CADD Development\Database\ADO_Database.mdb" Set Cnxn = New ADODB.Connection Cnxn.Open strCnxn Set rs = CreateObject("ADODB.recordset") SQL = "SELECT Name, Description FROM Blocks WHERE Name LIKE '%" & TextBox1.Text & "%' or Description LIKE '%" & TextBox1.Text & "%'" rs.Open SQL, Cnxn ListBox1.AddItem 'How do I get the results from the query into a listbox??!? Cnxn.Close
Matt, Something like this should do the trick If rs.State = adStateOpen Then With rs Do While Not .EOF ListBox1.AddItem !Name 'advance to next record .MoveNext Loop End With End If Joe
Thanks Joe, and as promised, this is my last request for the day! -- I love deadlines I like the whooshing sound they make as they fly by. | Matt, | | Something like this should do the trick | | If rs.State = adStateOpen Then | With rs | Do While Not .EOF | ListBox1.AddItem !Name | 'advance to next record | .MoveNext | Loop | End With | End If | | Joe | -- | | | > One more database question and then I'm through for the day... | > I would like to be able to query two fields, Name and Description from a | > table called Blocks. | > The idea is to be able to type something in a textbox and then dump the | > matching results into a listbox. | > I *think* I need to use the LIKE parameter (if that's the correct DB | > terminology) but I'm not sure if my syntax is correct, for starters. | > Once the syntax is correct, how do I search through the fields to get the | > results and feed them into a listbox?? | > | > Here's what I've got so far... | > | > Dim Cnxn As ADODB.Connection | > Dim strCnxn As String | > Dim rs As Recordset | > Dim strSQL As String | > Dim SQL As String | > | > strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\CADD | > Development\Database\ADO_Database.mdb" | > Set Cnxn = New ADODB.Connection | > Cnxn.Open strCnxn | > | > Set rs = CreateObject("ADODB.recordset") | > | > SQL = "SELECT Name, Description FROM Blocks WHERE Name LIKE '%" & | > TextBox1.Text & "%' or Description LIKE '%" & TextBox1.Text & "%'" | > rs.Open SQL, Cnxn | > | > ListBox1.AddItem 'How do I get the results from the query into a | > listbox??!? | > | > Cnxn.Close | > | > -- | > I love deadlines | > I like the whooshing sound they make as they fly by. | > | > | > | |
Matt What if you try this for SQL: SQL = "SELECT Name, Description FROM Blocks WHERE Name ='" & TextBox1.Text & "' OR Description ='" & TextBox1.Text & "'" And then add to ListBox1 could be done by: Do Until RS.EOF ListBox1.AddItem RS.Fields("Name") & " - " & RS.Fields("Description") RS.MoveNext Loop Chris
One slight tweak: ListBox1.AddItem !Name & "" As written, it'll error if Name is null which could happen given the OR clause in the SQL statement. Guess you could also test the "" value prior to adding so none of them make the listbox. -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...
Short and sweet! I like it! However, I don't want the *exact* phrase which is why I added the % symbols. Thanks! -- I love deadlines I like the whooshing sound they make as they fly by. | Matt | | What if you try this for SQL: | | SQL = "SELECT Name, Description FROM Blocks WHERE Name ='" & TextBox1.Text | & "' OR Description ='" & TextBox1.Text & "'" | | | And then add to ListBox1 could be done by: | | Do Until RS.EOF | ListBox1.AddItem RS.Fields("Name") & " - " & RS.Fields("Description") | RS.MoveNext | Loop | | Chris | | | > One more database question and then I'm through for the day... | > I would like to be able to query two fields, Name and Description from a | > table called Blocks. | > The idea is to be able to type something in a textbox and then dump the | > matching results into a listbox. | > I *think* I need to use the LIKE parameter (if that's the correct DB | > terminology) but I'm not sure if my syntax is correct, for starters. | > Once the syntax is correct, how do I search through the fields to get the | > results and feed them into a listbox?? | > | > Here's what I've got so far... | > | > Dim Cnxn As ADODB.Connection | > Dim strCnxn As String | > Dim rs As Recordset | > Dim strSQL As String | > Dim SQL As String | > | > strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\CADD | > Development\Database\ADO_Database.mdb" | > Set Cnxn = New ADODB.Connection | > Cnxn.Open strCnxn | > | > Set rs = CreateObject("ADODB.recordset") | > | > SQL = "SELECT Name, Description FROM Blocks WHERE Name LIKE '%" & | > TextBox1.Text & "%' or Description LIKE '%" & TextBox1.Text & "%'" | > rs.Open SQL, Cnxn | > | > ListBox1.AddItem 'How do I get the results from the query into a | > listbox??!? | > | > Cnxn.Close | > | > -- | > I love deadlines | > I like the whooshing sound they make as they fly by. | > | > | > | |
True, but in my database there should NEVER be an empty field. If there is, heads will roll!! -- I love deadlines I like the whooshing sound they make as they fly by. | One slight tweak: | | ListBox1.AddItem !Name & "" | | As written, it'll error if Name is null which could happen given the OR | clause in the SQL statement. Guess you could also test the "" value prior | to adding so none of them make the listbox. | | -- Mike | ___________________________ | Mike Tuersley | CADalyst's CAD Clinic | Rand IMAGINiT Technologies | ___________________________ | the trick is to realize that there is no spoon...
Yeah, but in the impossible event that it ever happens, you want your program to recognize it and point an accusing finger at someone else rather than crash... I always wrap my programs in If Pigs.Flying = True Or Hell.Temp <= 32 Then MsgBox "Duck and cover" Else 'my program here EndIf
I love it James! Might have to borrow that one from you =) Matt - TRUST US! After 10 years of developing database apps, I've seen almost everything and there is no "never". Its always best to cover every base you can no matter what the program is - from checking numerical input where you think no idiot would ever enter an alpha to concantenating a db field with "". Especially if you are writing these apps for someone else like I do because it'll always, always, come back to the programmer "...sure the field shouldn't have been empty but still your program should have handled it..." BTDT -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...
BTDT??!? I said never because I'm the one in control of it, but I do plan on adding a LOT of error checking. -- I love deadlines I like the whooshing sound they make as they fly by. | I love it James! Might have to borrow that one from you =) | | Matt - TRUST US! After 10 years of developing database apps, I've seen | almost everything and there is no "never". Its always best to cover every | base you can no matter what the program is - from checking numerical input | where you think no idiot would ever enter an alpha to concantenating a db | field with "". Especially if you are writing these apps for someone else | like I do because it'll always, always, come back to the programmer | "...sure the field shouldn't have been empty but still your program should | have handled it..." BTDT | | -- Mike | ___________________________ | Mike Tuersley | CADalyst's CAD Clinic | Rand IMAGINiT Technologies | ___________________________ | the trick is to realize that there is no spoon...
Been There, Done That =) -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...
Care to expand on that? If the return value is Null how is it not going to pop an error unless of course you already have included some sort of error handling mechanism. -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...
null & "" = "" No. Reread the question and Joe's original answer. Joe's sample code used !Name to populate the listbox and a dbase can have NULL or "" as valid entries depending upon the type and setup of the dbase. If the Name field is "" then the code will work; if it is null, it'll pop an error. -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...
I'm sorry, you are correct !! Joe's code uses !Name which will error out if Name = Null (unless the listbox.additem accepts nulls !) As you point out !Name & "" will take care of that situation without the of error handling (I thought it was Joe's original code) On the other hand, Matt says his database should NEVER have an empty field, but hey, wierd things can happen to the database.
No problem Jorge -- the more eyes the better the coding effort =) -- Mike ___________________________ Mike Tuersley CADalyst's CAD Clinic Rand IMAGINiT Technologies ___________________________ the trick is to realize that there is no spoon...
On the other hand, Matt says his database should NEVER have Okay! Okay! I'll add some error-checking. Sheesh! -- I love deadlines I like the whooshing sound they make as they fly by. | I'm sorry, you are correct !! | Joe's code uses !Name which will error out if Name = Null | (unless the listbox.additem accepts nulls !) | As you point out !Name & "" will take care of that situation | without the of error handling (I thought it was Joe's original code) | | On the other hand, Matt says his database should NEVER have | an empty field, but hey, wierd things can happen to the database. | | -- | Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica | | | | >> null & "" = "" | > | > No. Reread the question and Joe's original answer. Joe's sample code used | > !Name to populate the listbox and a dbase can have NULL or "" as valid | > entries depending upon the type and setup of the dbase. If the Name field | > is "" then the code will work; if it is null, it'll pop an error. | > | > -- Mike | > ___________________________ | > Mike Tuersley | > CADalyst's CAD Clinic | > Rand IMAGINiT Technologies | > ___________________________ | > the trick is to realize that there is no spoon... | |
<snicker> I guess brow-beating works! -- R. Robert Bell Okay! Okay! I'll add some error-checking. Sheesh!
