SQL Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Help

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources