add new record - weird results

[Buttercup]

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
adLockOptimisti c=3
Set cnnFormToDB = Server.CreateOb ject("ADODB.Rec ordset")
'INSERT******** *************** *************** *************** ********
'Open connection to sub-table
if action = "insert" then
cnnFormToDB.Ope n "SELECT top 1 * FROM " &subtable,
"DSN=Barrheadsq l;UID=barrhead; PWD=ty93eta",
adOpenKeyset,ad LockOptimistic
cnnFormToDB.Add New
else
cnnFormToDB.Ope n "SELECT top 1 * FROM " & subtable & " WHERE ID = " &
ID, "DSN=Barrheadsq l;UID=barrhead; PWD=ty93eta", adOpenKeyset,
adLockOptimisti c
End If

if not cnnFormToDB.eof then
cnnFormToDB.Mov eFirst
end if
'DELETE******** *************** *************** *************** **********
if action = "delete" then
cnnFormToDB.Del ete
cnnFormToDB.Clo se
else
'Build 2nd SQL String
For i=0 To Ubound(aFields)
cnnFormToDB(aFi elds(i)) = aValues(i)
Next

'Insert record into sub-table
cnnFormToDB.Upd ate

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
adLockOptimisti c=3
Set cnnFormToDB = Server.CreateOb ject("ADODB.Rec ordset")
cnnFormToDB.Ope n "SELECT top 1 * FROM FlightsDirect",
"DSN=Barrheadsq l;UID=barrhead; PWD=ty93eta", adOpenKeyset,
adLockOptimisti c
cnnFormToDB.Add New

cnnFormToDB("fk City") = 198
cnnFormToDB("fk DepartureAirpor t") = 159
cnnFormToDB("Va lidFrom") = "17/09/2003"
cnnFormToDB("Va lidTo") = "15/10/2003"
cnnFormToDB("fk Type") = 1
cnnFormToDB("Re turnFlight") = 1
cnnFormToDB("fk ReturnAirport") = 184
cnnFormToDB("Pr ice") = yyyyyy
cnnFormToDB("fk ATOL") = 5346

cnnFormToDB.Upd ate


Any suggestions appreciated

Thanks


Alison

[Erland Sommarskog]

Re: add new record - weird results

Buttercup (alison_clark20 @hotmail.com) writes:[color=blue]
> 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:[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp