How to get the second row of a recordset?

Here's my SQL Statement (I'm using MS SQL 2000):

SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName
FROM Menu
ORDER BY MenuDate DESC

This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).

Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!

MikeYou want the last row? Is that correct? Use the MoveLast method of the ADO recordset object. This will take you to the second row using a top 2.

eg:
dim Conn = ".... Your connection string ...."

Set dbAPI = Server.CreateObject("ADODB.Connection")
set rs1 = Set rs1 = Server.CreateObject("ADODB.Recordset")

myCmd = "select ........"

dbAPI.Open Conn

set rs1 = dbAPI.Execute myCmd

if rs1.EOF = false then

rs1.MoveLast

myvar1 = rs1("myCol1")
myvar2 = rs1("myCol2")
... and so on

end if

dbAPI.Close

Hope this helps.|||SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM Menu
ORDER BY MenuDate DESC) Menu
ORDER BY Menu.MenuDate

I hope this will solve your problem.|||Thank you both VERY MUCH! The move last would work (can't believe I didn't think of it). I decided to use Rudra's subquery because it was faster (in other words, less typing for me).

Thanks again for solving a problem that was driving me crazy!

How to get source by execution oracle sp using ref cursor

Hi,

I need to get recordset returned by oracle sp in execute sql task to process futher in For Each Loop container and on same lines i want to use oracle sp for extraction data in Data Flow Task. Could anybody suggest if it how we could do it in SSIS?

All suggestions will be highly appreciated.

Thanks,

Lalit

You should be able to do this. Is there a specific problem you are encountering?|||There is no variable type in SSIS that maps to refcursor type in Oracle. I don't think a variable of type object can be used for this either. So I guess you cannot execute Oracle SP using Execute SQL task to get the recordset.

How to get recordset from only last SELECT stmt executed??

How to get recordset from only last SELECT stmt executed?

this is part of my code.. in T-Sql

