| Author |
Topic |
|
Emotagoes
Starting Member
2 Posts |
 Posted - 2008-03-11 : 08:26:41
|
I need some serious SQL help guys.
I'm using Access 2001 and my Database is structured as follows:
Chance(Number, Detail, Action, Amount, Owner)
CommunityChest(Number, Detail, Action, Amount, Owner)
Players(Number, Name, Token, Money, Square, MissTurns, CurrentPlayer)
Properties(Number, PropertyName, Mortgaged, Set, Price, PlayerName, Rent, Rent1, Rent2, Rent3, Rent4, Rent5, HousesOwned)
PropertySets(Number, Colour, HousePrice, HouseColour, HotelColour)
Tokens(Name, Directory)
I'm making a Monopoly Game which i'll make sure i post up here when done, I'm using Visual Basics 2005 and OLEDB Connection, i know how to work all that but i have the following scenarios to find SQL for.
Upgrade Property - I need to use the Update Query for this.
A PropertyName is selected from a list box and someone then clicks to upgrade a property.
Properties.HousesOwned should have it's value raised by 1 Where the player owns all 3 sets. The Houseprice should then be took off the Players Money.
Downgrade Property - I need to use the Update Query for this.
A PropertyName is selected from a list box and someone then clicks to downgrade a property.
Properties.HousesOwned should have it's value lowered by 1 (until it gets to 0) Where the player owns all 3 sets. The Houseprice should then be added to the Players Money. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-11 : 10:26:18
|
You would build a SQL statement to apply the updates to the table
Dim SQL as String
Dim intValue
strParam = formname.comboboxname.value.tostring()
SQL = "Update [Table] SET [Column] = '" & strParam & "' "
And then execute the query against your database connection, in general.
objCN.Execute(SQL)
Poor planning on your part does not constitute an emergency on my part.
|
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-11 : 10:30:02
|
A good advice is also to add a WHERE clause in the UPDATE statement, otherwise all records will be updated in the table.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-11 : 10:34:16
|
quote:
Originally posted by Peso
A good advice is also to add a WHERE clause in the UPDATE statement, otherwise all records will be updated in the table.
E 12°55'05.25"
N 56°04'39.16"
Yeah...it was just a sample statement. Peso is correct, you should always qualify the update with a conditional clause to ensure only the applicable record(s) are updated.
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
Emotagoes
Starting Member
2 Posts |
Posted - 2008-03-11 : 15:08:56
|
My Problem is with the SQL
I need to Update Players.Money and Properties.HousesOwned. These are from two tables so does that mean executing 2 commands?
Players.Money should be updated to Players.Money - Properties.Price
And Properties.HousesOwned should be Updated to + 1.
This should all happen WHERE Properties.Houses Owned < 5 AND Property.Name = "A Selected Value From my Listbox"
It need a property count too though so it only happens when all 3 sets are owned :S |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-11 : 16:10:44
|
quote:
Originally posted by dataguru1971
You would build a SQL statement to apply the updates to the table
Dim SQL as String
Dim intValue
strParam = formname.comboboxname.value.tostring()
SQL = "Update [Table] SET [Column] = '" & strParam & "' "
And then execute the query against your database connection, in general.
objCN.Execute(SQL)
Never write an update statement like that -- always use parameters:
http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx
- Jeff
http://weblogs.sqlteam.com/JeffS
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-11 : 18:27:00
|
quote:
Originally posted by jsmith8858
quote:
Originally posted by dataguru1971
You would build a SQL statement to apply the updates to the table
Dim SQL as String
Dim intValue
strParam = formname.comboboxname.value.tostring()
SQL = "Update [Table] SET [Column] = '" & strParam & "' "
And then execute the query against your database connection, in general.
objCN.Execute(SQL)
Never write an update statement like that -- always use parameters:
http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx
- Jeff
http://weblogs.sqlteam.com/JeffS
Man...it isn't a real statement...just a general sample...but you are correct. I was kind of assuming that he wouldn't have "';DROP DATABASE master" in his combo box.
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-12 : 10:14:08
|
quote:
I was kind of assuming that he wouldn't have "';DROP DATABASE master" in his combo box.
You should have read the article I posted, sql injection is only a small part of why you should always use parameters.
Long story short: I don't think someone coming here for SQL advice will implicitly know that the code you posted for them as an example is something that they should never use; it is probably best to provide them with code and examples that they should be using, right? 
- Jeff
http://weblogs.sqlteam.com/JeffS
|
|
|
|
|
SQL Help