inserting records inconsistent

record insert inconsistently

[logicmystic]

I'm inserting records to an Access db from an input form using ASP. Not 100% of these records make it into the database. Sometimes as many as 3 out of 10 don't make it into the db. There is no error message or warning of any kind that the record didn't make it. (I can tell when it happens because my page doesn't say 'Done' in the lower left corner of the web page. I check the database and sure enough, they aren't there.) It still moves me to my confirm page and sends the email regarding a new record has been added.

I'm inserting records with ASP and have coded my connection 3 different ways and the insertion 2 different ways and am still getting the problem.

Has anyone seen this before?

Some of the code I'm using follows but both have the same inconsistent results.

set CN=Server.CreateObject("ADODB.Connection")
CN.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\path\filename;User ID=admin;Password=;"

'DbConn.BeginTrans
set rsAdd = Server.CreateObject("ADODB.Recordset")
set rsAdd.ActiveConnection = CN
rsAdd.LockType = adLockOptimistic
rsAdd.CursorLocation = adUseServer
rsAdd.CursorType = adOpenKeyset
rsAdd.open "tblInputFarms"

function AddRecord()

dim strSelect
if request.Form("Status") <> "" then
CN.Begintrans
'rs.open strSelect, DbConn, adOpenDynamic, 1, adcmdtext
rsAdd.addnew
rsAdd.fields("Status").value = request.Form("Status") & " "
rsAdd.fields("FarmName").value = request.Form("FarmName") & " "
rsAdd.fields("Addr1").value = request.Form("Addr1")
rsAdd.update
CN.CommitTrans
rsAdd.close
set rsAdd = nothing
end if

The other section of code does an insert into fldnames values valuelist. It was written by Dreamweaver.

Any ideas of how to trap for what is going on or ideas of what is going on would be greatly appreciated!

[MozMan]

You might try writing the insert with SQL instead. It may provide more reliable results, and the code will be simpler. It would look something like this:

set CN=Server.CreateObject("ADODB.Connection")
CN.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\path\filename;User ID=admin;Password=;"

'DbConn.BeginTrans
set rsAdd = Server.CreateObject("ADODB.Recordset")
set rsAdd.ActiveConnection = CN

Dim strSQL

strSQL = "INSERT INTO tblInputFarms (Status, FarmName, Addr1) VALUES ('" & Request.Form("Status") & "', '" & request.Form("FarmName") & "', '" & request.Form("Addr1") & "'"

rsAdd.Execute strSQL

-Moz

[logicmystic]

Moz,

I did the sql code (I was trying to say that with "The other section of code does an insert into fldnames values valuelist. It was written by Dreamweaver." Sorry I didn't explicitly say that.)

I received the same level of operation as when using the addNew code. Some records made it into the db and others not, for no apparent reason. The records were test records, very simple and essentially the same, except that I numbered them. When looking at the database sometime I'd be missing #8 or #13, so I could tell they had not all made it into the db.

Any other ideas?

[txbakers]

could be data mismataches that aren't throwing exceptions.

Try to insert those records manually through the QueryAnalyzer.

That's usually my first line of debug - I print the query to the screen, capture it and see if I can run it in the DB client. If I can, then I know it's an ASP problem somewhere.

[logicmystic]

txbakers,

I don't think I'm having a type mismatch as the data is all text fields in the db. Also the records I have been testing with are essentially identical. The same fields are filled in with data so similar that that shouldn't be causing the issue. For Example:
Status = new, FName = #1, State = AL, Country = USA Descript = #1

Another record containing:

Status = new, FName = #8, State = AK, Country = USA Descript = #8

may not make it into the database.

Any other ideas or things to try?

[logicmystic]

I have discovered something else that may help someone with ideas.

When the record doesn't insert, my session variables don't get set either.

[txbakers]

That's a good clue, something isn't posting correctly.

Put some "break points" in your code along with some Response.Write lines at various points, and check to see what your variables are (vs. what you think they should be)

You could be sending something in with an apostrophe or another special character that is throwing off the query.

[MozMan]

A very good idea. do a response.write of your session variables at every juncture (all ifs and elses, whiles, etc) to see how the code is flowing. Now that you mention that the session variables are not populating, it sounds more like an ASP issue, and less like a SQL issue.

[logicmystic]

Thanks! I'll try that. I won't be able to try it until next week.

But I did find a way to 'get around' the issue. I've restructured my code and now when the insert fails the user is left on the submit page. Hopefully thinking that they didn't click on the submit button correctly and hopefully clicking on it again.

It's not great but better than before!