Jeff P
|
| Posted: 02/22/2005, 1:58 AM |
|
Can anyone please correct my UPDATE code?
I am trying to update a field called 'topic_update' with the current date and time in a table called 'TblForumTopics' after a new record has been added to a table called TblForumReplies', which has a field 'parent_topic_id' holding the value of the field 'topic_id' in 'TblForumTopics'.
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
SQL = "UPDATE TblForumTopics SET topic_update = Now WHERE topic_id = parent_topic_id"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
|
|
|
 |
Alcides Soares
|
| Posted: 02/22/2005, 2:08 AM |
|
Just use now() instead of now. It works with mysql.
Ex:
UPDATE tblusers SET txtLastSessionEnd=now() WHERE intUser=2;
OK?
|
|
|
|
Jeff P
|
| Posted: 02/22/2005, 3:52 AM |
|
Thanks, Alcides. I should have said I was using an Access database but Access uses the same Now() function so it should have worked. It didn't.
I've simplified the code to:
Dim Connection
Set Connection = New clsDBConnection1
Connection.Open
Connection.Execute("UPDATE TblForumTopics SET topic_date_update = Now() WHERE topic_id = parent_topic_id")
Connection.Close
Set Connection = Nothing
|
|
|
|
Jeff P
|
| Posted: 02/22/2005, 3:56 AM |
|
... oh, and I should have said that I placed the code in the AfterExecutetInsert action. Perhaps that's the wrong location?
|
|
|
|
dataobjx
Posts: 181
|
| Posted: 02/22/2005, 8:23 AM |
|
Actually for access you may need to alter the sql as follows;
FROM:
Connection.Execute("UPDATE TblForumTopics SET topic_date_update = Now() WHERE topic_id = parent_topic_id")
TO:
Connection.Execute("UPDATE TblForumTopics SET topic_date_update = #" & Now() & "# WHERE topic_id = parent_topic_id")
_________________
www.DataObjx.net
www.mydigitalapps.com |
 |
|
Tuong Do
|
| Posted: 02/22/2005, 6:19 PM |
|
Since parent_topic_id is a variable
Change the SQL to:
Connection.Execute("UPDATE TblForumTopics SET topic_date_update = #" & Now()
&
"# WHERE topic_id = " & parent_topic_id
<JeffP@forum.codecharge (Jeff P)> wrote in message
news:6421b022e7f925@news.codecharge.com...
> Can anyone please correct my UPDATE code?
>
> I am trying to update a field called 'topic_update' with the current date
> and
> time in a table called 'TblForumTopics' after a new record has been added
> to a
> table called TblForumReplies', which has a field 'parent_topic_id' holding
> the
> value of the field 'topic_id' in 'TblForumTopics'.
>
> Dim SQL
> Dim Connection
> Dim ErrorMessage
>
> Set Connection = New clsDBConnection1
> Connection.Open
> SQL = "UPDATE TblForumTopics SET topic_update = Now WHERE topic_id =
> parent_topic_id"
> Connection.Execute(SQL)
> ErrorMessage = CCProcessError(Connection)
> Connection.Close
> Set Connection = Nothing
> On Error Goto 0
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Jeff P
|
| Posted: 02/23/2005, 12:27 AM |
|
 Many thanks guys!
Although the solutions offered didn't work in my case, they did provide enough clues for me to make sense of other examples in CCS Help (you might have guessed that I'm not a programmer !!!).
The variable 'topic_id' already exists as a parameter in the page I'm working with, so the code that works for me uses CCGetFromGet and is as follows:
' -------------------------------------
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
SQL = "UPDATE TblForumTopics SET topic_date_update = " & Connection.ToSQL(Now(), ccsDate) & " WHERE topic_id = "& Connection.ToSQL(CCGetFromGet("topic_id",0),ccsInteger) &" "
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
' ---------------------------------------
Now, because I'm not a programmer, can anyone please explain why
CCGetFromGet("topic_id",0),ccsInteger
needs the 0 after topic_id? What does it do?
|
|
|
|
