We help IT Professionals succeed at work.

Problem with rst.AddNew

ProfJames
ProfJames asked
on
Medium Priority
929 Views
Last Modified: 2008-03-10
Hi,

I would appreciate some help with this problem, pls!

I have code which adds a new record to a recordset and it is wrapped up in a loop.

I know the code I've got works (of a sort) as the first record always gets inserted but none of the rest of the records do. I was wondering if you can insert more than 1 record at a time, tho it seems daft to me that you cant.

My code is as follows:

set rst = createobject("ADODB.Recordset")
rst.Open "[Project Countries]", cnnSearch, 2,3, 2
For i=1 to 2
rst.AddNew
rst("Code") = 99999
rst("CreateID") = "hjames"
rst("Country") = request.form("Country")
rst.update
response.write rst("ID")
Next
rst.close
set rst = nothing

ID is an autonumber field and it returns an ID for both sets of records so when it does that I assume it has inserted the second record but when I look in the table it isnt there and yet when I try inserting another records the one that gets inserted this time has the correct ID number, ie 1 more than the record that didnt get inserted, if you see what I mean. so I know it gets inserted at some point but then its almost like its deleted straight away and I dont understand why.

Any help greatly appreciated!!
Heather

Comment
Watch Question

Bob ButcheriSeries Engineer

Commented:
by chance is there a primary key in one of those fields that you are entering the same data in?

Author

Commented:
my primary key field is the ID autonumber field so there shouldn't be any entering of the same data.
As an idea try;

for i=1 to 2
CnnSearch.Execute "INSERT INTO [project Countries](Code, CreateID, Country) VALUES (99999, 'hjames','" & Request("Country")
next

Also have you put 'On Error Resume Next' anywhere, this would bypass any errors that might be occuring.

Best of luck

Author

Commented:
@?>&*(%&@!!!

you are so right! I had put on error resume next and Id completely forgotten! - it just gets pasted in automatically with my db connection code and I always forget its there!

I checked for an error and I did have one which I can now correct, thanks for you help!

H

Your welcome

Explore More ContentExplore courses, solutions, and other research materials related to this topic.