charles
Posts: 59
|
| Posted: 03/23/2006, 8:03 PM |
|
I have struggled with this for days.
I will really appreciate if any one can help me figure this out.
I have a record form based on a table called terminations.This table has a related field called terminate in the employees table.
What i want to do is that whenever the user submits the terminations record form,the terminate field in the employees table is updated for the employee_id selected in the record form.
Here is my code in the After insert event of the terminations record form.
Dim terminated
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
terminated=terminations1.employee_id.value
SQL = ("Update employees" &_
"set terminate=terminated "& _
"where employee_id=terminated")
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
Thanks
Charles
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 03/24/2006, 3:18 AM |
|
You are unfortunately not in my Language realm, but at first sight I would say you need to put spaces in the SQL strings you are building, and concatenating.
So:
SQL = {'Update employees" &_
" set erminate=terminated"&_
" where employee_id=terminated")
AND you are intrerchanging the field terminated
meaning: you set teminated to terminations1.employee_id.value and then in the SQL you use that to select the right record in the Where and uou update the field terminate with that same content. This might be on purpose but it caught my eye as being strange I would expect terminate to be set to true or 1 or something like that.
Also echoing the SQL statement would help you determine if it is a valid statement in itself.
Hope this bring you something.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/24/2006, 3:19 AM |
|
the "{" should be a "("
the single and double quote mixing is an error on my part, use single.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 03/24/2006, 11:56 AM |
|
I am not certain what Dim terminated holds.
Assuming it is a numeric valuw which you want set in both of these fields
then your query should be:
SQL = "Update employees" &_
"set terminate=" & terminated & _
"where employee_id=" & teminated
If you ar trying to enter the value "termindated" in field "teminate"
SQL = "Update employees" &_
"set terminate='terminated'" & _
"where employee_id=" & teminated
|
|
|
|
zolw
Posts: 51
|
| Posted: 03/24/2006, 12:22 PM |
|
Try this.
Dim terminated
Dim SQL
Dim Connection
Dim ErrorMessage
Set Connection = New clsDBConnection1
Connection.Open
terminated=terminations1.employee_id.value
SQL = "(Update employees " &_
"set terminate=terminated "& _
"where employee_id=" & terminated & ")"
Connection.Execute(SQL)
ErrorMessage = CCProcessError(Connection)
Connection.Close
Set Connection = Nothing
On Error Goto 0
_________________
Zolw
http://www.xlso.com |
|
|
|
mhope
Posts: 37
|
| Posted: 03/24/2006, 2:01 PM |
|
If it's a form you're pulling from then
terminated=terminations1.employee_id.value
replace with
terminated=Request.Form("employee_id")
|
|
|
|
Charles yakub
|
| Posted: 03/25/2006, 5:02 AM |
|
First i tried Benjamin's Code:
terminated=terminations1.employee_id.value
SQL = ("Update employees" &_
"set terminate='terminated' "& _
"where employee_id="& terminated)
Unfortunately the problem persisted:The Employee's table was not updated.
I then tried zolw's code:
terminated=terminations1.employee_id.value
SQL = "(Update employees " &_
"set terminate=terminated "& _
"where employee_id=" & terminated & ")"
Still the Problem persisted:The Employees table was not updated
As Mhope suggested i then changed
terminated=(request.form("employee_id"))
Still no luck!
What could i be doing wrong?
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 03/25/2006, 12:02 PM |
|
We need more information.
It would help if you tell us which event you are using to fire this code.
Also, what you may want to do is the following:
After you create your SQL, add the following lines:
response.write "SQL=" & SQL & "<br>" & "terminated = " & terminated
response.end
Also, please post the structure of the employees table.
|
|
|
|
