Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

[VB6] Update using 2 or more WHERE clauses

The G.O.A.T
Loyal Member
Joined
May 2, 2007
Messages
574
Reaction score
3
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?
 
[emoji848]
Legend
Joined
Dec 3, 2011
Messages
2,232
Reaction score
1,518
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:
The G.O.A.T
Loyal Member
Joined
May 2, 2007
Messages
574
Reaction score
3
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
 
right + down + X
Loyal Member
Joined
May 25, 2006
Messages
1,688
Reaction score
298
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 = "[COLOR=#666666]UPDATE items SET cost='" + txtCost.Text + "', quantity='" + txtQuant.Text + "' WHERE  id='" + ListBox2.Text + "'"[/COLOR]


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!")
 
Back
Top