Create proc some mySp
...
AS
...
set nocount on
if (@.SelUserID is null)
select 0
else if (@.SelUserID = @.userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @.incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @.logText
if (@.@.rowcount != 0)
set @.isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do?ANytime you select values without an assignment operator, they will be returned as part of the rowset. It sounds like you want to check for the existence of one rowset before selecting the second. So how about this:


if exists (select 1
from dms_prsn_trx_log
where @.incNo = ult_incid_no
and prsn_trx_no = 10
and trx_log_txt = @.logText)
begin
select b.
end
else
select a.
end|||Thank you it helps..
So, you means there is no such switch thing on off?
I thought it has...-.-

how to get recordset current position ?

Hi,

I need to know how can we get the recordset current position ?

it's adodb.recordset ...

thanksExactly what do you mean by "current position"? Is there a reason you are using adodb in your ASP.NET application?|||I meant, the location of current "row" of the recordset ...
whenever I execute a movenext, it move to next "row" ...
just want to know the current data is from which "row" ...|||Well, in an ADODB recordset, you could just read the data and from there understand what data you are on. If you mean things like a "record number" then no, there is nothing I can think of (and the concept of a "record number" is really meaningless if it is a connected recordset, since rows can be added and deleted).|||ok, thanks for your answer ... have a nice day ...

How to get Recordset count

I'm currently using SQL 2005 but in previous versions I remember that within Query Analyzer there was a recordset count that displayed in the lower right hand corner. Currently within 2005 I can't find a way to do this without doing it manually which I can't do now since the stored procedure I'm testing has almost 100 recordsets. Any help would be greatly appricated.

Jerry:

That feature is still available; check and see if you are displaying the "status bar" (I think). In the meantime I'll do some more checking about that feature.

Kent

|||

In SSMS, it should be visible in the status bar of the Results window.

Welcome as a new user to SQL Server Jerry. Hopefully, you will be able to get the help you need from these forums, and as your skills progress, please don't forget to drop in occasionally and help others. (You will ALWAYS know something someone else can benefit from...)

It's not a 'recordset' -the output from a SQL query is referred to as a 'resultset'.

|||

My mistake on the 'recordset' comment. I know it's a resultset.

I've been using SQL for a bit now but the result set count still doesn't show up. I have all the bars visible. I see "Ln", "Col", "Ch" & I see the number of rows returned and server i'm running against under the resultset window.

|||

I think that perhaps you are looking to get the message that "n row(s) affected" that may follow an activity. (It sounds like perhaps you are seeing the "n rows" in the status bar.)

To get the "n row(s) affected" message, use this command:

SET NOCOUNT OFF

To stop getting the "n row(s) affected" message, use this command:

SET NOCOUNT ON

|||I thought in previous versions of SQL it actually said how man result sets were coming back. Not the number of rows but the number of result sets. I need 97 result sets to come back with a series of stored procedures that are being executed. I don't want to go through and count every result set.|||

I'm not aware of any facility within T-SQL that reports the number of resultsets returned. One query = one resultset.

I think that may be available with some of the ADO objects, specifically the datareader object.

I supose you could add a counter variable in the procedure, and check each query for @.@.ROWCOUNT, incremented as desired, and then return a statement using that variable.

|||

I don't remember one either.

You could possibly return the data to the grid and then (assuming no errors or print statements) you could get a count from the

(1 row(s) affected)

messages in the messages pane..

How to get identity field value after recordset update

I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work.

This is what I have:

rsRec.AddNew
rsRec("RequisitionID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
intItemId = rsRec("ReqChildID")
rsRec.Close

Forgive me if this has already been posted, but I couldn't find it.

[edit to add:]

rsRec is an ADODB.Recordset in case that wasn't clear.Look at @.@.IDENTITY and SCOPE_IDENTITY in BOL.|||1 intItemId set identity for replication
2 use keyset recordset type|||I appreciate the quick replies.

I am using @.@.Identity and it works well enough for my purposes.

[edit] I found the BOL. Never even looked at it before. Thanks for pointing it out

Thanks again all.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.

Agree. But it's good to know what differs those cases. Let the user choose what he needs. Give him fishing rod, not fish ;) .|||Due to time constraints I went with @.@.Identity. The database hits aren't very common so I will assume a certain small probability that there will be an error from time to time.

That being said, I would prefer Scope_Identity for 100% accuracy. I am continuing to work with SCOPE_IDENTITY when I can to try to make it work for me. As of this morning I got it to supply the correct ID using enterprise manager to test. I'll eventually get it to work in my code.

Thank you guys for helping me solve this problem.|||@.@.identity may acquire a value of anything else only when the target table contains a trigger that performs an insert into another table with identity field. if this is the case then scope_identity() needs to be used.

on another thought, you should put dml statements into a stored procedure and call it from your front end.|||I inherited this app. I would love to convert it to using stored procedures. I just need to get this section done so I can have a completed app, and a happy boss. Then I can convert as I go to make it all easier to work with.

Grrrr... I can't seem to get SCOPE_IDENTITY() to happen on the page.

I have:

set cn = Server.CreateObject("ADODB.Connection")
set rsRec = Server.CreateObject("ADODB.Recordset")
set rsIdent = Server.CreateObject("ADODB.Recordset")
cn.Open dbConnection

rsRec.Open sSQL,cn, 3 ,3
rsRec.AddNew
rsRec("ReqID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
rsRec.Close

sSQL = "SELECT SCOPE_IDENTITY() as Ident"
rsIdent.Open sSQL,cn, 3 ,3
intItemId = rsIdent("Ident")
rsIdent.Close

I have no idea why this doesn't work. I thought as long as I keep the connection(cn) open then this would work. Guess I'm wrong? Do I need to use the same recordset?|||sSQL="insert tbl (...) values (...) select ident=scope_identity()"
set rsRec=cn.execute(sSQL)|||I have simplified the statements that are actually in the code. Although that suggestion would likely make it work(as it works when I test it in the Query Analyzer), there are quite a few conditions that would affect the SQL statement and at this point I don't have the time to switch it to that style.

Its' done for the time being. I pushed it out for beta.

I'm only sorry I couldn't get it all to work fast enough, but that's what updates are for.

Thanks again for the help and pointing me in the right direction.