ASP HELP -- Disconnected recordset?????

[Paul]

I am in the process of doing a report and in the report, I'm pulling
information from a sql database.

Select * from Sales

In the Sales table, lets say I have the following tables and info:

First_Name Last_Name Dollar_Amount
Tom Johnson 43
Judy Benson 32
Junior Lon 8
Danny Hoff 10


In my asp code,

Do while not rs_query.eof

I want to update the recordset (but NOT update the
records--disconnected recordset? on the server), and find all the
people who's first name starts with "J" and replace the dollar_amount
to say 15.

Loop
Now after all this is done, I want to resort the recordset so now my
final report would show:

First_Name: Last_Name Dollar_Amount
Danny Hoff 10
Junior Lon 15
Judy Benson 15
Tom Johnson 43

Loop

Keep in mind that I just want to modified the record on the client
side only and not on the server side.

Any help will be much appreciated,

Thanks,
Paul

[TurkBear]

On 14 Aug 2003 10:01:18 -0700, [email]doodo08@yahoo.com[/email] (Paul) wrote:

>I am in the process of doing a report and in the report, I'm pulling
>information from a sql database.
>
>Select * from Sales
>
>In the Sales table, lets say I have the following tables and info:
>
>First_Name Last_Name Dollar_Amount
>Tom Johnson 43
>Judy Benson 32
>Junior Lon 8
>Danny Hoff 10
>
>
>In my asp code,
>
>Do while not rs_query.eof
>
> I want to update the recordset (but NOT update the
>records--disconnected recordset? on the server), and find all the
>people who's first name starts with "J" and replace the dollar_amount
>to say 15.
>
>Loop
>Now after all this is done, I want to resort the recordset so now my
>final report would show:
>
>First_Name: Last_Name Dollar_Amount
>Danny Hoff 10
>Junior Lon 15
>Judy Benson 15
>Tom Johnson 43
>
>Loop
>
>Keep in mind that I just want to modified the record on the client
>side only and not on the server side.
>
>Any help will be much appreciated,
>
>Thanks,
>Paul

After retreiving all the data place it in an array and process the array members before displaying them..

Try:
SqlStr = "Select * from sales"
Set rs1 = YourConnObj.Execute(SqlStr)
arrSalesData = rs1.GetRows())

Once in arrSalesData you can close the connection and use the contents...

hth,

[raydan]

....or do this in ASP:
IF LEFT(oRst("First_Name"), 1) = "J" THEN
response.write 15
ELSE
response.write oRst("Dollar_Amount")
END IF

and change Bob's Query:
Select First_Name,Last_Name, Dollar_Amount
FROM Sales
Order By CASE WHEN Left(First_Name) = 'J' THEN 15
ELSE Dollar_Amount END

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:enxTajoYDHA.1784@TK2MSFTNGP09.phx.gbl...

> I started giving you a solution using a disconnected recordset, but then
> realized that was silly. Modify your query to do this (you'll probably

want

> to put this in a stored procedure):
>
> Select First_Name,Last_Name,
> CASE WHEN Left(First_Name) = 'J' THEN 15
> ELSE Dollar_Amount END [Dollar_Amount]
> FROM Sales
> Order By CASE WHEN Left(First_Name) = 'J' THEN 15
> ELSE Dollar_Amount END
>
> HTH,
> Bob Barrows
>
> Paul wrote:

> > I am in the process of doing a report and in the report, I'm pulling
> > information from a sql database.
> >
> > Select * from Sales
> >
> > In the Sales table, lets say I have the following tables and info:
> >
> > First_Name Last_Name Dollar_Amount
> > Tom Johnson 43
> > Judy Benson 32
> > Junior Lon 8
> > Danny Hoff 10
> >
> >
> > In my asp code,
> >
> > Do while not rs_query.eof
> >
> > I want to update the recordset (but NOT update the
> > records--disconnected recordset? on the server), and find all the
> > people who's first name starts with "J" and replace the dollar_amount
> > to say 15.
> >
> > Loop
> > Now after all this is done, I want to resort the recordset so now my
> > final report would show:
> >
> > First_Name: Last_Name Dollar_Amount
> > Danny Hoff 10
> > Junior Lon 15
> > Judy Benson 15
> > Tom Johnson 43
> >
> > Loop
> >
> > Keep in mind that I just want to modified the record on the client
> > side only and not on the server side.
> >
> > Any help will be much appreciated,
> >
> > Thanks,
> > Paul

>
>

[raydan]

We just showed you, all 3 of us.
You have 3 different ways to do what you want.

"Paul" <doodo08@yahoo.com> wrote in message
news:b3b108ea.0308141242.68759d8@posting.google.co m...

> Thanks for responding guys. But aside from this, how do I edit the
> contents of the recordset without making changes to the data on the
> server, then re-display the recordset with the changes?
>
>
>
>
>
>
>
>
> [email]doodo08@yahoo.com[/email] (Paul) wrote in message

news:<b3b108ea.0308140901.6c36d243@posting.google. com>...

