#1
  1. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Oct 2004
    Posts
    244
    Rep Power
    11

    Smile Adding records to Database.... INSERT INTO or rsName.addNew ....


    Hi there,

    I was just wondering which is the best way to add a new record to a database via an asp page, via INSERT INTO sql statement, or via a recordset connection... rsName.addNew rsName.Update
    What are the advantages / dissadvantages of each?

    Your advice would be soooo much appreciated, thank you
  2. #2
  3. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2005
    Posts
    8
    Rep Power
    0

    Cool


    Originally Posted by Pluto
    Hi there,

    I was just wondering which is the best way to add a new record to a database via an asp page, via INSERT INTO sql statement, or via a recordset connection... rsName.addNew rsName.Update
    What are the advantages / dissadvantages of each?

    Your advice would be soooo much appreciated, thank you
    The answer I like the best is a stored_proc that way I can update or create with one sqlo call but, in all truth it depends on what you are doing.

    I would suggest the sql and the insert for most items that way you could do something like the following

    Code:
    sql = "INSERT INTO MyTable (Name, Address) VALUES('" & Name & "', '" & Address & "')"
    oCn.Execute sql
    You do need to watch out for single quotes in you data since they will break you sql string.

    In this way you do not have to even open a recordset to execute your insert, but if you are checking to see if the record exist you may want to do it this way.

    Code:
     
    sql = "SELECT * FROM MyTable where Name = '" & Name & "'"
    oRs.Open sql, oCn, 2, 3
      IF oRs.EOF then
    	 oRs.AddNew
      END IF
      oRs("Name") = Name
      oRs("Address") = Address
      oRs.Update
    oRs.CLose
    And of course my favorite would be the following

    Code:
     
    CREATE Procedure sp_UpdateMyTable
    (
    	 @Name varchar(15),
    	 @Address varchar(30)
    )
     
    DECLARE @nCnt int
     
    SELECT @nCt = count(*) 
    FROM MyTable 
    WHERE Name = @Name
     
    IF (@nCnt > 0)
    BEGIN
    	 UPDATE MyTable
    	 SET Name = @Name, Address = @Address
    END
    ELSE
    BEGIN
    	  INSERT INOT MyTable
    	  (Name, Address)
    	  (@Name, @Address)
    END
     
    return
    Then just call the stored proc via a sql query and a execute.


    GOOD LUCK
  4. #3
  5. Unholy Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Location
    In hell, where did you think?
    Posts
    12,026
    Rep Power
    658
    Best way to do it is through a stored procedure.

    using inline sql statements is okay, but leaves you at risk for sql injection attacks.

    using rs.addnew requires the server to create another object in memory.

    I never use an explicit recordset object (rs.addnew), I find they just add more code, which isn't necessary and they are a waste of server resources.

Similar Threads

  1. Securing A Database
    By TBÁrpi in forum Microsoft Access Help
    Replies: 31
    Last Post: January 25th, 2005, 05:23 PM
  2. SQL server creates 2 records for a single insert SQL query
    By thoidi8 in forum Microsoft SQL Server
    Replies: 2
    Last Post: September 30th, 2004, 04:48 PM
  3. help, trying to insert a record into a database.
    By HareshKainth in forum ASP Development
    Replies: 7
    Last Post: July 14th, 2004, 05:32 PM
  4. insert table in database
    By paurav in forum .NET Development
    Replies: 1
    Last Post: June 9th, 2004, 03:45 PM
  5. Replies: 0
    Last Post: February 24th, 2002, 04:19 PM

IMN logo majestic logo threadwatch logo seochat tools logo