rewinding Records

[GIGN]

...
Set Records = Conn.Execute(someQuery)
...

What is the call to send Records back to start of file?

[bb]

 

[ToddWW]

You mean execute some SQL and then undo it ??

That would be in transactions.

Conn.BeginTrans

Conn.Execute someSQL
Conn.Execute someOtherSQL
Conn.Execute someOtherOtherSQL

Then, you issue one of the following.

Conn.CommitTrans

OR

Conn.RollbackTrans

It's usually used in something like this.

IF Conn.Errors.Count = 0 THEN
  Conn.CommitTrans
ELSE
  Conn.RollbackTrans
END IF

ToddWW

 

[GIGN]

So there is no way I can reuse the same record set, without re querying?

You see in different parts of my page I need to count thru them and get stuff out. I figured "Records" to be like an array the respect that once set it could be re-used.

I will have to make an array then.

[bb]

 

[ToddWW]

Oh, that's what you meant by rewinding.

No you can move freely through a static recordset. But it needs to be of a specific type. Here's how I create static, disconnected free moving recordsets.

<%
dim objRst
dim strProvider
dim strQuery

strProvider =

Your conn string or DSN

set objRst = Server.CreateObject("ADODB.Recordset")
objRst.CursorLocation = 3
objRst.CursorType = 3
strQuery = "SELECT * FROM sometable"
objRst.Open strQuery, strProvider
set objRst.ActiveConnection = Nothing
%>

Now, you can move freely through that recordset like this.

<%
WHILE NOT objRst.EOF
  Response.Write objRst(&quot;somefield&quot;)
  objRst.MoveNext
WEND

objRst.MoveFirst ' Moves the Pointer back to the top
objRst.MoveLast ' Moves the Pointer to the end
objRst.Find &quot;somefield = 'somevalue'&quot; ' Finds a record
objRst.Filter = &quot;somefield = 'somevalue'&quot; ' Filters the set
WHILE NOT objRst.EOF
  Response.Write objRst(&quot;somefield&quot;) ' Loops filtered set
  objRst.MoveNext
WEND
objRst.Filter = 0 ' Removes the filter
%>

You can do all that to the same, static recordset object. No need to requery. Read this great FAQ written by another member regarding recordset types, etc..

faq333-618

ToddWW

 

[GIGN]

I may have to use a Recordset - despite their overheads.

Cheers,
[bb]

 

[ToddWW]

For displaying data to the browser, there's no other way. You must populate a recordset to do that.

For adding, updating and deleting, I use the following syntax.

<%
dim Conn
dim strQuery
dim strProvider

strProvider = your conn string or dsn

strQuery = &quot;INSERT INTO table (field1,field2) VALUES (value1,value2)&quot;
Conn.Open strProvider
Conn.Execute strQuery
Conn.Close
set Conn = Nothing
%>

Set your strQuery differently for updating and deleting.

<%
strQuery = &quot;UPDATE table SET field1 = value1, field2 = value2 WHERE somefield = somevalue&quot;

strQuery = &quot;DELETE FROM table WHERE somefield = somevalue&quot;
%>

Now I do believe that you can create a server side, updateable recordset and have ASP make changes to that and then persist those changes back to the DB. But I think that does require the most resources. I like to use disconnected recordsets to display data, then post data back to ASP to do the updating, inserting and deleting.

ToddWW

 

[GIGN]

Records.MoveFirst

;-)