add data to access - fail

Discussion in 'ASP General' started by dek, Mar 26, 2005.

  1. dek

    dek Guest

    please review my asp code:

    <%
    Dim adoCon ''hold database connection object
    Dim rsAddRecord ''hold recordset for new record to be added
    Dim strSQL ''hold the SQL query to query the database
    Dim strConnection ''hold data temp


    set adoCon = Server.CreateObject("ADODB.Connection")

    Set rsAddRecord=Server.CreateObject("ADODB.Recordset")

    adoCon.Open "FILEDSN=D:\dsn\database_dsn.dsn"

    strSQL = "SELECT Nama.ID, Nama.Nama, Nama.Umur, Nama.NoTelefon FROM Nama;"
    rsAddRecord.LockType=3
    rsAddRecord.Open strSQL, adoCon
    rsAddRecord.AddNew
    rsAddRecord.Fields("ID")=Request.Form("ID")
    rsAddRecord.Fields("Nama")=Request.Form("Nama")

    rsAddRecord.Update

    rsAddRecord.Close
    Set rsAddRecord=Nothing
    Set adoCon=Nothing
    ''redirect to the TestData.asp page
    Response.Redirect "TestData.asp"
    %>

    -----------------------------
    This message is posted by http://asp.forumszone.com
     
    dek, Mar 26, 2005
    #1
    1. Advertising

  2. I'd prefer something like this, but with data validation.

    Using DSNs is ill-advised.
    Creating a recordset object to do an insert is ill-advised.
    Your ID column isn't an identity column or some sort of other
    auto-incrementing column?

    <%
    Dim adoCon, strSQL, strConnection
    strConnection = "get your connection string at www.connectionstrings.com "

    strSQL = "INSERT INTO Nama (ID,Nama) VALUES (" & Request.Form("ID") & ",'" &
    Replace(Request.Form("Name"), "'", "''") & "'"
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Open strConnection
    adoCon.Execute strSQL,,129
    adoCon.Close
    Set adoCon = Nothing
    Response.Redirect "testdata.asp"
    %>


    Ray at home

    Set adoCon = CreateObject("ADODB.Connection")
    "dek" <> wrote in message
    news:...
    > please review my asp code:
    >
    > <%
    > Dim adoCon ''hold database connection object
    > Dim rsAddRecord ''hold recordset for new record to be added
    > Dim strSQL ''hold the SQL query to query the database
    > Dim strConnection ''hold data temp
    >
    >
    > set adoCon = Server.CreateObject("ADODB.Connection")
    >
    > Set rsAddRecord=Server.CreateObject("ADODB.Recordset")
    >
    > adoCon.Open "FILEDSN=D:\dsn\database_dsn.dsn"
    >
    > strSQL = "SELECT Nama.ID, Nama.Nama, Nama.Umur, Nama.NoTelefon FROM Nama;"
    > rsAddRecord.LockType=3
    > rsAddRecord.Open strSQL, adoCon
    > rsAddRecord.AddNew
    > rsAddRecord.Fields("ID")=Request.Form("ID")
    > rsAddRecord.Fields("Nama")=Request.Form("Nama")
    >
    > rsAddRecord.Update
    >
    > rsAddRecord.Close
    > Set rsAddRecord=Nothing
    > Set adoCon=Nothing
    > ''redirect to the TestData.asp page
    > Response.Redirect "TestData.asp"
    > %>
    >
    > -----------------------------
    > This message is posted by http://asp.forumszone.com
    >
     
    Ray Costanzo [MVP], Mar 26, 2005
    #2
    1. Advertising

  3. dek wrote:
    > please review my asp code:
    >
    > <%
    > Dim adoCon ''hold database connection object
    > Dim rsAddRecord ''hold recordset for new record to be added
    > Dim strSQL ''hold the SQL query to query the database
    > Dim strConnection ''hold data temp
    >
    >
    > set adoCon = Server.CreateObject("ADODB.Connection")
    >
    > Set rsAddRecord=Server.CreateObject("ADODB.Recordset")
    >
    > adoCon.Open "FILEDSN=D:\dsn\database_dsn.dsn"
    >
    > strSQL = "SELECT Nama.ID, Nama.Nama, Nama.Umur, Nama.NoTelefon FROM
    > Nama;"


    This is bad: you are retrieving all te data in the table without intending
    to use any of it.

    > rsAddRecord.LockType=3
    > rsAddRecord.Open strSQL, adoCon
    > rsAddRecord.AddNew


    This is a very inefficient way to add a record to your table.

    > rsAddRecord.Fields("ID")=Request.Form("ID")
    > rsAddRecord.Fields("Nama")=Request.Form("Nama")
    >


    Why did you retrieve Umur and NoTelefon?

    > rsAddRecord.Update
    >
    > rsAddRecord.Close
    > Set rsAddRecord=Nothing
    > Set adoCon=Nothing
    > ''redirect to the TestData.asp page
    > Response.Redirect "TestData.asp"
    > %>


    I would prefer this:

    Dim cn 'no need for long variable name - "cn" is universal
    Dim cmd 'Command object variable
    Dim arParms 'array to hold parameter values
    Dim strSQL
    Dim strConnection

    strConnection = "<ole db connection string>"
    'see www.able-consulting.com/ado_conn.htm
    'or www.connectionstrings.com

    strSQL = "INSERT INTO Nama (ID,Nama) VALUES (?,?)"
    arParms =Array(Request.Form("ID"), Request.Form("Nama"))

    set cn=createobject("adodb.connection")
    cn.open strConnection

    set cmd=createobject("adodb.command")
    cmd.CommandText=strSQL
    set cmd.ActiveConnection=cn
    cmd.Execute ,arParms, 129
    set cmd=nothing
    cn.close:set cn=nothing

    I prefer using a Command object to pass parameters to your sql statement vs.
    using dynamic sql (concatenation) because
    1. It prevents hackers from using sql injection to hack your database
    2. It's easier to write the code for this since you don't have to worry
    about delimiters (quotes)
    3. It performs the slightest bit faster than dynamic sql

    Bob Barrows
    PS. The 129 in the cmd.Execute statement is the result of the addition of 2
    constants:

    1 - adCmdText - Tells ADO that you are executing a sql string - you should
    use this setting when opening a recordset as well:
    Set rs = cn.Execute(strSQL,,1)

    128 - adExecuteNoRecords - Tells ADO that it does not have to construct a
    recordset object since the query being executed does not return records. If
    you do not specify this setting, ADO will waste time and resources creating
    a recordset object that will never be used
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Mar 26, 2005
    #3
  4. dek

    dek zorro Guest

    thax's guy... for you-all support.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    dek zorro, Mar 28, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rhino
    Replies:
    17
    Views:
    1,041
    Rhino
    Feb 24, 2005
  2. Wenjie

    if (f() != FAIL) or if (FAIL != f())?

    Wenjie, Jul 28, 2003, in forum: C Programming
    Replies:
    3
    Views:
    465
    E. Robert Tisdale
    Jul 31, 2003
  3. MING@HongKong
    Replies:
    0
    Views:
    431
    MING@HongKong
    Aug 9, 2007
  4. Savvoulidis Iordanis

    XML data access or DB data access ?

    Savvoulidis Iordanis, Jan 7, 2009, in forum: ASP .Net
    Replies:
    2
    Views:
    424
    Savvoulidis Iordanis
    Jan 7, 2009
  5. FAQ server
    Replies:
    3
    Views:
    105
    Dr J R Stockton
    Aug 17, 2010
Loading...

Share This Page