AddNew record error

[Ray at]

Don't create a recordset. Just insert your data:

sSQL = "INSERT INTO TheTable [Firstname]='" & request.form("firstname") &
"',[Lastname]='" & Request.form("lastName") & "'"
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open "DSN=mydsn"
oADO.Execute sSQL
oADO.Close
Set oADO = Nothing

After you have that working, drop the usage of a DSN and change the code to
oADO.Open YourConnectionString
[url]www.connectionstrings.com[/url]

Ray at home

--
Will trade ASP help for SQL Server help


"John Davis" <jrefactor@hotmail.com> wrote in message
news:OzY5xR9UDHA.1744@TK2MSFTNGP12.phx.gbl...

> I wrote a ASP page to add a new record using ADO to SQL Server, but it has
> the following error. If I return the records in the database, it is

working

> fine. Any ideas?? Please advice!!
>
> <%
> Dim rs
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open "Member", "DSN=mydsn"
> rs.AddNew
> rs.Fields("FirstName") = Request.Form("firstName")
> rs.Fields("LastName") = Request.Form("lastName")
> rs.Update
> rs.Close
> %>
>
>
> Error Type:
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating. This may be a limitation of

the

> provider, or of the selected locktype.
> /iishelp/iis/quiz/addmember.asp, line 13
>
>

[Ray at]

Don't create a recordset. Just insert your data:

sSQL = "INSERT INTO TheTable [Firstname]='" & request.form("firstname") &
"',[Lastname]='" & Request.form("lastName") & "'"
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open "DSN=mydsn"
oADO.Execute sSQL
oADO.Close
Set oADO = Nothing

After you have that working, drop the usage of a DSN and change the code to
oADO.Open YourConnectionString
[url]www.connectionstrings.com[/url]

Ray at home

--
Will trade ASP help for SQL Server help


"John Davis" <jrefactor@hotmail.com> wrote in message
news:OzY5xR9UDHA.1744@TK2MSFTNGP12.phx.gbl...

> I wrote a ASP page to add a new record using ADO to SQL Server, but it has
> the following error. If I return the records in the database, it is

working

> fine. Any ideas?? Please advice!!
>
> <%
> Dim rs
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open "Member", "DSN=mydsn"
> rs.AddNew
> rs.Fields("FirstName") = Request.Form("firstName")
> rs.Fields("LastName") = Request.Form("lastName")
> rs.Update
> rs.Close
> %>
>
>
> Error Type:
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating. This may be a limitation of

the

> provider, or of the selected locktype.
> /iishelp/iis/quiz/addmember.asp, line 13
>
>

[Bob Barrows]

John Davis wrote:

> I wrote a ASP page to add a new record using ADO to SQL Server, but
> it has the following error. If I return the records in the database,
> it is working fine. Any ideas?? Please advice!!
>
> <%
> Dim rs
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open "Member", "DSN=mydsn"
> rs.AddNew
> rs.Fields("FirstName") = Request.Form("firstName")
> rs.Fields("LastName") = Request.Form("lastName")
> rs.Update
> rs.Close
> %>
>
>
> Error Type:
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating. This may be a limitation
> of the provider, or of the selected locktype.
> /iishelp/iis/quiz/addmember.asp, line 13

You've probably opened a read-only recordset.
1. You should not be using a recordset to add records anyways. You should be
doing all data modifications via SQL statements, preferably SQL statements
in stored procedures.
2. You should not be using ODBC: there is a perfectly good OLEDB provider
for SQL Server that you can use. See [url]www.connectionstrings.com[/url] for help in
building a connection string using the SQLOLEDB provider.

Try this. Run this script on your SQL Server using Query Analyzer:

create procedure AddMember (
@fname varchar(50),
@lname varchar(50)
) as
insert into Member (FirstName,LastName)
values(@fname,@lname)
go

Then in asp:

<%
dim cn, sFirst, sLast
sFirst = Request.Form("firstName")
sLast = Request.Form("lastName")
if len(sFirst) > 0 and len(sLast) > 0 then
set cn = server.createobject("adodb.connection")
cn.AddMember sFirst, sLast
else
response.write "No data was submitted"
end if
%>

HTH,
Bob Barrows

