ASP - add data to access

Author

add data to access - fail

dek

2005-03-26, 3:55 am

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]

2005-03-26, 8:55 am

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" <dekzorro@yahoo.com> wrote in message
news:640311822427368@asp.forumszone.com...
> 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
>

Bob Barrows [MVP]

2005-03-26, 3:55 pm

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"

dek zorro

2005-03-28, 3:55 am

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

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

Sponsored Links