AddNew with ADO Recordset doesn't seem to work

AddNew with ADO Recordset doesn't seem to work

Post by Dylan Evan » Wed, 28 Jun 2000 04:00:00



Hi,
Can Anyone help me?  I seem to have been going round in Circles for days!!

I'm Trying to use an ADO Recordset with no Recordsource to Hold Data in an
ASP page (Which will later hold the data for the session).  Specifically,
this data is comming from an Index Server Recordset, which I iterate through
and add certain records into the new recordset.  However, When I try to
display the contents of the recordset, all the added data is blank!  Any
Ideas?  The code is below.

Thanks

<html>
<head>

    <TITLE>Search Form</TITLE>

</head>
<body>
<%
' *********  Subs used to fill Category1 and Category2 Recordsets *********

Set Q = Server.CreateObject("Ixsso.Query")

Q.DefineColumn "Category1 (DBTYPE_WSTR) =
d5cdd505-2e9c-101b-9397-08002b2cf9ae documenttype"

'Q.Columns = "Category1, Category2"
Q.Columns = "Category1, Path"
'Q.SortBy = "Category1[a],Category2[a]"
Q.SortBy = "Category1[a]"
Q.MaxRecords = 300
Set RSCat1 = Q.CreateRecordset("nonsequential")

Set rst = Server.CreateObject("ADODB.Recordset")
rst.fields.Append "Category1", adChar, 100
rst.fields.refresh
rst.Open

RSCat1.MoveFirst
Cat1 = RSCat1("Category1").value
RSCat1.MoveNext

rst.AddNew "Category1",Cstr(Cat1)
rst.Fields("Category1").Value = Cstr(Cat1)
rst.update
%> **<%response.write(Cat1)%>**<BR><%
%>**<% response.write(rst.Fields("Category1").Value) %>**<BR><%

do until RSCat1.Eof
 if Cat1 = RSCat1("Category1").value then
  RSCat1.MoveNext
 else
  if not RSCat1("Category1").value = "" then
  Cat1 = RSCat1("Category1").value
  %> **<%response.write(Cat1)%>**<BR><%
  rst.AddNew
  rst.Fields("Category1").Value = Cat1
  rst.update
 %>**<% response.write(rst.Fields("Category1").Value) %>**<BR><%
  end if
  RSCat1.MoveNext
 end if
loop

%><BR><BR><BR><%
rst.MoveFirst
do until rst.Eof
 if rst.Fields("Category1").Value = "" then
 %>Blank<BR><%
 else
 %>**<% response.write(rst.Fields("Category1").Value) %>**<BR><%
 end if
 rst.MoveNext
loop
rst.Close

%>
</body>

 
 
 

AddNew with ADO Recordset doesn't seem to work

Post by Stephen How » Wed, 28 Jun 2000 04:00:00



Quote:> Hi,
> Can Anyone help me?  I seem to have been going round in Circles for days!!

You look like you have a forward read-only recordset.

For lines below

Set rst = Server.CreateObject("ADODB.Recordset")
rst.fields.Append "Category1", adChar, 100
rst.fields.refresh
rst.Open

don't you want

Set rst = Server.CreateObject("ADODB.Recordset")
rst.LockType = adLockUnspecified                 'or possibly
adLockOptimistic or adLockBatchOptimistic
'rst.CursorType =                                                ' you might
want to set this as well
rst.fields.Append "Category1", adChar, 100
rst.fields.refresh
rst.Open

Stephen Howe

 
 
 

AddNew with ADO Recordset doesn't seem to work

Post by Dylan Evan » Thu, 29 Jun 2000 04:00:00


Hi,

Thanks for responding, I changed the cursor type to dynamic, but when I
tried to change the LockType I and got an error:-

******
ADODB.Recordset error '800a0bb9'

The application is using arguments that are of the wrong type, are out of
acceptable range, or are in conflict with one another.
******

Possibly because the recordsource isn't set??. Or Connection Object and
Provider.

I also checked that the recordset supports the addnew method using

*******
if rst.supports(adAddNew) then
%>Supports AddNew<%
end if
*******

Which the recordset does support, although it still doesn't update properly!
The recordset contains the correct amount of records, but they're all blank
Any other Ideas? Anything gratefully received!




> > Hi,
> > Can Anyone help me?  I seem to have been going round in Circles for
days!!

> You look like you have a forward read-only recordset.

> For lines below

> Set rst = Server.CreateObject("ADODB.Recordset")
> rst.fields.Append "Category1", adChar, 100
> rst.fields.refresh
> rst.Open

> don't you want

> Set rst = Server.CreateObject("ADODB.Recordset")
> rst.LockType = adLockUnspecified                 'or possibly
> adLockOptimistic or adLockBatchOptimistic
> 'rst.CursorType =                                                ' you
might
> want to set this as well
> rst.fields.Append "Category1", adChar, 100
> rst.fields.refresh
> rst.Open

> Stephen Howe

 
 
 

AddNew with ADO Recordset doesn't seem to work

Post by Dave Bauman » Thu, 29 Jun 2000 04:00:00


