im building an online portal for a uni project and one of the modules available allows the user to save their bookmarks in the db.

the code works fine, it adds the record but instead of adding 1 it adds 2 identical records.

here is the statement :

sql = INSERT INTO bookmarks (user_id, url) Values ('7', 'http://www.google.com');

i have a 3rd field called url_id which is the primary key and set to autonumber, could this have something to do with it?

if i run the sql through a normal query in ms access it works ok adding 1 record...

heres the main bit of my code..



//open up the database
DatabasePath="project.mdb";
conn = Server.CreateObject("ADODB.Connection");
conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" + Server.MapPath(DatabasePath));


// create new entry
sql = "INSERT INTO bookmarks (user_id, url) " + "Values ('"+user_id+"', '"+url+"');";


//execute statement via a recordset object
rst = Server.CreateObject("ADODB.Recordset");
rst.Open(sql,conn,1);
conn.Execute(sql);

Response.Write("<p>sql = "+sql)


anyideas ? short of inserting the url_id dynamically through the script

btw im using access 2000

thanks for reading
-Afy-

You repeat the query twice, that's why you get two records :

//execute statement via a recordset object
rst = Server.CreateObject("ADODB.Recordset");
rst.Open(sql,conn,1);
conn.Execute(sql);

try it without the recordset :

//execute statement via a recordset object
conn.Execute(sql);

well spotted !

works fine now, thanks.

Afy

In your code, u have created a connection object and a recordset object.
You are executing the sql query two times here.

Use either conn.execute(sql) or rst.Open (sql,conn,1)

Here conn.execute will execute ur sql query and the data is being inserted to db and again from recordset object also again it is being executed.

use either connection.execute or recordset object.

I am poor in english, if u find any mistake in explanation...just ignore it

Regards,
Kishan.


//open up the database
DatabasePath="project.mdb";
conn = Server.CreateObject("ADODB.Connection");
conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" + Server.MapPath(DatabasePath));


// create new entry
sql = "INSERT INTO bookmarks (user_id, url) " + "Values ('"+user_id+"', '"+url+"');";


//execute statement via a recordset object
rst = Server.CreateObject("ADODB.Recordset");
rst.Open(sql,conn,1);
conn.Execute(sql);

Response.Write("<p>sql = "+sql)

You are writing in double. Each time you invoque the script it will execute the content of the string (your variable named sql) twice...

You may use only ONE of these lines
rst.Open(sql,conn,1)
conn.Execute(sql)

______________
Paulo Gonçalves