[Bob Barrows]

John Davis wrote:

> I wrote a ASP page to add a new record using ADO to SQL Server, but
> it has the following error. If I return the records in the database,
> it is working fine. Any ideas?? Please advice!!
>
> <%
> Dim rs
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open "Member", "DSN=mydsn"
> rs.AddNew
> rs.Fields("FirstName") = Request.Form("firstName")
> rs.Fields("LastName") = Request.Form("lastName")
> rs.Update
> rs.Close
> %>
>
>
> Error Type:
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating. This may be a limitation
> of the provider, or of the selected locktype.
> /iishelp/iis/quiz/addmember.asp, line 13

You've probably opened a read-only recordset.
1. You should not be using a recordset to add records anyways. You should be
doing all data modifications via SQL statements, preferably SQL statements
in stored procedures.
2. You should not be using ODBC: there is a perfectly good OLEDB provider
for SQL Server that you can use. See [url]www.connectionstrings.com[/url] for help in
building a connection string using the SQLOLEDB provider.

Try this. Run this script on your SQL Server using Query Analyzer:

create procedure AddMember (
@fname varchar(50),
@lname varchar(50)
) as
insert into Member (FirstName,LastName)
values(@fname,@lname)
go

Then in asp:

<%
dim cn, sFirst, sLast
sFirst = Request.Form("firstName")
sLast = Request.Form("lastName")
if len(sFirst) > 0 and len(sLast) > 0 then
set cn = server.createobject("adodb.connection")
cn.AddMember sFirst, sLast
else
response.write "No data was submitted"
end if
%>

HTH,
Bob Barrows

[Bob Barrows]

Bob Barrows wrote:

>
> <%
> dim cn, sFirst, sLast
> sFirst = Request.Form("firstName")
> sLast = Request.Form("lastName")
> if len(sFirst) > 0 and len(sLast) > 0 then
> set cn = server.createobject("adodb.connection")
> cn.AddMember sFirst, sLast

cn.close
set cn=nothing

> else
> response.write "No data was submitted"
> end if
> %>
>
> HTH,
> Bob Barrows

I would also like to edit my post and add the lines to close and destroy the
connection object ... :]

Bob Barrows

[Bob Barrows]

Bob Barrows wrote:

>
> <%
> dim cn, sFirst, sLast
> sFirst = Request.Form("firstName")
> sLast = Request.Form("lastName")
> if len(sFirst) > 0 and len(sLast) > 0 then
> set cn = server.createobject("adodb.connection")
> cn.AddMember sFirst, sLast

cn.close
set cn=nothing

> else
> response.write "No data was submitted"
> end if
> %>
>
> HTH,
> Bob Barrows

I would also like to edit my post and add the lines to close and destroy the
connection object ... :]

Bob Barrows

[Bob Barrows]

Bob Barrows wrote:

> Bob Barrows wrote:

>>
>> <%
>> dim cn, sFirst, sLast
>> sFirst = Request.Form("firstName")
>> sLast = Request.Form("lastName")
>> if len(sFirst) > 0 and len(sLast) > 0 then
>> set cn = server.createobject("adodb.connection")

cn.open "provider=sqloledb;data source=servername;" & _
"user name=xxxx;password=xxxx"

>> cn.AddMember sFirst, sLast

> cn.close
> set cn=nothing

>> else
>> response.write "No data was submitted"
>> end if
>> %>
>>
>> HTH,
>> Bob Barrows

>
> I would also like to edit my post and add the lines to close and
> destroy the connection object ... :]
>
> Bob Barrows

Oh! And of course it would help to add the line to open the connection as
well ... duhh!

[Bob Barrows]

Bob Barrows wrote:

> Bob Barrows wrote:

>>
>> <%
>> dim cn, sFirst, sLast
>> sFirst = Request.Form("firstName")
>> sLast = Request.Form("lastName")
>> if len(sFirst) > 0 and len(sLast) > 0 then
>> set cn = server.createobject("adodb.connection")

cn.open "provider=sqloledb;data source=servername;" & _
"user name=xxxx;password=xxxx"

>> cn.AddMember sFirst, sLast

> cn.close
> set cn=nothing

