function switch between update and insert but can't work, help

[choohean]

I try to do some function by using if and then to switch between insert and update but it can't work... can anyone give me a hand please....

this is my coding:

SQL = "SELECT equip_id FROM tracking WHERE equip_id='"&equip_id&"' "
set rs1 = openConn.Execute(SQL)

dim compare
compare=(rs1("equip_id&quot)

if compare!=null Then

tSQL ="UPDATE tracking SET status='"&status&"' WHERE equip_id='"&equip_id&"' "
set rs = openConn.Execute(tSQL)

else
sSQL = "INSERT INTO tracking"
sSQL = sSQL &"(status)"
sSQL = sSQL &"VALUES ('"&status&"')"
set rs = openConn.Execute(sSQL)

 

[rksingh024]

Instead of
-------
if compare!=null Then
-----

Use this
----------
if ISNULL(compare) = false Then
----------


Ramesh

 

[choohean]

still can't work, If I try with different equip_id, this will come out:

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

 

[rksingh024]

Sorry for the earlier solution actually I focused on your null statement.

do not use this line

--compare=(rs1("equip_id&quot)

The correct code is this...

'After conn.execute, Check for the eof-bof of recordset
IF rs.eof and rs.bof then
'Means no record exists, so call the insert function
else
'means record exists, so call the update function
end if

--Always check eof-bof of recordset before using any property/method of recordset

Ramesh Singh

 

[williamu]

IMHO The easiest method is to use the PK ID value of the record. If the PK is 0 then the implication is that this is a new record since no value has been assigned.

If of course the value > 0 then again the implication is that the record exists and requires updating. When doing this I (as I've said before) use a stored procedure to do all my updating. The SP work on the above principal of a 0 ID means insert and > 0 means Update.

This approach allows me to use the same code in my application for insertions and updates.

I.E. {? = CALL Apply_NHSGP_Details(?, ?, ?, ?, ?, ?, ?)}

There are other advantages to this approach as well. I.E. I know that if the ID is > 0 I need to get it from the DB and display it in a form for example.

HTH


William
Software Engineer
ICQ No. 56047340

 

[tlbroadbent]

Instead of checking for a null value, just check if the record set is empty. It is empty if EOF is true when the record set is created.

If rs1.EOF = True
<Insert>
Else
rs1.Movefirst
<Update>
End If

I note that you are attempting to create a new record set (rs) with the Update and Insert statements. These action statements do not return a record set. You need to check the reference manual to make sure you are using the correct syntax for the statement.

Even though you may be using a SL Database, your question should probably posted in a Visual Basic or ActiveX forum.

forum222 - Visual Basic(Microsoft) -Version 5 & 6
forum194 - ActiveX Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[choohean]

sorry, I have try the solution that given as above but still got error....

It comes out like this:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

 

[bperry]

Going back to one of the earlier posts, I still think this code below should work. (I've turned the IF around to test for a true [is null] rather than a false [is not null]. Doesn't really matter, but I find it easier to look at an IF and read 'If my value IS this, then DO that.' Just personal preference.)
-------------------------------------------
IF isnull(rs.Fields(&quot;equip_id&quot.Value)
sSQL = &quot;INSERT INTO tracking &quot;
sSQL = sSQL &&quot;(status)&quot;
sSQL = sSQL &&quot;VALUES ('&quot;&status&&quot;')&quot;
set rs = openConn.Execute(sSQL)
else
tSQL =&quot;UPDATE tracking SET status='&quot;&status&&quot;'
WHERE equip_id='&quot;&equip_id&&quot;' &quot;
set rs = openConn.Execute(tSQL)
End If
---------------------------------------
These things are usually easily de-bugged by putting some kind of Print or Display statement in each leg of the IF, to determine which path is actually being taken.
----------------------------------------------
However, in any event it's not a good practice to directly allow Inserts/Updates to the database. IMHO, a better overall approach would be to pass both the equip_id and the status to a stored procedure, and let the SP decide if it needs to Insert or Update. Optionally, if we need to know just what action was taken, the SP could pass back a return code or output parameter indicating what action was taken.

There's a lot of soapbox, but not a lot of help. Perhaps that bit of code might work, though.

bp

 

[choohean]

Ya..It works...Thank all of you very much for helping me to solve the problem....

Really really thanks a lot....