[macataq]

hello
having problems with the following SQL statement


sql = "UPDATE totals(WK 16) VALUES(" & scorestr &")"_
& " where Username = " &Session("FootballPoolUsername")

Set rs = DbConn.Execute(sql)
displays;

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/results.asp, line 235

but when i do a
response.write it displays
UPDATE totals(WK 16) VALUES(13) where Username = MyName
wk 16 is the column i want the value to go into,
13 is the correct value
Myname is also correct

the problem is the error message and nothing is written to the DB.

could anyone point out my mistake?

Thank you

Michael

 

[Chicon]

Hi macataq,

The underscore character in the following statement is it correct ?

sql = "UPDATE totals(WK 16) VALUES(" & scorestr &")"_
& " where Username = " &Session("FootballPoolUsername")

 

[macataq]

Chicon
thanks for the reply
i believe that it is required when the query continues on more that one line.

Mac

 

[Chicon]

In SQL, the correct syntax of a basic UPDATE statement is in the following structure :

[SIZE=2]
[B]UPDATE[/B] [I]tableName[/I]
[B]SET[/B] [I]columnName[/I] = ...
[B]WHERE[/B] [I]condition expression[/I][/SIZE]

It is applicable for Oracle, DB2, MySQL and Access. The multi-lines representation is not mandatory, it is only aimed for the readability.

Link : http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

Therefore, your SQL statement should be :

sql = "UPDATE totals SET WK 16 = " & scorestr & " WHERE Username = " & Session("FootballPoolUsername")

Remark : the column name is not a single word, is it allowed in Access ?
-------- I know it is not allowed in Oracle, MySQL and DB2.

 

[macataq]

hi Chicon
i have changed the column names in my DB to WK_1.....WK_17
still get a syntax error.
thanks
Mac

 

[Chicon]

Sorry, I didn't know single quotes were mandatory for Microsoft Jet SQL.

The values must be enclosed with single quotes this way :

sql = "UPDATE totals SET WK_16 = '" & scorestr & "' WHERE Username = '" & Session("FootballPoolUsername") & "'"

 

[macataq]

hi Chicon
thanks for the reply
this statement appears to work(no error)
but nothing is being sent to the Access DB
i do have the correct permissions in place
and the data type is correct.
any ideas on what else could be causing this

Thanx

Mac

 

[Chicon]

It seems your database is not automatically refreshed after execution of the SQL update. It needs the Oracle equivalent of a commit instruction.
You have probably to implement a transaction in VB with a begin, a commit and eventually a rollback.

 

[macataq]

hello
i have looked up on ggogle everything i can follow
still cant this to work, i dont understand why the Response.write is showing the data i want to send to the db on the presnet page , but will not write it to the db?

Mac

 

[Chicon]

I have found out an example on this page from a French forum.
The example shows the way to connect and disconnect from a DB and the way to perform a SQL request (SELECT, INSERT, DELETE, UPDATE) in a VB script.

 

[macataq]

hi again
okay i have gotten the sql to write the value to the db.
i need to be able to write it to the correct week. i have
so far only been able to write it to the table by specifying
the column name in the Set statement, e.g SET WEEK_12 = & SCORESTR &.
i would like send it to the current week, but i dont know how to go about it .
any ideas?
thank you for any help

Macataq

 

[Chicon]

There's a formula to compute the week number on this page :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1002193&SiteID=1

 

[macataq]

i am not trying to determine the week of the year or the week of the month.
i am trying to determine the week of the season (17 week nfl season)
thats how my db is laid out e.g. week_1, week_2, week_3 etc..etc
hope that clarifies what i am trying to accomplish

thanx


Mac

 

[AGCurry]

i am not trying to determine the week of the year or the week of the month.
i am trying to determine the week of the season (17 week nfl season)
thats how my db is laid out e.g. week_1, week_2, week_3 etc..etc
hope that clarifies what i am trying to accomplish

An excellent example of why normalization is good and repeating groups are bad.

 

[macataq]

ACCurry;

thanks for your 2 cents..... worth every penny!

Mac