If file exists 1

[hamish75]

I have the following code that updates a SQL database with a record. This works fine.

For i = 0 to Ubound(num)
if len(num(i))=2 then

rs = "INSERT INTO Model ([BusinessID], [Model Code], [Model Description])"
rs = rs & " VALUES"
rs = rs & "('" & first_part & "', '" & num(i) & "', '"  & "Model Code " & num(i) & " for Business " & second_part  & "')"
else
Response.Redirect("test.asp?a=not a valid entry")
end if

Set obj_RS1 = obj_CN.Execute(rs, adBoolean)

Next

Response.Redirect("test.asp?a=added")

What I’d like to do is produce another Response.Redirect if the record already exists.

Can anyone help?

Thanks.


Ah dinnae ken...

 

[foxbox]

For i = 0 to Ubound(num)

if len(num(i))=2 then

rs = "INSERT INTO Model ([BusinessID], [Model Code], [Model Description])"
rs = rs & " VALUES"
rs = rs & "('" & first_part & "', '" & num(i) & "', '"  & "Model Code " & num(i) & " for Business " & second_part  & "')"


else

 Response.Redirect("test.asp?a=not a valid entry")

end if

ON ERROR RESUME NEXT

Set obj_RS1 = obj_CN.Execute(rs, adBoolean)

if err.number <> 0 then
 response.redirect "errorpage.asp"
 response.end
end if

ON ERROR GOTO 0   'Back to normal behavior


Next

Response.Redirect("test.asp?a=added")
[code]

 

[hamish75]

Works perfect! Thank you for your help!

Ah dinnae ken...

 

[hamish75]

Foxbox, Do you know if is there another way of doing this without using the ON ERROR RESUME NEXT?

Thank you.

Ah dinnae ken...

 

[foxbox]

i believe we came to the conclusion (in another TT thread) that the best way of preventing duplicates in a table is to define a unique key index.

You may rewrite things so your program start with a check if the record exist, and if not do the INSERT.... but what if 2 people do an update at the same moment (oke, changes maybe extreme low, but still this might happen). If you are dead sure only 1 user is working (eg because its an admin function, and you're the only admin) then it might work.

What is your problem with ON ERROR?

 

[hamish75]

I really just wanted to see an alternative. I wasn't sure if it was the best method.

Thanks for your help!

Ah dinnae ken...