recordset.addNew

[c676228]

Hi all,

I googled a bit and didn't find good explanation about AddNew method. so I
post here.
What are the differences between these 2 versions:
1.

order_infpen cmdTemp, , adOpenKeyset, adLockOptimistic
if order_info.EOF then
order_info.AddNew
end if
order_info("EnrollDate").Value = Now()
order_info("City").Value = Request("City")
order_info("State").Value = Request("State")
order_info("Zip").Value = Request("Zip")
order_info("Email").Value = Request("Email")
....

2.
order_infpen cmdTemp, , adOpenKeyset, adLockOptimistic
if order_info.EOF then
order_info.AddNew
order_info("EnrollDate").Value = Now()
order_info("City").Value = Request("City")
order_info("State").Value = Request("State")
order_info("Zip").Value = Request("Zip")
order_info("Email").Value = Request("Email")
end if
...

I am wondering whether the 1 version is culprit caused duplicated records in
the table.

 

[Vince Xu [MSFT]]

Hello Betty,

As far as I known, when calling the AddNew method without arguments,it sets
the EditMode property to adEditAdd (an EditModeEnum value). The provider
caches any field value changes locally. Calling the Update method posts the
new record to the database and resets the EditMode property to adEditNone
(an EditModeEnum value). (The new record will be posted when Update method
is called.)
If you pass arguments, ADO immediately posts the new record to the database
(no Update call is necessary); the EditMode property value does not change
(adEditNone).(The new record will be posted directly when AddNew is called)

In logical aspect, these two approaches you listed are different.
Based on the approach one, when order_info.EOF is true, it will post a new
record as the approach two does. But if order_info.AddNew is false, it will
update the current record if it calls order_info.Update after these codes,
rather than appending a new record after the recordset. So, I don't think
it will cause duplicated records with this approach, unless it does submit
towice. Maybe other codes cause the duplicated records because of this
turbid code structure.


Based on the approach two, it will do post a new record to the database
clearly if order_info.EOF is true(if you append order_info.Update before
"end if"). I think if you want to add a new record, you can use this
approach.

If you want to know more about "AddNew", please check the following
reference:
http://msdn.microsoft.com/en-us/library/ms677536(VS.85).aspx

Sincerely,

Vince Xu

Microsoft Online Support


==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================




--------------------
| Thread-Topic: recordset.addNew
| thread-index: AcnISwCPDalLj7pKRT6Ht33VTVyi3g==
| X-WBNR-Posting-Host: 63.241.246.251
| From: =?Utf-8?B?YzY3NjIyOA==?= <[email protected]>
| Subject: recordset.addNew
| Date: Tue, 28 Apr 2009 14:48:01 -0700
| Lines: 34
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3168
| Newsgroups: microsoft.public.inetserver.asp.general
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.inetserver.asp.general:10218
| NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| X-Tomcat-NG: microsoft.public.inetserver.asp.general
|
| Hi all,
|
| I googled a bit and didn't find good explanation about AddNew method. so
I
| post here.
| What are the differences between these 2 versions:
| 1.
|
| order_infpen cmdTemp, , adOpenKeyset, adLockOptimistic
| if order_info.EOF then
| order_info.AddNew
| end if
| order_info("EnrollDate").Value = Now()
| order_info("City").Value = Request("City")
| order_info("State").Value = Request("State")
| order_info("Zip").Value = Request("Zip")
| order_info("Email").Value = Request("Email")
| ...
|
| 2.
| order_infpen cmdTemp, , adOpenKeyset, adLockOptimistic
| if order_info.EOF then
| order_info.AddNew
| order_info("EnrollDate").Value = Now()
| order_info("City").Value = Request("City")
| order_info("State").Value = Request("State")
| order_info("Zip").Value = Request("Zip")
| order_info("Email").Value = Request("Email")
| end if
| ...
|
| I am wondering whether the 1 version is culprit caused duplicated records
in
| the table.
| --
| Betty
|

 

[Bob Barrows]

