February 26th, 2005, 10:20 AM
Adding records to Database.... INSERT INTO or rsName.addNew ....
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
February 27th, 2005, 02:09 AM
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.
Originally Posted by Pluto
I would suggest the sql and the insert for most items that way you could do something like the following
You do need to watch out for single quotes in you data since they will break you sql string.
sql = "INSERT INTO MyTable (Name, Address) VALUES('" & Name & "', '" & Address & "')"
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.
And of course my favorite would be the following
sql = "SELECT * FROM MyTable where Name = '" & Name & "'"
oRs.Open sql, oCn, 2, 3
IF oRs.EOF then
oRs("Name") = Name
oRs("Address") = Address
Then just call the stored proc via a sql query and a execute.
CREATE Procedure sp_UpdateMyTable
DECLARE @nCnt int
SELECT @nCt = count(*)
WHERE Name = @Name
IF (@nCnt > 0)
SET Name = @Name, Address = @Address
INSERT INOT MyTable
February 27th, 2005, 10:47 AM
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.