Thanks to Peter Lanoie and Eric Van Camp for past feedback - much 

appreciated.



I am struggling with a slightly different UPDATE problem and having read 

several examples and articles I still cannot see what I'm doing wrong. I'm 

trying to retrieve a field value and then insert it into another table 

(Details) as a foreign key.



'First I retrieve the values from another table

vacancyID = objRS.Fields("vacancyID").Value

contactID = objRS.Fields("contactID").Value



(I've 'responsed out' these values and they do contain the values I want 

and they are numeric (Long))



'I then try to insert the vacancyID value as a foreign key in Details

mySQL = "UPDATE Details "

mySQL = mySQL & "SET vacancyID =" & vacancyID

mySQL = mySQL & " WHERE contactID =" & vacContactID

objRS.Open mySQL, Con, adOpenStatic, adLockOptimistic



The code goes through without errors but the vacancyID field remains 

Null.  I've tried more or less every combination of ' and no-' but I think 

what I have above is correct.  Can anyone tel me what I'm doing wrong?  

I've reached a total dead-end :(



Many thanks in advance.

> vacancyID = objRS.Fields("vacancyID").Value

> contactID = objRS.Fields("contactID").Value



    Assuming the above is your real code...



> mySQL = mySQL & " WHERE contactID =" & vacContactID



    then you need to not use vacContactID (who has a null value) and use

contactID (which has the correct value).



    Just a hunch.



- Chuck



----- Original Message -----

From: "Jake C" <jakeone@b...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, April 17, 2001 11:32 PM

Subject: [asp_databases] update syntax pt.2





> Thanks to Peter Lanoie and Eric Van Camp for past feedback - much

> appreciated.

>

> I am struggling with a slightly different UPDATE problem and having read

> several examples and articles I still cannot see what I'm doing wrong. I'm

> trying to retrieve a field value and then insert it into another table

> (Details) as a foreign key.

>

> 'First I retrieve the values from another table

> vacancyID = objRS.Fields("vacancyID").Value

> contactID = objRS.Fields("contactID").Value

>

> (I've 'responsed out' these values and they do contain the values I want

> and they are numeric (Long))

>

> 'I then try to insert the vacancyID value as a foreign key in Details

> mySQL = "UPDATE Details "

> mySQL = mySQL & "SET vacancyID =" & vacancyID

> mySQL = mySQL & " WHERE contactID =" & vacContactID

> objRS.Open mySQL, Con, adOpenStatic, adLockOptimistic

>

> The code goes through without errors but the vacancyID field remains

> Null.  I've tried more or less every combination of ' and no-' but I think

> what I have above is correct.  Can anyone tel me what I'm doing wrong?

> I've reached a total dead-end :(

>

> Many thanks in advance.

> 

I've managed to figure out the problem.  I didn't close the Recordset 

after retrieving the first set of values.  Didn't know it could cause so 

much trouble. I'll try to write neater code in future ;)





> Thanks to Peter Lanoie and Eric Van Camp for past feedback - much 

> appreciated.

> 

> I am struggling with a slightly different UPDATE problem and having read 

> several examples and articles I still cannot see what I'm doing wrong. 

I'm 

> trying to retrieve a field value and then insert it into another table 

> (Details) as a foreign key.

> 

> 'First I retrieve the values from another table

> vacancyID = objRS.Fields("vacancyID").Value

> contactID = objRS.Fields("contactID").Value

> 

> (I've 'responsed out' these values and they do contain the values I want 

> and they are numeric (Long))

> 

> 'I then try to insert the vacancyID value as a foreign key in Details

> mySQL = "UPDATE Details "

> mySQL = mySQL & "SET vacancyID =" & vacancyID

> mySQL = mySQL & " WHERE contactID =" & vacContactID

> objRS.Open mySQL, Con, adOpenStatic, adLockOptimistic

> 

> The code goes through without errors but the vacancyID field remains 

> Null.  I've tried more or less every combination of ' and no-' but I 

think 

> what I have above is correct.  Can anyone tel me what I'm doing wrong?  

> I've reached a total dead-end :(

> 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: > 'I then try to insert the vacancyID value as a foreign key in Details

: > mySQL = "UPDATE Details "

: > mySQL = mySQL & "SET vacancyID =" & vacancyID

: > mySQL = mySQL & " WHERE contactID =" & vacContactID

: > objRS.Open mySQL, Con, adOpenStatic, adLockOptimistic

: >

: > The code goes through without errors but the vacancyID field remains

: > Null.  I've tried more or less every combination of ' and no-' but I

: think

: > what I have above is correct.  Can anyone tel me what I'm doing wrong?

: > I've reached a total dead-end :(



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Just a though - don't know if anyone has mentioned it, but you don't use a

recordset when updating a table. A recordset holds records (or columns from

records) that you've extracted from the underlying datasource - you don't

have any records when you update something...



<%

strSQL = _

    "UPDATE Details " & _

    "SET vacancyID =" & vacancyID & _

    " WHERE contactID =" & vacContactID



objConn.Execute strSQL,,adExecuteNoRecords+adCmdText



objConn.close

Set objConn = nothing

%>



is all you need.



Cheers

Ken