Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > add data to access - fail

Reply
Thread Tools

add data to access - fail

 
 
dek
Guest
Posts: n/a
 
      03-26-2005
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

 
Reply With Quote
 
 
 
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      03-26-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-26-2005
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"


 
Reply With Quote
 
dek zorro
Guest
Posts: n/a
 
      03-28-2005


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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
XML data access or DB data access ? Savvoulidis Iordanis ASP .Net 2 01-07-2009 10:47 PM
Access Control in WebUserControl Fail via aspx page MING@HongKong ASP .Net 0 08-09-2007 11:44 AM
Domain names fail to access the internet, ip addresses work ok =?Utf-8?B?c3RlZXJwaWtl?= Microsoft Certification 3 01-06-2006 04:04 AM
Why would an add() in a TreeSet subclass fail? Rhino Java 17 02-24-2005 05:09 AM
if (f() != FAIL) or if (FAIL != f())? Wenjie C Programming 3 07-31-2003 09:54 PM



Advertisments