ASP with Access database
am 24.04.2006 10:49:38 von jagdishl
Hi:
I have an asp form which would update the table created in the Access
form.This is the code which I have written but unfortunately the
update is not made to the access table after the submit button is
pressed.
f Request.Form("B1")= "Submit" Then
DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source="C:\Documents and Settings\Jugluck\Desktop\informs.mdb" & ";"
Set objRecordset = Server.CreateObject("ADODB.Recordset")
objRecordset.Open "Alumni", DB_CONNECTIONSTRING, adOpenStatic,
adLockOptimistic, adCmdTable
objRecordset.AddNew
'-- Add records to database from form --
objRecordset.Fields("Firstname") = Request.Form("firstname")
objRecordset.Fields("Lastname") = Request.Form("lastname")
objRecordset.Fields("Homeaddress") = Request.Form("address")
objRecordset.Fields("city") = Request.Form("city")
objRecordset.Fields("state") = Request.Form("state")
objRecordset.Fields("zipcode") = Request.Form("zipcode")
objRecordset.Fields("e-mail") = Request.Form("email")
objRecordset.Fields("web-page") = Request.Form("webpage")
objRecordset.Fields("graduation degree") =
Request.Form("graddegree")
objRecordset.Fields("graduation year") = Request.Form("gradyear")
objRecordset.Fields("department") = Request.Form("department")
objRecordset.Fields("degree advisor") = Request.Form("advisor")
objRecordset.Fields("Professional interest") =
Request.Form("interest")
objRecordset.Fields("company") = Request.Form("company")
objRecordset.Fields("company address") =
Request.Form("compaddress")
objRecordset.Fields("c_city") = Request.Form("pcity")
objRecordset.Fields("c_state") = Request.Form("pstate")
objRecordset.Fields("c_zip code") = Request.Form("pzipcode")
objRecordset.Fields("c_web page") = Request.Form("pwebpage")
strsql = "INSERT INTO Alumni Members
(Firstname,Lastname,Homeaddress,city,state,zipcode,e-mail,we b-page,graduation
degree,graduation year,department,degree advisor,Professional
interest,company,company address,c_city,c_state,c_zip code,c_web page)"
strsql = strSQL & "Values('" & firstname & "'"
strsql = strSQL & "Values('" & lastname & "'"
strsql = strSQL & "Values('" & address & "'"
strsql = strSQL & "Values('" & city & "'"
strsql = strSQL & "Values('" & state & "'"
strsql = strSQL & "Values('" & zipcode & "'"
strsql = strSQL & "Values('" & email & "'"
strsql = strSQL & "Values('" & webpage & "'"
strsql = strSQL & "Values('" & graddegree & "'"
strsql = strSQL & "Values('" & gradyear & "'"
strsql = strSQL & "Values('" & department & "'"
strsql = strSQL & "Values('" & advisor & "'"
strsql = strSQL & "Values('" & interest & "'"
strsql = strSQL & "Values('" & company & "'"
strsql = strSQL & "Values('" & compaddress & "'"
strsql = strSQL & "Values('" & pcity & "'"
strsql = strSQL & "Values('" & pstate & "'"
strsql = strSQL & "Values('" & pzipcode & "'"
strsql = strSQL & ",'" & pwebpage & "')"
with objRecordset
.mode = 3
.Open DB_CONNECTIONSTRING
.execute(strsql)
End with
objRecordset.Update
objRecordset.Close
I have been working for a long time on this code.so any help will be
really appreciated.
Thanks
Jagdish.l
Re: ASP with Access database
am 24.04.2006 12:07:09 von Mike Brind
jagdishl@gmail.com wrote:
> Hi:
> I have an asp form which would update the table created in the Access
> form.This is the code which I have written but unfortunately the
> update is not made to the access table after the submit button is
> pressed.
>
>
> f Request.Form("B1")= "Submit" Then
> DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source="C:\Documents and Settings\Jugluck\Desktop\informs.mdb" & ";"
>
> Set objRecordset = Server.CreateObject("ADODB.Recordset")
> objRecordset.Open "Alumni", DB_CONNECTIONSTRING, adOpenStatic,
> adLockOptimistic, adCmdTable
>
>
> objRecordset.AddNew
>
> '-- Add records to database from form --
>
> objRecordset.Fields("Firstname") = Request.Form("firstname")
> objRecordset.Fields("Lastname") = Request.Form("lastname")
> objRecordset.Fields("Homeaddress") = Request.Form("address")
> objRecordset.Fields("city") = Request.Form("city")
> objRecordset.Fields("state") = Request.Form("state")
> objRecordset.Fields("zipcode") = Request.Form("zipcode")
> objRecordset.Fields("e-mail") = Request.Form("email")
> objRecordset.Fields("web-page") = Request.Form("webpage")
> objRecordset.Fields("graduation degree") =
> Request.Form("graddegree")
> objRecordset.Fields("graduation year") = Request.Form("gradyear")
> objRecordset.Fields("department") = Request.Form("department")
> objRecordset.Fields("degree advisor") = Request.Form("advisor")
> objRecordset.Fields("Professional interest") =
> Request.Form("interest")
> objRecordset.Fields("company") = Request.Form("company")
> objRecordset.Fields("company address") =
> Request.Form("compaddress")
> objRecordset.Fields("c_city") = Request.Form("pcity")
> objRecordset.Fields("c_state") = Request.Form("pstate")
> objRecordset.Fields("c_zip code") = Request.Form("pzipcode")
> objRecordset.Fields("c_web page") = Request.Form("pwebpage")
> strsql = "INSERT INTO Alumni Members
> (Firstname,Lastname,Homeaddress,city,state,zipcode,e-mail,we b-page,graduation
> degree,graduation year,department,degree advisor,Professional
> interest,company,company address,c_city,c_state,c_zip code,c_web page)"
> strsql = strSQL & "Values('" & firstname & "'"
> strsql = strSQL & "Values('" & lastname & "'"
> strsql = strSQL & "Values('" & address & "'"
> strsql = strSQL & "Values('" & city & "'"
> strsql = strSQL & "Values('" & state & "'"
> strsql = strSQL & "Values('" & zipcode & "'"
> strsql = strSQL & "Values('" & email & "'"
> strsql = strSQL & "Values('" & webpage & "'"
> strsql = strSQL & "Values('" & graddegree & "'"
> strsql = strSQL & "Values('" & gradyear & "'"
> strsql = strSQL & "Values('" & department & "'"
> strsql = strSQL & "Values('" & advisor & "'"
> strsql = strSQL & "Values('" & interest & "'"
> strsql = strSQL & "Values('" & company & "'"
> strsql = strSQL & "Values('" & compaddress & "'"
> strsql = strSQL & "Values('" & pcity & "'"
> strsql = strSQL & "Values('" & pstate & "'"
> strsql = strSQL & "Values('" & pzipcode & "'"
> strsql = strSQL & ",'" & pwebpage & "')"
> with objRecordset
> .mode = 3
> .Open DB_CONNECTIONSTRING
> .execute(strsql)
> End with
> objRecordset.Update
> objRecordset.Close
>
>
> I have been working for a long time on this code.so any help will be
> really appreciated.
> Thanks
> Jagdish.l
It's always a good idea to post the error message you get when you have
a problem like this, as it gives a starting point ot people trying to
help. However, from the code you have supplied, there are a number of
obvious errors.
The first is your implementation of the AddNew method of the recordset
object. The syntax is wrong. You have used the update syntax instead,
which changes a current record rather than inserting a new one. In any
event, using a recordset object to create a new record is not generally
recommended.
You have also mingled in some code for an alternative way to insert
records, that is to define a SQL Insert statement and then run that
against the database. On its own, this will fail for a number of
reasons:
1. The actual SQL statement you have built and assigned to strsql is
syntactically incorrect, and is missing the line continuation
character. You will see this if you response.write strsql. A correct
statement should look like this:
strsql = "INSERT INTO Table (field1, field2, field3, ....fieldn) VALUES
(value1, value2, value3,....valuen)
2. Even if the statement was correct, you have then tried to execute
the SQL against a recordset object:
with objRecordset
....
..execute(strsql)
Recordset objects do not have an execute method. This should be run
against the connection object (which you haven't created or opened).
Your use of dynamic sql leaves you open to the possibility of SQL
Injection attacks. With Access, you are advised to use Saved Parameter
Queries for performing database inserts and updates. See here:
http://groups.google.co.uk/group/microsoft.public.inetserver .asp.db/browse_frm/thread/93443d4632f902fd/?hl=en#
--
Mike Brind