0

I have never used code like this before, and am lost on how to output the code to an asp classic page. I tried simply calling the rs("theFile") however, that did not work. It is breaking and giving me an error.

here is the code, with a demo line, to output. I need to put the code the same way it outputs in SSMS, in a Classic ASP page.

Set sqlGetSong = Server.CreateObject("ADODB.Command")
sqlGetSong.ActiveConnection=EV_WikiConn
sqlGetSong.Prepared = true

sqlGetSong.commandtext = "DECLARE @seperator INT;" & vbCrLf & _
   "Declare @String nvarchar(max);" & vbCrLf & _
   "Declare @Tab TABLE (DT   VARCHAR(100));" & vbCrLf & _
   "set @String='File_Date_(Jan_21,_1976) / File_Date_(Mar_18,_1996) / File_Date_(Jan_22,_1983) / File_Date_(Jan_20,_1983) / File_Date_(Jan_14,_1976) / File_Date_(Jan_18,_1979) / File_Date_(Dec_18,_1979)';" & vbCrLf & _
   "WHILE (CHARINDEX('/', @String, 0) > 0) BEGIN" & vbCrLf & _
       "SET @seperator =   CHARINDEX('/', @String, 0);" & vbCrLf & _
       "INSERT INTO   @Tab (DT) SELECT RTRIM(LTRIM(SUBSTRING(@String, 0, @seperator)));" & vbCrLf & _
       "SET @String = STUFF(@String, 1, @seperator, '');" & vbCrLf & _
   "END" & vbCrLf & _
   "INSERT INTO   @Tab (DT) SELECT RTRIM(LTRIM(@String));" & vbCrLf & _ 
   "select DT as theFile from" & vbCrLf & _
       "(select right(REPLACE(DT,right(DT,7),''),2)DY, right(REPLACE(DT, ')',''), 4)YR, * from  @Tab) A" & vbCrLf & _
   "order by YR, DY;"

set rsSongs = sqlGetSong.execute
if rsSongs.eof then
    ' show records here
end if

It gives me an error here:

if rsSongs.eof then
Operation is not allowed when the object is closed.