> > I am in the process of doing a report and in the report, I'm pulling
> > information from a sql database.
> >
> > Select * from Sales
> >
> > In the Sales table, lets say I have the following tables and info:
> >
> > First_Name Last_Name Dollar_Amount
> > Tom Johnson 43
> > Judy Benson 32
> > Junior Lon 8
> > Danny Hoff 10
> >
> >
> > In my asp code,
> >
> > Do while not rs_query.eof
> >
> > I want to update the recordset (but NOT update the
> > records--disconnected recordset? on the server), and find all the
> > people who's first name starts with "J" and replace the dollar_amount
> > to say 15.
> >
> > Loop
> > Now after all this is done, I want to resort the recordset so now my
> > final report would show:
> >
> > First_Name: Last_Name Dollar_Amount
> > Danny Hoff 10
> > Junior Lon 15
> > Judy Benson 15
> > Tom Johnson 43
> >
> > Loop
> >
> > Keep in mind that I just want to modified the record on the client
> > side only and not on the server side.
> >
> > Any help will be much appreciated,
> >
> > Thanks,
> > Paul

[Paul]

Thanks for responding guys. But aside from this, how do I edit the
contents of the recordset without making changes to the data on the
server, then re-display the recordset with the changes?








[email]doodo08@yahoo.com[/email] (Paul) wrote in message news:<b3b108ea.0308140901.6c36d243@posting.google. com>...

> I am in the process of doing a report and in the report, I'm pulling
> information from a sql database.
>
> Select * from Sales
>
> In the Sales table, lets say I have the following tables and info:
>
> First_Name Last_Name Dollar_Amount
> Tom Johnson 43
> Judy Benson 32
> Junior Lon 8
> Danny Hoff 10
>
>
> In my asp code,
>
> Do while not rs_query.eof
>
> I want to update the recordset (but NOT update the
> records--disconnected recordset? on the server), and find all the
> people who's first name starts with "J" and replace the dollar_amount
> to say 15.
>
> Loop
> Now after all this is done, I want to resort the recordset so now my
> final report would show:
>
> First_Name: Last_Name Dollar_Amount
> Danny Hoff 10
> Junior Lon 15
> Judy Benson 15
> Tom Johnson 43
>
> Loop
>
> Keep in mind that I just want to modified the record on the client
> side only and not on the server side.
>
> Any help will be much appreciated,
>
> Thanks,
> Paul

[Bob Barrows]

Well - you are certainly persistant. We just showed you two ways to
accomplish the task set in your original message without using a
disconnected recordset, but still you persist ... there must be something
you aren't telling us.

OK, here is the code to open a disconnected recordset:

sSQL = "Select * from sales"
set rs=server.createobject("adodb.recordset")
rs.cursorlocation = adUseClient
rs.open sSQL,cn,,adLockBatchOptimistic,adCmdText
Set rs.ActiveConnection = nothing
cn.close
set cn=nothing

The recordset is now open and disconnected. You can now update it and resort
it to your heart's content, without affecting the data in the database. When
you are done with it, don't forget to close it and set it to nothing.

HTH,
Bob Barrows

PS. If you get the "arguments are of the wrong type" error, that means you
haven't defined the ado constants in your page. See here for the best way to
do that: [url]http://www.aspfaq.com/show.asp?id=2112[/url]

Paul wrote:

> Thanks for responding guys. But aside from this, how do I edit the
> contents of the recordset without making changes to the data on the
> server, then re-display the recordset with the changes?
>
>
>
>
>
>
>
>
> [email]doodo08@yahoo.com[/email] (Paul) wrote in message
> news:<b3b108ea.0308140901.6c36d243@posting.google. com>...

>> I am in the process of doing a report and in the report, I'm pulling
>> information from a sql database.
>>
>> Select * from Sales
>>
>> In the Sales table, lets say I have the following tables and info:
>>
>> First_Name Last_Name Dollar_Amount
>> Tom Johnson 43
>> Judy Benson 32
>> Junior Lon 8
>> Danny Hoff 10
>>
>>
>> In my asp code,
>>
>> Do while not rs_query.eof
>>
>> I want to update the recordset (but NOT update the
>> records--disconnected recordset? on the server), and find all the
>> people who's first name starts with "J" and replace the dollar_amount
>> to say 15.
>>
>> Loop
>> Now after all this is done, I want to resort the recordset so now my
>> final report would show:
>>
>> First_Name: Last_Name Dollar_Amount
>> Danny Hoff 10
>> Junior Lon 15
>> Judy Benson 15
>> Tom Johnson 43
>>
>> Loop
>>
>> Keep in mind that I just want to modified the record on the client
>> side only and not on the server side.
>>
>> Any help will be much appreciated,
>>
>> Thanks,
>> Paul

[Paul]

Well, my project is probably more complex than what I had in the
example -- Sorry! But I just need to be able to modified the
recordset that I have query.
I tried you code below but it didn't work . I've included the adovbs
file :
<!--#include virtual="/adovbs.inc"--> (adovbs.inc is store at the
root). Here is my code from the top...

'******************************Code*************** ****************
'************************************************* ****************
<object RUNAT="Server" ID="Conn" PROGID="ADODB.Connection"></object>
<% response.buffer=true %>
<html>
<head>

