recordset.addNew

microsoft.public.inetserver.asp.general

28 Apr 2009 9:48 PM

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_info.Open 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_info.Open 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

29 Apr 2009 7:11 AM

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
==================================================

--------------------
Show quoteHide quote

Are all your drivers up to date? click for free checkup

29 Apr 2009 10:51 AM

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.

Vince Xu [MSFT] wrote:

> Hello Betty,

<snip>
Show quoteHide quote

>>
>> 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_info.Open 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_info.Open 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

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

1 May 2009 4:36 AM

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.

--
Betty

Show quoteHide quote

"Bob Barrows" wrote:

> 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.
>
>
>
> Vince Xu [MSFT] wrote:
> > Hello Betty,
> <snip>
> >>
> >> 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_info.Open 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_info.Open 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
>
> --
> Microsoft MVP - ASP/ASP.NET - 2004-2007
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

1 May 2009 11:23 AM

Bob Barrows

c676228 wrote:

> 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

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

1 May 2009 4:30 AM

c676228

Hi Vince,

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

--
Betty

Show quoteHide quote

"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==?= <betty@newsgroup.nospam>
> | Subject: recordset.addNew
> | Date: Tue, 28 Apr 2009 14:48:01 -0700
> | Lines: 34
> | Message-ID: <C8608154-F779-49D8-8AA2-BC95BEB12***@microsoft.com>
> | 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_info.Open 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_info.Open 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
> |
>
>

5 May 2009 2:57 AM

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==?= <betty@newsgroup.nospam>
| References: <C8608154-F779-49D8-8AA2-BC95BEB12***@microsoft.com>

<rkF$PnJyJHA.2***@TK2MSFTNGHUB02.phx.gbl>
Show quoteHide quote

http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
Show quoteHide quote

| >
| > 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==?= <betty@newsgroup.nospam>
| > | Subject: recordset.addNew
| > | Date: Tue, 28 Apr 2009 14:48:01 -0700
| > | Lines: 34
| > | Message-ID: <C8608154-F779-49D8-8AA2-BC95BEB12***@microsoft.com>
| > | 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
Show quoteHide quote

| > | 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_info.Open 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_info.Open 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
| > |
| >
| >
|