Problem with AddNew method while adding record - microsoft.public.inetserver.asp.db

Message from discussion Problem with AddNew method while adding record

Bob Barrows [MVP]

View profile

More options Feb 18 2005, 2:48 pm

Newsgroups: microsoft.public.inetserver.asp.db

From: "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>

Date: Fri, 18 Feb 2005 14:48:33 -0500

Local: Fri, Feb 18 2005 2:48 pm

Subject: Re: Problem with AddNew method while adding record

  Reply to author
  |
  Forward
  | Print
  | View thread
  | Show original
  | Report this message
  | Find messages by this author
  

Jack wrote:
> Hi,
> I am testing a small asp page where addnew method is applied to add
> record to a table. However, I am getting the following error:

> Unspecified error
> /gwisbrandnewready4/test/REPLY.ASP, line 35

> Line 35 is goes as follows:
> pRS.Open "People", myDSN,,adLockOptimistic,adCmdTable
> Any help is appreciated in advance. Thanks

I'm not sure why you are getting this error. I suspect it is due to the
reserved word you are using for one of your column names (Name). Anyways, in
ASP, it is not recommended that you use a recordset to modify data.
Recordsets should only be used to retrieve read-only data for display
purposes. Your application will be much more scalable if you learn to use
SQL DML (SQL Data Modification Language): INSERT,UPDATE and DELETE
statements. See below:

> myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; "
> myDSN=myDSN & "DBQ=C:\_______GWISBRANDNEWREADY4\test\disabletext.mdb"

The obsolete ODBC driver may not support adCmdTable. You should be using the
OLE DB Provider anyways:

myDSN="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\_______GWISBRANDNEWREADY4\test\disabletext.mdb"

> set CN=server.createobject("ADODB.Connection")
> CN.Open myDSN
<snip>
> Dim l_Name
> Dim l_ColorPreference
> Dim l_Age
> Dim l_IsChecked

> 'Store the input values in the form to the above defined variable

> l_Name = Request.Form("txtName")
> l_ColorPreference = Request.Form("txtColorPreference")
> l_Age = Request.Form("txtAge")
> l_IsChecked = Request.Form("chkConsent")

> 'Using the above variable values update the People table

Dim sSQL, cmd
sSQL = "INSERT INTO People (Name,ColorPreference," & _
"Age,IsChecked) VALUES(?,?,?,?)"

set cmd=createobject("adodb.command")
with cmd
.CommandText=sSQL
.CommandType=adCmdText
Set .ActiveConnection=CN
on error resume next
.Execute(,array(l_Name,l_ColorPreference,l_Age ,l_IsChecked), _
adExecuteNoRecords)
if err<>0 then
Response.Write("Your record has been added to the database")
end if
end with

Better yet would be to use a saved parameter query. See:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYx...

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%...

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx...

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvO...

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to author Forward