Vince, I'm not sure why I did not see the original post; so forgive me for
replying to you rather than directly to Betty.

Betty, rather than look to your application code to prevent duplicates, you
should let your relational database do its job: create a primary key or
unique index on the fields that must not be duplicated so an error will be
raised when a data modification is made that would result in a duplicate.
The difference between the two versions is that the first version will set
the current record to the new values if the sql used in cmdTemp returns one
or more records. The second version will only set the values to a new record
if it is necessary to create one. I'm not sure what the intent is and you
did not show us the sql used in cmdTemp so I cannot really tell if that is
the culprit.

 

[c676228]

Hi Vince,

It does happen that for some reasons(network communication error, server's
slowness, etc), customers would submit twice.

 

[c676228]

Hi Bob,

Nice to hear from you again.

cmdTemp.CommandText = "select * from " & sProductTable & " where
order_id='" & order_id & "'"

basically the cmdTemp just check if this specific record is inserted into
the database or not.

The database table was designed long time ago and it was not real relational
database and by the time we want to enforce the primary, foreign keys etc.
The table has thousands of records, not doable.

Thank you so much.

 

[Bob Barrows]

Hi Bob,

Nice to hear from you again.

cmdTemp.CommandText = "select * from " & sProductTable & " where

Why is the table a variable? Do you have multiple tables with a primary key
of order_id? This seems like it could be a case of bad database design,
unless subclassing was necessary. I don't have enough information to judge.

order_id='" & order_id & "'"

basically the cmdTemp just check if this specific record is inserted
into the database or not.

If order_id is the primary key, then this is not the problem. Again, I see
nothing in either version of the code that would lead to duplicates. Again,
the two versions do different things depending on if an existing record is
found:
The first version sets the existing record's data to the new data in the
form submission.
The second version does nothing with the submitted data (unless you've left
out some subsequent code).

Which one is the intended action?

The database table was designed long time ago and it was not real
relational database and by the time we want to enforce the primary,
foreign keys etc. The table has thousands of records, not doable.

Of course it's doable. "thousands of records"? You say that as if it's a lot
of records ... ;-)
The only question is: do you want to do it?
What is the backend database now?

If order_id should be the primary key, this query will reveal which ones are
duplicated:

select order_id from orders group by order_id having count(*)>1

Now the only question that remains is: what do you want to do with those
duplicates? I would assume you want to eliminate the duplicates because
really, what meaning can multiple records about a single order_id convey?
Only one of those records is really relevant, correct? Anyways, this is as
far as I can take you without further information about the database type
and version and the table structure(s).
See this article for information about eliminating duplicates:
http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html

 

[Vince Xu [MSFT]]

Hello Betty,

Sorry for my delay to come back, because there is a national day in my
country. Now I'm available.
Based on my understanding, the code you used submits twice so that it posts
duplicate new records into the database.
Did you use any additional submit() function in your code besides the form
submit? Maybe some form1.submit() cause the duplicate submit. Please check
it according to this clue. If it can't help you out, please post your codes
in detail or post me the demo application.

Sincerely,

Vince Xu