Have you included adovbs.inc? If not, VBscript has no clue what
AddNew or adLockOptimistic are.

<!--#include file="adovbs.inc"-->

I found this in the inetserver.asp.db thread.  Give thanks to Kris Eiben if
this helps you out.  Look up "adovbs.inc" on the MSDN site or Technet for
more info.

Dave Baumann


> Hi,

> Thanks for responding, I changed the cursor type to dynamic, but when I
> tried to change the LockType I and got an error:-

> ******
> ADODB.Recordset error '800a0bb9'

> The application is using arguments that are of the wrong type, are out of
> acceptable range, or are in conflict with one another.
> ******

> Possibly because the recordsource isn't set??. Or Connection Object and
> Provider.

> I also checked that the recordset supports the addnew method using

> *******
> if rst.supports(adAddNew) then
> %>Supports AddNew<%
> end if
> *******

> Which the recordset does support, although it still doesn't update
properly!
> The recordset contains the correct amount of records, but they're all
blank
> Any other Ideas? Anything gratefully received!





> > > Hi,
> > > Can Anyone help me?  I seem to have been going round in Circles for
> days!!

> > You look like you have a forward read-only recordset.

> > For lines below

> > Set rst = Server.CreateObject("ADODB.Recordset")
> > rst.fields.Append "Category1", adChar, 100
> > rst.fields.refresh
> > rst.Open

> > don't you want

> > Set rst = Server.CreateObject("ADODB.Recordset")
> > rst.LockType = adLockUnspecified                 'or possibly
> > adLockOptimistic or adLockBatchOptimistic
> > 'rst.CursorType =                                                ' you
> might
> > want to set this as well
> > rst.fields.Append "Category1", adChar, 100
> > rst.fields.refresh
> > rst.Open

> > Stephen Howe

 
 
 

AddNew with ADO Recordset doesn't seem to work

Post by Stephen How » Sat, 01 Jul 2000 04:00:00



Quote:> Hi,

> Thanks for responding, I changed the cursor type to dynamic, but when I
> tried to change the LockType I and got an error:-

> ******
> ADODB.Recordset error '800a0bb9'

You have not posted your code. You should do that every time if the code is
small. It maybe that someone spots some small glitch on your part. Otherwise
you could waste hours (and everyone else) trying to guess the problem. You
don't have to post all your code. Just the area that is giving you a
problem.

I would specifically experiment with setting CursorTypes, LockTypes _before_
you open your recordset. If you look at Microsoft's docs on LockTypes, some
types of locks might give errors  (like adLockPessimistic may cause errors)

I would be trying (in the order I show):

CursorType: adOpenStatic, adOpenKeyset, adOpenDynamic
LockType: adLockUnspecified, adLockOptimistic,adLockPessimistic

Stephen

 
 
 

AddNew with ADO Recordset doesn't seem to work

Post by Dylan Evan » Tue, 04 Jul 2000 04:00:00


Hi Stephen,

Sorry, in fact more etiquette missed by me I'm afraid.  Someone wrote a post
wich fixed the problem I was having, and I emailed my reply.  The fix was
that I hadn't included adovbs.inc, and the addnew method doesn't work
without it!!.
Thanks for your help.




> > Hi,

> > Thanks for responding, I changed the cursor type to dynamic, but when I
> > tried to change the LockType I and got an error:-

> > ******
> > ADODB.Recordset error '800a0bb9'

> You have not posted your code. You should do that every time if the code
is
> small. It maybe that someone spots some small glitch on your part.
Otherwise
> you could waste hours (and everyone else) trying to guess the problem. You
> don't have to post all your code. Just the area that is giving you a
> problem.

> I would specifically experiment with setting CursorTypes, LockTypes
_before_
> you open your recordset. If you look at Microsoft's docs on LockTypes,
some
> types of locks might give errors  (like adLockPessimistic may cause
errors)

> I would be trying (in the order I show):

> CursorType: adOpenStatic, adOpenKeyset, adOpenDynamic
> LockType: adLockUnspecified, adLockOptimistic,adLockPessimistic

> Stephen

 
 
 

1. Select @vchMyVar = Null doesn't seem to work in ADO

I have a SQL Server 6.5 sproc that has the following lines:



If I run the sproc from Enterprise Manager it works fine, the variable gets
set to Null.

If I run the sproc through ADO (either through my ASP page or through the
Visual InterDev data environment) the variable does not get set to Null, but
rather ends up with an empty string.

Ideas?

2. Ayuda BDComboBox - Help DBComboBox

3. provider=SQLOLEDB doesn't seem to work with ADO

4. connection to Oracle8, using Dev/2000 tools

5. AddNew works, but then it doesn't (ADO)

6. Help: ADO 2.5 XML works, ADTG doesn't with persisted recordsets

7. variable doesn't seem to work

8. TOP function doesn't seem to work in 6.5

9. My Access SQL Doesn't seem to work

10. Round function doesn't seem to work on floats

11. Urgent - NOT FOR REPLICATION doesn't seem to work