Problem with AddNew method while adding record

564 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.

Reply all
Reply to author
Forward
0 new messages