Update my DB!

[Morgoth]

Small Problem, My Syntax is incorrect.

ssSQL = "UPDATE Members (Loggedin) VALUES (True) WHERE (UserName='"& oRS("username") &"')"
Set ooRS = oConn.Execute(ssSQL)


Please tell me why my syntax is wrong, is it because of my brakets? I hope not cause that would confuse me.

And oRS("username") is valid. oRS("username") = MyName


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

Line 46: Set ooRS = oConn.Execute(ssSQL)

[whammy]

What field type is "Loggedin" in your database?

[Morgoth]

Data Type Yes/No
Format True/False
Default Value False

[whammy]

Hmm, I haven't messed with "Yes/No" fields in Access, but try using a 1 for true and a 0 for false and lemme know what happens. (No single quotes around the variable in this case.)

I'm assuming it's basically the same (if not exactly the same) as a bit field, but not sure since I usually use a bit field for this kind of "Yes/No" condition.

[Morgoth]

Nope, same error.

[whammy]

I just realized... you're using the update statement completely wrong... it should be:

UPDATE tablename SET blah = blah, blah2 = blah2 WHERE something = something else

that's your problem

you're using the INSERT INTO syntax for an update statement! Sorry I didn't catch that sooner... but I'm a bit distracted at the moment hehe

[Morgoth]

Originally posted by whammy
In these cases, I always (always) do this:

Response.Write(strSQL) : Response.End

That way you can see what's actually being passed to your SQL statement, and not execute it. That's the first step in debugging. Since you are hosting, I assume you also have access to SQL Query Analyzer, which is your next best friend? :)

Now, what is "SQL Query Analyzer", cause I might have access to it.

The result is: UPDATE Members (Loggedin) VALUE (True) WHERE (UserName='Morgoth')

Hum? Any clues?

[whammy]

Re-read my last post, I just updated it lol... then you'll see what the problem is

[whammy]

P.S. Again, sorry I didn't catch that sooner since I am a bit distracted... I hope that solves your dilemma. :)

[Morgoth]

I think it may.

I did add Set after I saw it on:
http://www.programmersresource.com/articles/dbupdate.asp

But I didn't change around loggedin = True I left it as:
ssSQL = "UPDATE Members (Loggedin) SET (True) WHERE (UserName='"& oRS("username") &"')"


It should work now.

Thank you whammy.

[whammy]

If you still need help with the syntax lemme know, I'm sure everyone has done something like that at one time or another. I know I have. :D

P.S. make sure you use the primary key in your update statement (where) so you don't accidentally update a bunch of records!

[Morgoth]

Originally posted by whammy
If you still need help with the syntax lemme know, I'm sure everyone has done something like that at one time or another. I know I have. :D

P.S. make sure you use the primary key in your update statement (where) so you don't accidentally update a bunch of records!

Well, the ID is the primary Key, and yeah I should probibly use that. And I will change it to it now, I don't remember why I had it as that in the first place.
But if it's any matter, the username was also unique, only one person has the username of what ever it is.

[whammy]

If you're sure the username is unique that's fine, but it's just a good habit to use the primary key (especially when updating a database) and check other records to make sure you don't duplicate data. :)

WHERE clauses are very important, you should usually use something unique in your where clause (like a primary key in a database), so you don't accidentally update records (for example, a colleague of mine accidentally updated 50,000 records with the same data, because he was updating a database depending on a session cookie, and that user had cookies disabled on his machine!) that you had no intention of updating!

[Morgoth]

Ouch.. that is sad...

That is also why everytime I add new data when I am creating scripts, I back up my db that has (not alot at all) some data in it that is important.

That reminds me.
How can I check to see if people have cookies enabled? Or better yet, what would the script be to find out if they have cookies enabled?

example:
If cookies = enabled Then
Allow
Else
Your Cookies aren't enabled.
End If

[whammy]

Well, it boils down to this... NEVER depend upon cookie (or sessions in classic asp since that relies on cookies) data to update your database!

If the user has cookies disabled, all the related variables will be empty... so your where clause will not matter. See? :D

[Morgoth]

I don't use cookies to update my database I use my cookies to keep information about a person so I can call them by their name when the enter my site or so they don't need ot re log into my site to post on the forum.

You know what I mean?

I guess it doesn't matter I don't think I will have a problem, cause if they try to login it stores the info in a cookie. If they log out it deletes the cookie, and if they try to login with cookies disabled it will not proccess the rest of the data, so it will say Your computer doesn't accept cookies and it needs to, to login.


I think I have solved this problem..
You can close it or what ever now.

[whammy]

:cool: