Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    5

    Exclamation Unanswered: simple SQL INSERT adds 2 records instead of 1???

    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-

  2. #2
    Join Date
    Feb 2003
    Location
    Los Angeles
    Posts
    18

    Lightbulb Try this

    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);
    Eddie.

  3. #3
    Join Date
    Nov 2002
    Posts
    5
    well spotted !

    works fine now, thanks.

    Afy

  4. #4
    Join Date
    Feb 2003
    Posts
    18

    Re: simple SQL INSERT adds 2 records instead of 1???

    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)

  5. #5
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Re: simple SQL INSERT adds 2 records instead of 1???

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •