Problem ADO recordset AddNew and Update adds 2 records to MS access DB

[Oliver Southgate]

Hi,

Hope someone can help. I have a problem with an ADO recordset that i
have never seen before.

The problem is when i do an rs.addnew and rs.update, it inserts two
records into the database, the docid field is an autonumber(primary
key) and text, memo and 2 datetime fields, you can see in the code
below, the field names.
It adds two records with all the same data, except the docid
eventhough the code is only called once and no error messages are
raised

I have tried changing the cursor and lock types to every combination.
I have tried using Array Fields and values, this does not work.

The only way to get it to work is with a SQL Statement, but would only
use that as a last last last resort. I would like to know if any has
had the same problem.

I am currently developing on XP Pro IIS 5.1 with classic ASP, but have
also tested on windows 2003, to which it also fails.

I hope someone can help

Ollie

Here is the code:

dim cnn, rs
dim m_connection
dim m_ErrorMessage
dim m_Completed

Public Sub Add()
Set cnn = Server.CreateObject("ADODB.connection")
Set rs = Server.CreateObject("ADODB.recordset")

Dim strSQL
m_ErrorMessage = ""
On Error Resume Next

m_Docid = -1

If CheckRequiredFields = False Then
m_Completed = False
Exit Sub
End If

Call OpenConnection()

If m_ErrorMessage = "" Then
strSQL = "Select * From UserDocuments Where Docid = " &
m_Docid & ""
rs.ActiveConnection = cnn

'rs.CursorType = 3 'Static cursor.
rs.CursorType = 2 'adUseServer

rs.LockType = 2 'Pessimistic Lock.
'rs.LockType = 3 ' adLockOptimistic

rs.Source = strSQL
rs.Open
If Not rs.EOF Then
m_ErrorMessage = "m_Docid|Already exists"
m_Completed = False
rs.Close
Call CloseConnection()
Exit Sub

Else
rs.AddNew
rs.Fields("Doctitle").Value = "OLLIE"
rs.Fields("Docbody").Value = "OLLIE"
rs.Fields("Docdatecreated").Value = Now()
rs.Fields("Docreleasedate").Value = Now()
rs.Fields("Docuser").Value = 1
rs.Update 'adds duplicates here
End If
rs.Close

If err.number <> 0 Then
m_ErrorMessage = err.number & ":" & err.Description
m_Completed = False
Else
m_Completed = True
End If

Else
m_Completed = False
End If

Call CloseConnection()
End Sub

Private Sub OpenConnection()
On Error Resume Next
cnn.Open m_connection
If err.number <> 0 Then
m_ErrorMessage = err.number & ":" & err.Description
End If
End Sub

Sub CloseConnection()
On Error Resume Next
cnn.Close
If err.number <> 0 Then
m_ErrorMessage = err.number & ":" & err.Description
End If
End Sub

'---------------------------------------------------------------

Here is the database table after
docID docTitle docBody docDateCreated docReleaseDate
docUser
16 OLLIE OLLIE 05/05/2004 21:50:53 05/05/2004 21:50:53
1
17 OLLIE OLLIE 05/05/2004 21:50:53 05/05/2004 21:50:53
1

[Frank Hickman]

What is the context of the Add subroutine? It is probably being called more
than once.

--
Frank

"Oliver Southgate" <oliver.s...@rbi.co.uk> wrote in message
news:f0ccc4.040505...@posting.google.com...

[Oliver Southgate]

Hi Frank,

The Add Sub is being used within an ASP Class, the class is fairly
big, so didn't want to post it all here, I have taken the code out and
tested it, it still doesn't work, the code is definatly only being
called once.

If i response.end directly after the rs.update, it still enters two
records.

Thanks

ollie

"Frank Hickman" <fhickman_nosp@m_noblesoft.com> wrote in message news:<6c6dnXjQe5o...@comcast.com>...

[Kevin Yu [MSFT]]

Thanks for Frank's quick response!

Hi Oliver,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that two records were added to the database
after update, although you only added 1 record to the recordset. If there
is any misunderstanding, please feel free to let me know.

Since this code is run on an ASP appliction, I agree with Frank's idea.
Please try to set a breakpoint in your code to see how many times the code
has been called exactly and how many records has been added.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Oliver Southgate]

Hi Kevin

Thats exactly what happens, I cannot step through the code in Interdev
using ASP classes, but i have used response.write and response.end to
debug it, if the code was being called twice, then a response.end
directly after the rs.update would result in only one record being added
and show that it is being called twice. But this isn't the case.

I have been through every line of code in both the call and calling asp
page. I am 100% sure it is not being called twice.

I am not doing anything Different than MS Knowledgebase Article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q193946

This is quite frustrating. I have been doing ADO inserts like this for a
long time and never had this problem before, could it be a bug in ADO.

I have looked in the registry HKEY_CLASSES_ROOT\ADODB.Recordset\CurVer
and its 2.7, i don't have 2.5 or 2.6 installed.

Hope this spreads a bit more light

Ollie

[Kevin Yu [MSFT]]

Hi Ollie,

Hi have tried these code on my computer, however, this cannot be
reproduced. Another probably case is that the update code was called only
once per request. However, the request was somehow being called twice. To
troubleshoot this, we can use a tool named Wfetch.exe. Here is a KB article
of how to use it.

http://support.microsoft.com/default.aspx?scid=kb;en-us;284285