[VB6] Update using 2 or more WHERE clauses

Results 1 to 4 of 4
  1. #1
    The G.O.A.T What Can I Say is offline
    True MemberRank
    May 2007 Join Date
    Luton, EnglandLocation
    1,043Posts

    [VB6] Update using 2 or more WHERE clauses


    RaGEZONE Recommends

    RaGEZONE Recommends

    Hello, I'm creating a program on VB6 (Don't slander me, I enjoy using it and it's for personal use) and I'm trying to update a record using 2 WHERE clauses.

    My records are set out like

    Code:
    |---------|---------|---------|---------|
    |----ID---|---Name--|---Cost--|Quantity-|
    |---------|---------|---------|---------|
    |----1----|----a----|----1----|---10----|
    |----2----|----a----|----1----|----1----|
    |----3----|----b----|----1----|----3----|
    |----4----|----c----|----2----|----5----|
    |---------|---------|---------|---------|
    As you can see I have 2 things with the name "a"

    I have 2 ListBoxes. 1 visible (Showing 'name') and one hidden (Showing 'ID'). When I click the visible listbox, the ListIndex transfers over the the second listbox so when I click the name in the first list box, the correct ID is selected in the hidden listbox.

    My query I'm running to update is

    Code:
    rs.Open "UPDATE items SET cost='" + txtCost.Text + "', quantity='" + txtQuant.Text + "' WHERE name='" + ListBox1.Text + "' AND id='" + ListBox2.Text + "'"
    However is is ignoring the second WHERE clause and replacing both items named a.

    Is there any way of getting around this?


  2. #2
    [VB6] Update using 2 or more WHERE clauses Future is offline
    SupervisorRank
    Dec 2011 Join Date
    1,827Posts

    Re: [VB6] Update using 2 or more WHERE clauses

    Looking at it from here, I don't see why the second WHERE clause would be ignored in your case. My guess would be some issue with how you linked these ListBoxes and what is selected by the time you execute the UPDATE query. Or you have other data in your real table than what you showed in this example (e.g. duplicated Ids). I see at least one big misconception though.

    You want to filter either by Name OR by Id value here. Not both, that doesn't make sense. While the Id should be an unique identifier, the Name is obviously not. So you either want to update a single entity (selected by Id) or a number of entities (selected by equal Name for example). Depending on what your business logic requires, that UPDATE statement should be stripped off either the Name or the Id WHERE clause.

    Very likely you just want the Id here. From how your current UPDATE statement works, it'll be unique on the Id anyways, because even if you have two similarly named entities, the check on the Id will make it unique to one entity or zero (if the Id is an unique index / primary key).

    Further Tipps:
    • Use databinding / datasources instead of what you did with a hidden ListBox
    • Use a database framework instead of query strings like these, or at least make sure they are not that easily exploitable.
    Last edited by Future; 11-09-17 at 01:51 AM. Reason: Added tipps




    Quote Originally Posted by A Wise Man
    P-Servers are NOT dead. Bugs need squishing. Quests need fixing. Unfortunately, majority of people don't know the difference between a computer and a toaster so...

  3. #3
    The G.O.A.T What Can I Say is offline
    True MemberRank
    May 2007 Join Date
    Luton, EnglandLocation
    1,043Posts

    Re: [VB6] Update using 2 or more WHERE clauses

    Quote Originally Posted by Future View Post
    Looking at it from here, I don't see why the second WHERE clause would be ignored in your case. My guess would be some issue with how you linked these ListBoxes and what is selected by the time you execute the UPDATE query. Or you have other data in your real table than what you showed in this example (e.g. duplicated Ids). I see at least one big misconception though.

    You want to filter either by Name OR by Id value here. Not both, that doesn't make sense. While the Id should be an unique identifier, the Name is obviously not. So you either want to update a single entity (selected by Id) or a number of entities (selected by equal Name for example). Depending on what your business logic requires, that UPDATE statement should be stripped off either the Name or the Id WHERE clause.

    Very likely you just want the Id here. From how your current UPDATE statement works, it'll be unique on the Id anyways, because even if you have two similarly named entities, the check on the Id will make it unique to one entity or zero (if the Id is an unique index / primary key).

    Further Tipps:
    • Use databinding / datasources instead of what you did with a hidden ListBox
    • Use a database framework instead of query strings like these, or at least make sure they are not that easily exploitable.
    Yeah now I see it typed, I'm not sure why I'm using name and id to update when just id would work. Common sense obviously isn't a strong point of mine!
    Thanks for the swift and informative reply

  4. #4
    right + down + X GhostSnyper is online now
    SupervisorRank
    May 2006 Join Date
    AZ, USALocation
    3,482Posts

    Re: [VB6] Update using 2 or more WHERE clauses

    don't bother with the a

    If you know the index, use the index in the WHERE. Don't bother with anything else

    Code:
    SqlString = "UPDATE items SET cost='" + txtCost.Text + "', quantity='" + txtQuant.Text + "' WHERE  id='" + ListBox2.Text + "'"


    Also, if you don't need to, don't open a recordset. If you want to know if it was successful, dim a "counter" as an int, then you can just

    Code:
    conn.Execute SqlString, counter
    
    If counter = 0 Then MsgBox("There was an issue updating the record!")
    I don't even know what I'm doing anymore




Advertisement