>> else
>> response.write "No data was submitted"
>> end if
>> %>
>>
>> HTH,
>> Bob Barrows

>
> I would also like to edit my post and add the lines to close and
> destroy the connection object ... :]
>
> Bob Barrows

Oh! And of course it would help to add the line to open the connection as
well ... duhh!

[Aaron Bertrand [MVP]]

> insert into thetable ([firstname],[lastname]) values ('" &

> request.form("lastname") & "','" & request.form("lastname") & "')"

You should also be replacing any single instances of ' to avoid errors or,
worse, SQL injection.

[Aaron Bertrand [MVP]]

> insert into thetable ([firstname],[lastname]) values ('" &

> request.form("lastname") & "','" & request.form("lastname") & "')"

You should also be replacing any single instances of ' to avoid errors or,
worse, SQL injection.

[Ray at]

You are correct, although my sites, I'd never pull right from request.form
right into a sql query anyway. John will post back in a few days asking how
to deal with thing likes "O'Brien" and we'll cover this then. ;]

Ray at home
--
Will trade ASP help for SQL Server help


"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:umMwbO$UDHA.2156@TK2MSFTNGP11.phx.gbl...

> > insert into thetable ([firstname],[lastname]) values ('" &
> > request.form("lastname") & "','" & request.form("lastname") & "')"

>
> You should also be replacing any single instances of ' to avoid errors or,
> worse, SQL injection.
>
>

[Ray at]

You are correct, although my sites, I'd never pull right from request.form
right into a sql query anyway. John will post back in a few days asking how
to deal with thing likes "O'Brien" and we'll cover this then. ;]

Ray at home
--
Will trade ASP help for SQL Server help


"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:umMwbO$UDHA.2156@TK2MSFTNGP11.phx.gbl...

> > insert into thetable ([firstname],[lastname]) values ('" &
> > request.form("lastname") & "','" & request.form("lastname") & "')"

>
> You should also be replacing any single instances of ' to avoid errors or,
> worse, SQL injection.
>
>

[¦a²y¤H]

Hi all

Are u using Access DB with NT/2000/xp?
if so , please check your access DB, have u got WR right to Iuser?

KR
Perkin

"John Davis" <jrefactor@hotmail.com> ¼¶¼g©ó¶l¥ó·s»D
:OzY5xR9UDHA.1744@TK2MSFTNGP12.phx.gbl...

> I wrote a ASP page to add a new record using ADO to SQL Server, but it has
> the following error. If I return the records in the database, it is

working

> fine. Any ideas?? Please advice!!
>
> <%
> Dim rs
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open "Member", "DSN=mydsn"
> rs.AddNew
> rs.Fields("FirstName") = Request.Form("firstName")
> rs.Fields("LastName") = Request.Form("lastName")
> rs.Update
> rs.Close
> %>
>
>
> Error Type:
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating. This may be a limitation of

the

> provider, or of the selected locktype.
> /iishelp/iis/quiz/addmember.asp, line 13
>
>

[¦a²y¤H]

Hi all

Are u using Access DB with NT/2000/xp?
if so , please check your access DB, have u got WR right to Iuser?

KR
Perkin

"John Davis" <jrefactor@hotmail.com> ¼¶¼g©ó¶l¥ó·s»D
:OzY5xR9UDHA.1744@TK2MSFTNGP12.phx.gbl...

> I wrote a ASP page to add a new record using ADO to SQL Server, but it has
> the following error. If I return the records in the database, it is

working

> fine. Any ideas?? Please advice!!
>
> <%
> Dim rs
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.Open "Member", "DSN=mydsn"
> rs.AddNew
> rs.Fields("FirstName") = Request.Form("firstName")
> rs.Fields("LastName") = Request.Form("lastName")
> rs.Update
> rs.Close
> %>
>
>
> Error Type:
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating. This may be a limitation of

the

> provider, or of the selected locktype.
> /iishelp/iis/quiz/addmember.asp, line 13
>
>

[Daniel Bush]

On Sun, 27 Jul 2003 00:12:52 -0400, "Ray at <%=sLocation%>"
<myfirstname at lane34 dot com> wrote:

>You are correct, although my sites, I'd never pull right from request.form
>right into a sql query anyway. John will post back in a few days asking how
>to deal with thing likes "O'Brien" and we'll cover this then. ;]
>
>Ray at home

Not to be churlish or anything, but I suggest that it is not
appropriate to use

insert into thetable ([firstname],[lastname]) values ('" &
request.form("lastname") & "','" & request.form("lastname") & "')"

in *any* case. One should always use the ADO Command object and
placeholders, as follows:

cmd.commandtext = "insert into thetable ([firstname],[lastname])
values (?,?)"
cmd.execute lngRecs,array(request.form("firstname"), _
request.form("lastname"))

personally, I prefer this to other methods like

strFirst=replace(request.form("firstname"),"'","'' ")

which does address the quote problem but still allows user input into
the SQL String - a security No-No. IMO, using the placeholder method
is easier to read and safer. One never wants to be in the situation
where the user can type input directly into the SQL server which is
what happens when one uses syntax like the above example.

My 2 cents,

Dan Bush
[email]me@R3MOV3.danbush.com[/email]

[Daniel Bush]

On Sun, 27 Jul 2003 00:12:52 -0400, "Ray at <%=sLocation%>"
<myfirstname at lane34 dot com> wrote:

>You are correct, although my sites, I'd never pull right from request.form
>right into a sql query anyway. John will post back in a few days asking how
>to deal with thing likes "O'Brien" and we'll cover this then. ;]
>
>Ray at home

Not to be churlish or anything, but I suggest that it is not
appropriate to use

insert into thetable ([firstname],[lastname]) values ('" &
request.form("lastname") & "','" & request.form("lastname") & "')"

in *any* case. One should always use the ADO Command object and
placeholders, as follows:

cmd.commandtext = "insert into thetable ([firstname],[lastname])
values (?,?)"
cmd.execute lngRecs,array(request.form("firstname"), _
request.form("lastname"))

personally, I prefer this to other methods like

strFirst=replace(request.form("firstname"),"'","'' ")

which does address the quote problem but still allows user input into
the SQL String - a security No-No. IMO, using the placeholder method
is easier to read and safer. One never wants to be in the situation
where the user can type input directly into the SQL server which is
what happens when one uses syntax like the above example.

My 2 cents,

Dan Bush
[email]me@R3MOV3.danbush.com[/email]

[Aaron Bertrand - MVP]

> insert into thetable ([firstname],[lastname]) values ('" &

> request.form("lastname") & "','" & request.form("lastname") & "')"
>
> in *any* case. One should always use the ADO Command object and
> placeholders, as follows:
>
> cmd.commandtext = "insert into thetable ([firstname],[lastname])
> values (?,?)"
> cmd.execute lngRecs,array(request.form("firstname"), _
> request.form("lastname"))

Huh? What's the difference?

In either case, the user can still put something like this into firstname:

'foo','bar'); TRUNCATE TABLE thetable; --

cmd.execute will still happily put that into commandtext and execute it (and
in this case, it won't matter what the user puts into lastname, because it
will be commented out). At least with the replace of single quotes, the
above input will be problematic and will not cause the TRUNCATE to be
executed (instead, a syntax error will occur).

My point being, as long as the user can enter anything, no matter what
method you use, you will be vulnerable to injection attacks. My preference
over both of these methods is to use a stored procedure or stored query...
then you can control the datatypes of incoming parameters *and* have greater
control over how the SQL statement is built.

[Aaron Bertrand - MVP]

> insert into thetable ([firstname],[lastname]) values ('" &

> request.form("lastname") & "','" & request.form("lastname") & "')"
>
> in *any* case. One should always use the ADO Command object and
> placeholders, as follows:
>
> cmd.commandtext = "insert into thetable ([firstname],[lastname])
> values (?,?)"
> cmd.execute lngRecs,array(request.form("firstname"), _
> request.form("lastname"))

Huh? What's the difference?

In either case, the user can still put something like this into firstname:

'foo','bar'); TRUNCATE TABLE thetable; --

cmd.execute will still happily put that into commandtext and execute it (and
in this case, it won't matter what the user puts into lastname, because it
will be commented out). At least with the replace of single quotes, the
above input will be problematic and will not cause the TRUNCATE to be
executed (instead, a syntax error will occur).

My point being, as long as the user can enter anything, no matter what
method you use, you will be vulnerable to injection attacks. My preference
over both of these methods is to use a stored procedure or stored query...
then you can control the datatypes of incoming parameters *and* have greater
control over how the SQL statement is built.

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> insert into thetable ([firstname],[lastname]) values ('" &
>> request.form("lastname") & "','" & request.form("lastname") & "')"
>>
>> in *any* case. One should always use the ADO Command object and
>> placeholders, as follows:
>>
>> cmd.commandtext = "insert into thetable ([firstname],[lastname])
>> values (?,?)"
>> cmd.execute lngRecs,array(request.form("firstname"), _
>> request.form("lastname"))

>
> Huh? What's the difference?
>
> In either case, the user can still put something like this into
> firstname:
>
> 'foo','bar'); TRUNCATE TABLE thetable; --
>

I don't think that's quite right Aaron. We are now passing parameters, not
concatenating. The truncate statement would appear inside the values block
and would cause at least a syntax error - although, I suppose there's a
chance that the string would get inserted into the column if it's not a
numeric column.

I just tried it - the string does get inserted as-is. See for yourself:

CREATE TABLE dbo.InjectTest
(
FirstName varchar(80) NULL,
LastName varchar(80) NULL
)
go
insert injecttest
select 'foo','bar'
union all
select 'foo1','bar1'
union all
select 'foo2','bar2'


<%
option explicit
dim cn, sSQL,cmd, rs

set cn=server.CreateObject("adodb.connection")

sSQL="insert into InjectTest values(?,?)"
cn.open "provider=sqloledb;data source=censored;" & _
"user id = censored; password=censored;initial catalog=test"

set cmd=Server.CreateObject("ADODB.Command")
cmd.CommandText=sSQL
Set cmd.ActiveConnection = cn
cmd.Execute , _
array("'foo','bar'); TRUNCATE TABLE thetable; --","any"), _
adCmdText + adExecuteNoRecords
set rs=server.CreateObject("adodb.recordset")
set rs = cn.Execute("select * from injecttest",,adCmdText)
Response.Write rs.getstring(,," | ","<BR>")
rs.Close
set rs=nothing
cn.close
set cn=nothing
%>

Bob

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> insert into thetable ([firstname],[lastname]) values ('" &
>> request.form("lastname") & "','" & request.form("lastname") & "')"
>>
>> in *any* case. One should always use the ADO Command object and
>> placeholders, as follows:
>>
>> cmd.commandtext = "insert into thetable ([firstname],[lastname])
>> values (?,?)"
>> cmd.execute lngRecs,array(request.form("firstname"), _
>> request.form("lastname"))

>
> Huh? What's the difference?
>
> In either case, the user can still put something like this into
> firstname:
>
> 'foo','bar'); TRUNCATE TABLE thetable; --
>

I don't think that's quite right Aaron. We are now passing parameters, not
concatenating. The truncate statement would appear inside the values block
and would cause at least a syntax error - although, I suppose there's a
chance that the string would get inserted into the column if it's not a
numeric column.

I just tried it - the string does get inserted as-is. See for yourself:

CREATE TABLE dbo.InjectTest
(
FirstName varchar(80) NULL,
LastName varchar(80) NULL
)
go
insert injecttest
select 'foo','bar'
union all
select 'foo1','bar1'
union all
select 'foo2','bar2'


<%
option explicit
dim cn, sSQL,cmd, rs

set cn=server.CreateObject("adodb.connection")

sSQL="insert into InjectTest values(?,?)"
cn.open "provider=sqloledb;data source=censored;" & _
"user id = censored; password=censored;initial catalog=test"

set cmd=Server.CreateObject("ADODB.Command")
cmd.CommandText=sSQL
Set cmd.ActiveConnection = cn
cmd.Execute , _
array("'foo','bar'); TRUNCATE TABLE thetable; --","any"), _
adCmdText + adExecuteNoRecords
set rs=server.CreateObject("adodb.recordset")
set rs = cn.Execute("select * from injecttest",,adCmdText)
Response.Write rs.getstring(,," | ","<BR>")
rs.Close
set rs=nothing
cn.close
set cn=nothing
%>

Bob