dmhohf
Posts: 16
|
| Posted: 12/13/2006, 8:58 PM |
|
I'm trying to work my way through learning ASP and VB all at the same time. I copied (from peterr)and edited this code to enter data into a second table to log updates to the main tables records. But, of course, nothing happens outside of the main table being updated. The tb_credit_log has no entries.
I think it's how I'm referencing the varibles "LastID, UserIP, USER_ID, TimeStampEntry".
Function tb_credit_AfterUpdate() 'tb_credit_AfterUpdate @3-35BEF6EB
'Custom Code @35-73254650
Dim SQL
Dim LastID
Dim UserIP
Dim UserID
Dim TimeStampEntry
Dim Connection
SQL = "INSERT INTO tb_credit_log(CREDIT_ID, IP, USER_ID, TIME_STAMP) "&_
"VALUES (LastID, UserIP, USER_ID, TimeStampEntry)"
LastID = CCDLookUp("max(ID)","tb_track_mission","", DBocmUser)
UserIP = Request.ServerVariables("REMOTE_HOST")
UserID = CCGetUserLogin()
TimeStampEntry = Now()
Set Connection = New clsDBocmUser
Connection.Open
Connection.Execute(SQL)
Connection.Close
Set Connection = Nothing
'End Custom Code
End Function 'Close tb_credit_AfterUpdate @3-54C34B28
Thanks,
D
|
 |
 |
Edd
Posts: 547
|
| Posted: 12/13/2006, 9:57 PM |
|
D,
The problem is the sequence of commands and concatenating the SQL correctly.
Function tb_credit_AfterUpdate() 'tb_credit_AfterUpdate @3-35BEF6EB
'Custom Code @35-73254650
Dim SQL
Dim LastID
Dim UserIP
Dim UserID
Dim TimeStampEntry
Dim Connection
LastID = CCDLookUp("max(ID)","tb_track_mission","", DBocmUser)
UserIP = Request.ServerVariables("REMOTE_HOST")
UserID = CCGetUserLogin()
TimeStampEntry = Now()
SQL = "INSERT INTO tb_credit_log(CREDIT_ID, IP, USER_ID, TIME_STAMP) "&_
"VALUES (" & DBocmUser.ToSQL(LastID, ccsInteger) & ", " & _
DBocmUser.ToSQL(UserIP, ccsInteger) & ", " & _
DBocmUser.ToSQL(USER_ID, ccsInteger) & ", " & _
DBocmUser.ToSQL(TimeStampEntry, ccsDate) & ")"
' Assumes User_ID is integer - look in the Help for "toSQL"
Set Connection = New clsDBocmUser
Connection.Open
Connection.Execute(SQL)
Connection.Close
Set Connection = Nothing
'End Custom Code
End Function 'Close tb_credit_AfterUpdate @3-54C34B28
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
dmhohf
Posts: 16
|
| Posted: 12/13/2006, 10:01 PM |
|
It's 1AM here, and I was hoping for a late night rescue. User_ID is "ccsText", but thanks for the heads up there too.
Thanks Edd! :)
|
|
|
|
dmhohf
Posts: 16
|
| Posted: 12/13/2006, 10:33 PM |
|
Well, I tried using the provided code and its still not updating to the table. I made the appropriate changes to the "toSQL" data types too.
I also tested with another page to insert and update to that table, to confirm that I can write to the table.
Is there some debuging code I can add to step through the process to see where the failure is at?
btw I'm using a MsSQL db
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/14/2006, 3:13 AM |
|
D,
Use the standard
Response.Write SQL
Response.End
before the function closes
Then copy and run that SQL in the query analyser.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
dmhohf
Posts: 16
|
| Posted: 12/14/2006, 7:20 PM |
|
I tried using responce.write and there is information displayed.Function tb_credit_AfterUpdate() 'tb_credit_AfterUpdate @3-35BEF6EB
'Custom Code @35-73254650
Dim SQL
Dim LastID
Dim UserIP
Dim UserID
Dim TimeStampEntry
Dim Connection
LastID = CCDLookUp("max(ID)","tb_track_mission","", DBocmUser)
UserIP = Request.ServerVariables("REMOTE_HOST")
UserID = CCGetUserLogin()
TimeStampEntry = Now()
SQL = "INSERT INTO tb_credit_log(CREDIT_ID, IP, USER_ID, TIME_STAMP) "& _
"VALUES (" & DBocmUser.ToSQL(LastID, ccsInteger) & ", " & _
DBocmUser.ToSQL(UserIP, ccsText) & ", " & _
DBocmUser.ToSQL(USER_ID, ccsText) & ", " & _
DBocmUser.ToSQL(TimeStampEntry, ccsDate) & ")"
Response.Write SQL
Response.End
Set Connection = New clsDBocmUser
Connection.Open
Connection.Execute(SQL)
Connection.Close
Set Connection = Nothing
'End Custom Code
End Function 'Close tb_credit_AfterUpdate @3-54C34B28
Did I place this custom code after the wrong event? I'm lost. 
|
|
|
|
dmhohf
Posts: 16
|
| Posted: 12/14/2006, 7:50 PM |
|
Ok, after a few typos andplacing he code in the after insert portion I was able to get the SQL to display.
But still no luck with the after update portion... I'll try placing the code in the after insert event, but does that impact the max(id) number?
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/14/2006, 10:03 PM |
|
D,
Can you post what the SQL from the response.write looked like on the screen?
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
dmhohf
Posts: 16
|
| Posted: 12/15/2006, 2:32 PM |
|
The code works in the "AfterInsert()" event, but not the "AfterUpdate()" event.
And the "Max(ID)" number properly relates as the forgienKey. So it seems the problem is solved, but I'm just curious why one event is working and the other is not. Especially since the example in: http://forums.codecharge.com/posts.php?post_id=45926 from peterr specifically says the "AfterUpdate()" event. I'm just curious and cautious (described "anal" in some circles).
Here is the SQL anyways:
INSERT INTO tb_credit_log(CREDIT_ID, IP, USER_ID, TIME_STAMP) VALUES (13951, '167.165.35.200', 'dm0h123', '12/15/2006 16:28:05')
Thanks again Edd for your time.
|
|
|
|
dmhohf
Posts: 16
|
| Posted: 12/15/2006, 2:39 PM |
|
I just realized. Cause I'm not updating while testing, I'm inserting....god it's the small things...
I just doubled up the code in the update and insert events to facilatate either events wrting to the log table....
I wish I could remove this thread and just send you some cash Edd. :)
|
|
|
|
Edd
Posts: 547
|
| Posted: 12/15/2006, 4:42 PM |
|
Cash or Credit card - we take both 
Good job
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