Microsoft Online Support
--------------------
| Thread-Topic: recordset.addNew
| thread-index: AcnKFYEiCR/9GilzShWt4D4E9iRweQ==
| X-WBNR-Posting-Host: 64.85.242.28
| From: =?Utf-8?B?YzY3NjIyOA==?= <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: RE: recordset.addNew
| Date: Thu, 30 Apr 2009 21:30:06 -0700
| Lines: 130
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3168
| Newsgroups: microsoft.public.inetserver.asp.general
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.inetserver.asp.general:10235
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.inetserver.asp.general
|
| Hi Vince,
|
| It does happen that for some reasons(network communication error,
server's
| slowness, etc), customers would submit twice.
| --
| Betty
|
|
| "Vince Xu [MSFT]" wrote:
|
| > Hello Betty,
| >
| > As far as I known, when calling the AddNew method without arguments,it
sets
| > the EditMode property to adEditAdd (an EditModeEnum value). The
provider
| > caches any field value changes locally. Calling the Update method posts
the
| > new record to the database and resets the EditMode property to
adEditNone
| > (an EditModeEnum value). (The new record will be posted when Update
method
| > is called.)
| > If you pass arguments, ADO immediately posts the new record to the
database
| > (no Update call is necessary); the EditMode property value does not
change
| > (adEditNone).(The new record will be posted directly when AddNew is
called)
| >
| > In logical aspect, these two approaches you listed are different.
| > Based on the approach one, when order_info.EOF is true, it will post a
new
| > record as the approach two does. But if order_info.AddNew is false, it
will
| > update the current record if it calls order_info.Update after these
codes,
| > rather than appending a new record after the recordset. So, I don't
think
| > it will cause duplicated records with this approach, unless it does
submit
| > towice. Maybe other codes cause the duplicated records because of this
| > turbid code structure.
| >
| >
| > Based on the approach two, it will do post a new record to the database
| > clearly if order_info.EOF is true(if you append order_info.Update
before
| > "end if"). I think if you want to add a new record, you can use this
| > approach.
| >
| > If you want to know more about "AddNew", please check the following
| > reference:
| > http://msdn.microsoft.com/en-us/library/ms677536(VS.85).aspx
| >
| > Sincerely,
| >
| > Vince Xu
| >
| > Microsoft Online Support
| >
| >
| > ==================================================
| > Get notification to my posts through email? Please refer to
| >
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
| >
| > MSDN Managed Newsgroup support offering is for non-urgent issues where
an
| > initial response from the community or a Microsoft Support Engineer
within
| > 2 business day is acceptable. Please note that each follow up response
may
| > take approximately 2 business days as the support professional working
with
| > you may need further investigation to reach the most efficient
resolution.
| > The offering is not appropriate for situations that require urgent,
| > real-time or phone-based interactions. Issues of this nature are best
| > handled working with a dedicated Microsoft Support Engineer by
contacting
| > Microsoft Customer Support Services (CSS) at
| > http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
| > ==================================================
| >
| >
| >
| >
| > --------------------
| > | Thread-Topic: recordset.addNew
| > | thread-index: AcnISwCPDalLj7pKRT6Ht33VTVyi3g==
| > | X-WBNR-Posting-Host: 63.241.246.251
| > | From: =?Utf-8?B?YzY3NjIyOA==?= <[email protected]>
| > | Subject: recordset.addNew
| > | Date: Tue, 28 Apr 2009 14:48:01 -0700
| > | Lines: 34
| > | Message-ID: <[email protected]>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3168
| > | Newsgroups: microsoft.public.inetserver.asp.general
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.inetserver.asp.general:10218
| > | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| > | X-Tomcat-NG: microsoft.public.inetserver.asp.general
| > |
| > | Hi all,
| > |
| > | I googled a bit and didn't find good explanation about AddNew method.
so
| > I
| > | post here.
| > | What are the differences between these 2 versions:
| > | 1.
| > |
| > | order_infpen cmdTemp, , adOpenKeyset, adLockOptimistic
| > | if order_info.EOF then
| > | order_info.AddNew
| > | end if
| > | order_info("EnrollDate").Value = Now()
| > | order_info("City").Value = Request("City")
| > | order_info("State").Value = Request("State")
| > | order_info("Zip").Value = Request("Zip")
| > | order_info("Email").Value = Request("Email")
| > | ...
| > |
| > | 2.
| > | order_infpen cmdTemp, , adOpenKeyset, adLockOptimistic
| > | if order_info.EOF then
| > | order_info.AddNew
| > | order_info("EnrollDate").Value = Now()
| > | order_info("City").Value = Request("City")
| > | order_info("State").Value = Request("State")
| > | order_info("Zip").Value = Request("Zip")
| > | order_info("Email").Value = Request("Email")
| > | end if
| > | ...
| > |
| > | I am wondering whether the 1 version is culprit caused duplicated
records
| > in
| > | the table.
| > | --
| > | Betty
| > |
| >
| >
|