Visual Basic Recordsets and ListView Searching

[ShadowPierce]

->I'm a Computer Science major, currently the project leader for our Computer Programming 3 thesis... Despite being the project leader, I do all the work... So             I came here to ask for some Visual Basic help... No advanced stuff please, we're still on the basics so my professor would probably get suspicious if I use some weird code....

Anyway, could anyone take a look at this code? I can't, for the love of Blizzard, seem to make it run properly... It always catches a different error every time I try to change it, specifically the Sub on the Textbox:

Spoiler: ShowHide

Sub FillListView()
    ListView1.ListItems.Clear
    dbConnect
        rs.Open "Select * from tblProducts", db, 1, 2
        Do Until rs.EOF = True
            Dim lst As ListItem
            Set lst = ListView1.ListItems.Add(, , rs!pID)
            lst.SubItems(1) = rs!pName
            lst.SubItems(2) = rs!pCategory
            lst.SubItems(3) = rs!pSubCategory
            lst.SubItems(4) = rs!pColor
            lst.SubItems(5) = rs!pSize
            lst.SubItems(6) = rs!pSizeType
            lst.SubItems(7) = rs!pPrice
            lst.SubItems(8) = rs!pSupplier
            lst.SubItems(9) = rs!pInStock
            lst.SubItems(10) = rs!pReorderQty
            lst.SubItems(11) = rs!pMiscDetails
            rs.MoveNext
        Loop
        rs.Close
    dbDisconnect
End Sub

Private Sub Form_Load()
cboSearchBy.AddItem "Product Name"
cboSearchBy.AddItem "Category"
cboSearchBy.AddItem "Color"
cboSearchBy.AddItem "Size Type"
cboSearchBy.AddItem "Supplier"
cboSearchBy.Text = "Product Name"
FillListView
End Sub

Private Sub txtKeyword_Change()
ListView1.ListItems.Clear
    dbConnect
        rs.Open "Select * from tblProducts where " & cboSearchBy.Text & " like '" & txtKeyword.Text & "%'", db, 1, 2
        Do Until rs.EOF = True
            Dim lst As ListItem
            Set lst = ListView1.ListItems.Add(, , rs!pID)
            lst.SubItems(1) = rs!pName
            lst.SubItems(2) = rs!pCategory
            lst.SubItems(3) = rs!pSubCategory
            lst.SubItems(4) = rs!pColor
            lst.SubItems(5) = rs!pSize
            lst.SubItems(6) = rs!pSizeType
            lst.SubItems(7) = rs!pPrice
            lst.SubItems(8) = rs!pSupplier
            lst.SubItems(9) = rs!pInStock
            lst.SubItems(10) = rs!pReorderQty
            lst.SubItems(11) = rs!pMiscDetails
            rs.MoveNext
        Loop
        rs.Close
    dbDisconnect
End Sub

Answers ASAP please... Thanks!


†

[ForeverZer0]

I would move this to Sea of Code, but for some strange reason Academics is like the one board I have no control of. I think Blizz is calling me stupid in some passive-aggressive type of way

Unfortunately, Visual Basic is not my forte, so I am sorry that I cannot help a lot. I am unsure of the rules of VB, but I do know that most languages do not allow for modification of an Array/List during iteration. It appears that is what you are trying to do. What errors is it giving it you?

[Ryex]

Well, I think your out of luck here man. I'm one of the few people here who has any experience with VB. in fact. I might be the only one. the only experience I have with VB is from back when I was helping JC with Pokemon Neon and he was writing a new engine in VB.

any way your using VB and MySQL if the SQL like query stings are any clue, and from my limited experience I see nothing wrong. I would help me to know what kind of errors you are getting or what isn't working, I could point you to potential problem sources then.

[ShadowPierce]

->I think I'm getting there, instead of taking the field name from the database itself, I assign it on a string first before using it in the recordset...

I'm getting various kinds of errors every time I change the code on the textbox, but this is the most common:

Runtime error '-2147217 (80040e14)'

Syntax error (missing operator) in query expression.

Any ideas as to why this happens? 


†

[Ryex]

this error is telling you that your SQL query is malformed. somewhere in your iteration the query is generated with a missing operator.
to debug it have it generate the query as a string and print it before using it to query the database. the last string to get printed before the error gets raised is the one that is malformed.

[ShadowPierce]

->I tried redefining my queries but still no luck...


†
EDIT:
->Wait! Got it! It seems that my queries weren't matching with the string read by VB, so I just had to change those with my combobox so that they can work... Thanks Ryex! *hugs*

Resolved in less than an hour...


†

[Ryex]

glad to be of help.

[G_G]

Well, I think your out of luck here man. I'm one of the few people here who has any experience with VB. in fact. I might be the only one. the only experience I have with VB is from back when I was helping JC with Pokemon Neon and he was writing a new engine in VB.

any way your using VB and MySQL if the SQL like query stings are any clue, and from my limited experience I see nothing wrong. I would help me to know what kind of errors you are getting or what isn't working, I could point you to potential problem sources then.

You wish! >:U The very first version of Cybele was coded in VB. In fact, I learned Visual Basic before C#. *goes back to doing chores because no one around this house has to do them ever*

[ForeverZer0]

Well, I think your out of luck here man. I'm one of the few people here who has any experience with VB. in fact. I might be the only one. the only experience I have with VB is from back when I was helping JC with Pokemon Neon and he was writing a new engine in VB.

He never said he was the only one. Just saying.

[G_G]

Well, I think your out of luck here man. I'm one of the few people here who has any experience with VB. in fact. I might be the only one. the only experience I have with VB is from back when I was helping JC with Pokemon Neon and he was writing a new engine in VB.

[Ryex]

Well, I think your out of luck here man. I'm one of the few people here who has any experience with VB. in fact. I might be the only one. the only experience I have with VB is from back when I was helping JC with Pokemon Neon and he was writing a new engine in VB.

well that makes TWO people who know VB.

also when I was first looking at the code and asked for the error, I had a feeling that the error would be with the SQL and not the actual VB.

[ForeverZer0]

lol

Zer0 = fail

[Blizzard]

lol

Zer0 = fail

But doesn't this mean you're not failing at all, because it's zero fail?

Spoiler: ShowHide

[ForeverZer0]

That means I am incapable of failure.

I like your reasoning.

[winkio]

You are incapable of being incapable.  Reasoning is broken.

[ForeverZer0]

Being incapable does not necessarily mean failure, so win.

[ShadowPierce]

->Anyone willing to take a look at the system I did so far? It's still unfinished & missing a lot of things (will be passing it in 3 days) so please post bugs & errors here...

http://www.mediafire.com/?ts2wp12s91oq99k

NOTE: Requires VB and MS Access to open all files

I'd really appreciate it if you guys would help!


†

[Ryex]

what are you trying to do again?

[ShadowPierce]

->Nothing, really... I just can't find a bug so I thought I'd ask someone to "beta-test" it in case I missed something...


†

[Ryex]

ah, makes sense. others can often find bugs you thought were impossible until they manifested themselves.