Can someone please let me know how to output this code to Classic ASP. Thank You Wayne

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Wayne Barron
  • 304
  • 4
  • 16
  • The code you've provided is purely SQL, there's no classic asp VBScript there. Your error message means that you haven't opened your recordset object. I think you need to read a tutorial about how to talk to a database with Classic ASP - here's one. http://www.4guysfromrolla.com/webtech/faq/Databases/faq4.shtml – John Mar 05 '16 at 13:18
  • John 3, sorry that I did not have the rest of the code in. I know how to show records, and the code above, I just need to know how to get it to show records. Even with the records provided in the code, is still gives that error. So. Any idea's anyone on how to get this code, to show records... – Wayne Barron Mar 05 '16 at 16:39
  • You are missing the `Not` from `If Not rsSongs.EOF Then` the `If` statement at the moment will only get to "show records here" when there are no records to show. The `If Not` does the opposite, you will get to "show records here" when the Recordset has not reached the EOF. If the recordset had reached the EOF (End Of File) it would be closed as there are no records to show. – user692942 Mar 05 '16 at 17:56
  • That was a mis-type Lankymart. The problem with the code, is that it will not display the records, even though they are there, it will not show them. Using the If rs.eof then,,,,, else..... end if.... Will not work..... – Wayne Barron Mar 06 '16 at 03:08
  • Possible duplicate of ["Operation is not allowed when the object is closed" when executing stored procedure](http://stackoverflow.com/questions/16529032/operation-is-not-allowed-when-the-object-is-closed-when-executing-stored-proce) – Shadow The GPT Wizard Mar 06 '16 at 09:20
  • You are missing "SET NOCOUNT ON", as the answer in the other question says. – Shadow The GPT Wizard Mar 06 '16 at 09:21
  • Hello Shadow. Unfortunately, the SET NOCOUNT ON, did not resolve the issue here, I still get the same error. The script runs in SQL Server, like it supposed to run, But not in the ASP page. So, I am at a loss. – Wayne Barron Mar 06 '16 at 20:32
  • If I remove the if ........rsSongs.eof ...... all together. I get an error on this line..... rsSongs("theFile") stating that it cannot be found. – Wayne Barron Mar 06 '16 at 20:46
  • As already stated `SET NOCOUNT ON;` at the beginning of the `CommandText` should fix the issue. Failing that you can always use `rsSongs.NextRecordset` to keep getting the next recordset until you find the one where the `rsSongs.State` isn't `adStateClosed`. – user692942 Mar 08 '16 at 23:15
  • I will give it a shot after while Lanky and see if it resolves the issue. – Wayne Barron Mar 09 '16 at 22:26
  • 1
    Finally got back around to this. Lanky, that worked, adding in the SET NOCOUNT ON. However, it only returns one record, and there are many. How would I go about using the NextRecordSet. I have never used it before, and am looking right now, for information on it. – Wayne Barron Mar 11 '16 at 04:23

2 Answers2

1

I have updated the code, with the information provided by Lankymart. Everything works wonderfully now.

Lankymart, if you would like to take this information and make it your own, I will accept you as Answer.

Set sqlGetSong = Server.CreateObject("ADODB.Command")
sqlGetSong.ActiveConnection=EV_WikiConn
sqlGetSong.Prepared = true

sqlGetSong.commandtext = "SET NOCOUNT ON DECLARE @seperator INT;" & vbCrLf & _
   "Declare @String nvarchar(max);" & vbCrLf & _
   "Declare @Tab TABLE (DT   VARCHAR(100));" & vbCrLf & _
   "set @String='File_Date_(Jan_21,_1976) / File_Date_(Mar_18,_1996) / File_Date_(Jan_22,_1983) / File_Date_(Jan_20,_1983) / File_Date_(Jan_14,_1976) / File_Date_(Jan_18,_1979) / File_Date_(Dec_18,_1979)';" & vbCrLf & _
   "WHILE (CHARINDEX('/', @String, 0) > 0) BEGIN" & vbCrLf & _
       "SET @seperator =   CHARINDEX('/', @String, 0);" & vbCrLf & _
       "INSERT INTO   @Tab (DT) SELECT RTRIM(LTRIM(SUBSTRING(@String, 0, @seperator)));" & vbCrLf & _
       "SET @String = STUFF(@String, 1, @seperator, '');" & vbCrLf & _
   "END" & vbCrLf & _
   "INSERT INTO   @Tab (DT) SELECT RTRIM(LTRIM(@String));" & vbCrLf & _ 
   "select DT as theFile from" & vbCrLf & _
       "(select right(REPLACE(DT,right(DT,7),''),2)DY, right(REPLACE(DT, ')',''), 4)YR, * from  @Tab) A" & vbCrLf & _
   "order by YR, DY;"

set rsSongs = sqlGetSong.execute
while not rsSongs.eof%>
<%=rsSongs("theFile")%><br />
<%rsSongs.movenext
Wend
'set rsSongs = rsSongs.Nextrecordset ' was not needed

Thank You Wayne

Wayne Barron
  • 304
  • 4
  • 16
0

You need to open the connection before using the recordset:

EV_WikiConn.Open

Also: this seems like an incredibly awkward way to do some simple string manipulation. One wonders why you connect to a database here at all, when this data could just be hard-coded into an array in the correct order.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Hello Joel.... 2nd paragraph.... >> with a demo line << This is to show how it works, and what is supposed to be shown... If you have a better way of ordering the demo line, then please, by all means, show... Also, the connection is already opened. I can put a simple select statement in this, and it works without an issue, this SQL code, will not let it work. but like I said. If you have a better way, of order the content (By Year and Month and Day), then by all means, please assist. – Wayne Barron Mar 06 '16 at 03:11
  • EV_WikiConn can be a connection string, in which case classic ASP will create a temporary connection on its own, and Open it behind the scenes. The problem here is elsewhere. (See [here](https://msdn.microsoft.com/en-us/library/ms676107(v=vs.85).aspx): "If you attempt to call the Execute method on a Command object before setting this property to an open Connection object **or valid connection string**, an error occurs.") – Shadow The GPT Wizard Mar 06 '16 at 09:11
  • Shadow. If I replace the SQL Code, with a simple Query... select field from table" ........ It runs without an issue, it is just not working with this SQL Query. The connection is opened, just not with this type of Query, and if I remove the if rsSongs.eof all together, then I get an error on the rsSongs("theFile"), saying that it does not exist. ...........I have never used this type of SQL Query before, so, I am in new territory. – Wayne Barron Mar 06 '16 at 20:49