<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Report</title>
</head>

<body>
<%
Server.ScriptTimeout = 480
Conn.CommandTimeout = 480
%>
<!--#include virtual="reports/manager/connect.asp"-->
<!--#include virtual="/adovbs.inc"-->
<%

'********
Next is my SQL Query
'********

SQL_QUERY= "SELECT "
SQL_QUERY= SQL_QUERY & "oh.customer_file_id as 'Cust ID#', "
etc.....


'*****************
Then I open up the Connetion
'*****************
set rs_query = Conn.Execute(sql_query)
set rs=server.createobject("adodb.recordset")
rs.cursorlocation=aduseclient
rs.open rs_query, cn, adlockbatchoptimistic, adcmdtext
set rs.activeconnection = nothing
cn.close
set cn= nothing


When I run it, it's giving me this error:
Microsoft VBScript runtime error '800a0411'
Name redefined: 'adOpenForwardOnly'
/adovbs.inc, line 14

I included this in my Global.asa
<!-- METADATA TYPE="TypeLib" FILE="C:\Program Files\Common
Files\system\ado\msado15.dll" -->

If i didn't include this, I would get the "arguments are of the wrong
type" error

Any ideas?

Paul







"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:<ejbgRcrYDHA.1872@TK2MSFTNGP12.phx.gbl>...

> Well - you are certainly persistant. We just showed you two ways to
> accomplish the task set in your original message without using a
> disconnected recordset, but still you persist ... there must be something
> you aren't telling us.
>
> OK, here is the code to open a disconnected recordset:
>
> sSQL = "Select * from sales"
> set rs=server.createobject("adodb.recordset")
> rs.cursorlocation = adUseClient
> rs.open sSQL,cn,,adLockBatchOptimistic,adCmdText
> Set rs.ActiveConnection = nothing
> cn.close
> set cn=nothing
>
> The recordset is now open and disconnected. You can now update it and resort
> it to your heart's content, without affecting the data in the database. When
> you are done with it, don't forget to close it and set it to nothing.
>
> HTH,
> Bob Barrows
>
> PS. If you get the "arguments are of the wrong type" error, that means you
> haven't defined the ado constants in your page. See here for the best way to
> do that: [url]http://www.aspfaq.com/show.asp?id=2112[/url]
>
> Paul wrote:

> > Thanks for responding guys. But aside from this, how do I edit the
> > contents of the recordset without making changes to the data on the
> > server, then re-display the recordset with the changes?
> >
> >
> >
> >
> >
> >
> >
> >
> > [email]doodo08@yahoo.com[/email] (Paul) wrote in message
> > news:<b3b108ea.0308140901.6c36d243@posting.google. com>...

> >> I am in the process of doing a report and in the report, I'm pulling
> >> information from a sql database.
> >>
> >> Select * from Sales
> >>
> >> In the Sales table, lets say I have the following tables and info:
> >>
> >> First_Name Last_Name Dollar_Amount
> >> Tom Johnson 43
> >> Judy Benson 32
> >> Junior Lon 8
> >> Danny Hoff 10
> >>
> >>
> >> In my asp code,
> >>
> >> Do while not rs_query.eof
> >>
> >> I want to update the recordset (but NOT update the
> >> records--disconnected recordset? on the server), and find all the
> >> people who's first name starts with "J" and replace the dollar_amount
> >> to say 15.
> >>
> >> Loop
> >> Now after all this is done, I want to resort the recordset so now my
> >> final report would show:
> >>
> >> First_Name: Last_Name Dollar_Amount
> >> Danny Hoff 10
> >> Junior Lon 15
> >> Judy Benson 15
> >> Tom Johnson 43
> >>
> >> Loop
> >>
> >> Keep in mind that I just want to modified the record on the client
> >> side only and not on the server side.
> >>
> >> Any help will be much appreciated,
> >>
> >> Thanks,
> >> Paul

[Bob Barrows]

If you have the ado constants defined by referencing the type library in
Global.ASA, then you do not need to #include the adovbs.inc file. You should
be more specific in referencing the type library. See here for more details:
[url]http://www.aspfaq.com/show.asp?id=2112[/url]

Make sure you use the correct uuid.

More below:

Paul wrote:
<snip>

> '********
> Next is my SQL Query
> '********
>
> SQL_QUERY= "SELECT "
> SQL_QUERY= SQL_QUERY & "oh.customer_file_id as 'Cust ID#', "
> etc.....
>
>
> '*****************
> Then I open up the Connetion
> '*****************
> set rs_query = Conn.Execute(sql_query)

This statement opens a recordset. it does not open a connection. i'm
assuming you've previously opened your connection (the Conn object) prior to
this. Get rid of this line.

<snip>

> rs.open rs_query, cn, adlockbatchoptimistic, adcmdtext

You can't use another recordset (rs_query) as the source argument in the
recordset.open statement. Get rid of the statement that opens rs_query, and
change this statement to:

rs.open sql_query, cn, adlockbatchoptimistic, adcmdtext

HTH,
Bob Barrows