Problems pulling the last record in a table

[Garabaldi]

Folks,

I am a new to developing in ASP and VBscript... and I'll bet dollars to donuts that there is more than one error in the script I've placed below but I'd be eternally grateful for any help that anyone can give me in trying to solve my current problem...

I am having difficulty pulling the last record from an SQL Server Database table in ASP.

I keep getting this error:

"ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.

/oec_proj/trial_2.asp, line 52"

My code looks like this:

Call Connect

Set objMaxTicRS = Server.CreateObject("ADODB.Recordset&quot

objMaxTicRS.Open "SELECT * FROM tb_web_proj WHERE TicketNum = (SELECT MAX(TicketNum) FROM [tb_web_proj])", objADOConn
MaxTic = objMaxTicRS.Fields.Value
objMaxTicRS.Close

Set objMaxTicRS = Nothing

Call Connect

Public Sub Connect

Set objADOConn = Server.CreateObject("ADODB.Connection&quot
Set objADORS = Server.CreateObject("ADODB.Recordset&quot

sConnect = "Driver={SQL Server};" & _
"Server=ASQCUQ;" & _
"Database=OEC_PROJ;" & _
"Uid=admin;" & _
"Pwd=oecSQL;"

objADOConn.ConnectionString = sConnect
objADOConn.Open

sSqlQuery = "SELECT * FROM tb_web_proj WHERE TicketNum = '" & MaxTic & "'"

objADORS.Open sSqlQuery, objADOConn

sTicketNum = objADORS("TicketNum&quot
sRequestor = objADORS("Requestor&quot
sContNum = objADORS("ContNum&quot
sContEmail = objADORS("ContEmail&quot
sDept = objADORS("Dept&quot
sSite = objADORS("Site&quot
sSiteOwner = objADORS("SiteOwner&quot
sSiteOwnerContNum = objADORS("SiteOwnerContNum&quot
sSiteOwnerEmail = objADORS("SiteOwnerEmail&quot
sDynamic = objADORS("Dynamic&quot
sOrigDueDate = objADORS("OrigDueDate&quot

objADORS.Close
End Sub
%>

The error is refering to the line sTicketNum = objADORS("TicketNum&quot

Once again any help would be greatly appreciated.
--
Garabaldi

 

[phuctran]

Assume that you can connect to the database and get the recordset (I cannot test on that).
After getting the recordset objADORS, you have two cases:
1. objADORS has one or more records.
2. objADORS does not have any record.

If you are in case 1, then you won't have problem because your code only read the first records into variables.
If you are in case 2, then you have problem because you are reading non-existing record into variables.

The right way to do is you check for (EOF and BOF) before reading records. (EOF AND BOF) = True if there is no record found.

If (BOF AND EOF) Then
'...do something like raise a flag (isEmpty) ....
Else
'If you want to get the last record, run the following line
'objADORS.MoveLast

'...read record into variable
sTicketNum = objADORS("TicketNum&quot
'..... more ....
End If

 

[Garabaldi]

Thanks for your help phuctran...

I worded the goal I was trying to get to wrong... I don't necessarily need the last record in the table but the record which has the highest value.

Right now I have a form which places records into an SQL Server Database table and numbers them automatically. The problem is that they don't always number them in ascending order... (last time I looked at the table it had 79 come right after 56 then jump back to 71.... its messed)

So to work around this I tried to run a Select Max query against the TicketNum field. then using that variable do a Select * for that record...

Now for my next question... do you need to do a Select Min when you do a select Max to get a query to operate properly??? I wouldn't think so but then again what the heck do I know???

 

[phuctran]

The table does not have any idea about which record goes first, which record is next ... so if you open the table, you will see a mess in there. record 79 is not after record 71 ...)

if you already number them (automatically by using identity number) then you can use the statement like
Select top 2 <some fields> from <table> order by <identity field> desc

That will return to you the top two records (79, 78 in your case) in the recordset (I use top 2 for easy understanding, you can use top 1). Then you just use
If (BOF AND EOF) Then
'...do something like raise a flag (isEmpty) ....
Else
'...read record into variable
sTicketNum = objADORS(&quot;TicketNum&quot
'..... more ....
End If

to read the first top record (that is 79 in your case) in your variables. If I were you, I should use If (BOF AND EOF) condition in case the table does not have any records (that case, you will have an empty recordset then you will have problem of &quot;Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record. &quot; again)

 

[lobstah]

to get your max ticket number, all you need is this part of your select statement. then you can store the value in a variable and use it further down in your code as you are.

objMaxTicRS.Open &quot;SELECT MAX(TicketNum) FROM tb_web_proj&quot;, objADOConn

 

[phuctran]

Select top 2 <some fields> from <table> order by <identity field> desc

One more thing, <some fields> must contain the <identity filed>.

For example, you have table TABLE1
ID Name
2 A
4 B
1 C
10 D
7 F

If you run
Select top 2 ID, Name from Table1 order by ID desc
then you have
ID Name
10 D
7 F
in you recordset.