S_A
Posts: 29
|
| Posted: 09/27/2004, 2:49 AM |
|
Hi, I have a problem with getting a table to update once a user performs a specific action. The code below is called from the forms "After Execute Insert" event - i.e. once the record is successfully submitted. I then need to run this custom SQL to insert a record into the user table as I only want them to register once and I don't want to use cookies!
I get no error and the table does not update.....
Any ideas? ASP, IIS and MSAccess
dim Connection
Set Connection = New clsDBConnection1
Connection.Open
Connection.Execute("UPDATE tbl_reg_users SET User_Reg=True, Date_Reg=#" & Now() & "# WHERE User_Name=" & CCGetUserID)
Connection.Close
Set Connection = Nothing
Thanks,
Scott.
|
 |
 |
peterr
Posts: 5971
|
| Posted: 09/27/2004, 3:23 AM |
|
I recommend that you do little debugging and print the value of CCGetUserID first.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
S_A
Posts: 29
|
| Posted: 09/27/2004, 3:42 AM |
|
Peter, thanks I already did that and I get the correct user ID returned. I added CCGetUserID as the default value of a text box and when I'm logged in I get the correct name displayed.......I guess it must be syntax or something but I'm stumped...... Do you have any further suggestions please?
Thanks,
Scott.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 09/27/2004, 4:13 AM |
|
Maybe try:
response.write "UPDATE tbl_reg_users SET User_Reg=True, Date_Reg=#" & Now() & "# WHERE User_Name=" & CCGetUserID
response.end
Once the SQL is printed paste it directly into MS Access and investigate why the record is not updated.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
S_A
Posts: 29
|
| Posted: 09/27/2004, 5:16 AM |
|
 Peter - Thanks !! I got it working thanks to your tip!! That's a good way to see exactly what is going on. Basically the code was missing the ' around the userID. So when viewed in Access it had actually added the user ID as a parameter and not a value.....so of course the query could not run.
The printed out SQL before the fix looked like this:-
UPDATE tbl_reg_users SET User_Reg=True, Date_Reg=#27/09/2004 13:12:12# WHERE User_Name=JOEBLOGGS
Note the lack of the ' either side of the username
After the fix of course it looked like this....
UPDATE tbl_reg_users SET User_Reg=True, Date_Reg=#27/09/2004 13:12:12# WHERE User_Name='JOEBLOGGS'
As usual with this kind of stuff I had to surround the desired character in " so the modified statement that fixed the problem is shown below.
Connection.Execute("UPDATE tbl_reg_users SET User_Reg=True, Date_Reg=#" & Now() & "# WHERE User_Name=" & "'" & CCGetUserID &"'")
Thanks again,
Scott.
|
|
|
|
|
