469,080 Members | 1,747 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,080 developers. It's quick & easy.

add new record - weird results

I have written a generic script in asp to add records to a table. The
script works fine with one table but in the other tables it updates
the first record in the table with the values for the new record and
adds a new record with all null values?!? Here is the script:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
'INSERT******************************************* ******************
'Open connection to sub-table
if action = "insert" then
cnnFormToDB.Open "SELECT top 1 * FROM " &subtable,
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta",
adOpenKeyset,adLockOptimistic
cnnFormToDB.AddNew
else
cnnFormToDB.Open "SELECT top 1 * FROM " & subtable & " WHERE ID = " &
ID, "DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
End If

if not cnnFormToDB.eof then
cnnFormToDB.MoveFirst
end if
'DELETE******************************************* ********************
if action = "delete" then
cnnFormToDB.Delete
cnnFormToDB.Close
else
'Build 2nd SQL String
For i=0 To Ubound(aFields)
cnnFormToDB(aFields(i)) = aValues(i)
Next

'Insert record into sub-table
cnnFormToDB.Update

The even weirder thing is I know that values in aFields and aValues
are OK because this test script I wrote for one of the tables works
just fine:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
cnnFormToDB.Open "SELECT top 1 * FROM FlightsDirect",
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
cnnFormToDB.AddNew

cnnFormToDB("fkCity") = 198
cnnFormToDB("fkDepartureAirport") = 159
cnnFormToDB("ValidFrom") = "17/09/2003"
cnnFormToDB("ValidTo") = "15/10/2003"
cnnFormToDB("fkType") = 1
cnnFormToDB("ReturnFlight") = 1
cnnFormToDB("fkReturnAirport") = 184
cnnFormToDB("Price") = yyyyyy
cnnFormToDB("fkATOL") = 5346

cnnFormToDB.Update
Any suggestions appreciated

Thanks
Alison
Jul 20 '05 #1
1 3307
Buttercup (al************@hotmail.com) writes:
I have written a generic script in asp to add records to a table. The
script works fine with one table but in the other tables it updates
the first record in the table with the values for the new record and
adds a new record with all null values?!? Here is the script:


I cannot really say what is going on. The problem with ADO is that
while it tries to hides to the SQL from you, it does actually makes
you more confused, because you don't know what is going on under the
covers.

You can use the Profiler to see what ADO submits to SQL Server.

However, rather than relying on ADO doing things right by chance, I
would encourage you to use stored procedures instead. Then you don't
use these .AddNew or .Update methods.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Rick | last post: by
5 posts views Thread by Andrew | last post: by
2 posts views Thread by Phil Stanton | last post: by
3 posts views Thread by William Wisnieski | last post: by
reply views Thread by Andy_Khosravi | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.