Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Problem with AddNew method while adding record

577 views
Skip to first unread message

Jack

unread,
Feb 18, 2005, 1:23:01 PM2/18/05
to
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

CODE:

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

<P> </P>
<%

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

set CN=server.createobject("ADODB.Connection")
CN.Open myDSN
'pRS stands for recordset corresponding to the current people detail
set pRS=server.createobject("ADODB.Recordset")
pRS.ActiveConnection = CN
'Define local variables to hold values written on form

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

pRS.Open "People", myDSN,,adLockOptimistic,adCmdTable
pRS.AddNew
pRS("Name") = l_Name
pRS("ColorPreference") = l_ColorPreference
pRS("Age") = l_Age
pRS("IsChecked") = l_IsChecked
pRS.Update

'Issue a statement stating that the update has been successful
Response.Write("Your record has been added to the database")
%>
</BODY>
</HTML>

Steven Burn

unread,
Feb 18, 2005, 1:31:13 PM2/18/05
to
> pRS.Open "People", myDSN,,adLockOptimistic,adCmdTable

This is invalid........... it should be along the lines of;

pRS.Open "Select People From <table_name>", myDSN,,
adLockOptimistic,adCmdTable

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"Jack" <Ja...@discussions.microsoft.com> wrote in message
news:AA0CAF3D-4599-4001...@microsoft.com...

Jack

unread,
Feb 18, 2005, 1:51:06 PM2/18/05
to
Thanks for your reply Steven. However, People is the name of the table where
I am trying to add new record. Not very clear why we should have a select
statement when we are trying to add new record to the table.
Regards

Bob Barrows [MVP]

unread,
Feb 18, 2005, 2:48:33 PM2/18/05
to
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=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

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.


Jack

unread,
Feb 18, 2005, 5:13:02 PM2/18/05
to
Thanks Bob for the generous help. I appreciate it. With the concept I got
from your code I could handle the problem pretty easy. As per your advise, I
added the
OLEDB Provider and used insert statement to build and execute the sql
statement.
Regards.

Michael D. Kersey

unread,
Feb 18, 2005, 8:00:35 PM2/18/05
to
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 as follows:
> pRS.Open "People", myDSN,,adLockOptimistic,adCmdTable
The above line should be
pRS.Open "People", CN,adOpenKeyset,adLockOptimistic,adCmdTable
^^^^^^^^^^^^^^^
The calling sequence for the RecordSet Open() method is:
recordset.Open Source, ActiveConnection, CursorType, LockType, Options

The failing code inadvertently
- substitutes a string (myDSN) for a connection object(CN) and
- omits the necessary CursorType of adOpenKeyset.

0 new messages