Problem with AddNew method while adding record

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 %>

Re: Problem with AddNew method while adding record

> pRS.Open "People", myDSN,,adLockOptimistic,adCmdTable

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

pRS.Open "Select People From ", myDSN,,
adLockOptimistic,adCmdTable

--
Regards

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

Keeping it FREE!

"Jack" wrote in message
news:AA0CAF3D-4599-4001-8630-7CE444E35095@microsoft.com...
> 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 %>
>
>
>
>
>
>
>

Re: Problem with AddNew method while adding record

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

"Steven Burn" wrote:

> > pRS.Open "People", myDSN,,adLockOptimistic,adCmdTable
>
> This is invalid........... it should be along the lines of;
>
> pRS.Open "Select People From ", myDSN,,
> adLockOptimistic,adCmdTable
>
> --
> Regards
>
> Steven Burn
> Ur I.T. Mate Group
> www.it-mate.co.uk
>
> Keeping it FREE!
>
> "Jack" wrote in message
> news:AA0CAF3D-4599-4001-8630-7CE444E35095@microsoft.com...
> > 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 %>
> >
> >
> >
> >
> >
> >
> >

Re: Problem with AddNew method while adding record

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

> 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%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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.

Re: Problem with AddNew method while adding record

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.
"Bob Barrows [MVP]" wrote:

> 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
>
> > 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%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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.
>
>
>

Re: Problem with AddNew method while adding record

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.