Solved

Populate array from database

Posted on 2003-12-11
13
702 Views
Last Modified: 2007-12-19
Heres what I have so far. In my database there is a table that holds all the news items and the date for each. I am using the following code to retrieve the top 6 items.

Function GetNewsData(poConnection)
Dim lsSQL
Dim loCommand
      
      lsSQL = "SELECT TOP 6" & _
                        "NCount, NewsDate, SuperScript, NYear, NewsData " & _
                  "FROM " & _
                        "dbo.tblNews" & _
                  "ORDER BY " & _      
                        "NCount DESC"

      Set loCommand = Server.CreateObject("ADODB.Command")
      
      Set loCommand.ActiveConnection = poConnection
      
      loCommand.CommandType = adCmdText
      loCommand.CommandText = lsSQL
      
      Set GetUserData = loCommand.Execute
      
    Set loCommand = Nothing
      
End Function

What I would like to know is how can I take the data from this and place it into an array that will hold all 6 rows of data. I want to take this array and then be able to populate the news page with these 6 items. So I would need an array that will hold...

NCount
NewsDate
SuperScript
NYear
NewsData

6 times over. Is this possible or just to much work? I have never worked with arrays before and am at a total loss here. I have no idea how to fill the array and then once it is filled how to use it to create the news on the page.
0
Comment
Question by:ferenczy
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 18

Expert Comment

by:SquareHead
ID: 9925419
Try using the ADO getrows method
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9925433
Like this:

If GetUserData is your recordset, then

dim myArray

if not(GetUserData.eof and GetUserData.bof) then
   myArray = GetUserData.GetRows()
   GetUserData.close
end if
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9925440
the GetRows method creates a zero-based array of the items in your recordset...
0
 
LVL 18

Expert Comment

by:SquareHead
ID: 9925447
Here's a description:


GetRows Method
Retrieves multiple records of a Recordset object into an array.

Syntax
array = recordset.GetRows( Rows, Start, Fields )
Return Value
Returns a Variant whose value is a two-dimensional array.

Parameters
Rows
Optional. A GetRowsOptionEnum value that indicates the number of records to retrieve. The default is adGetRowsRest.
Start
Optional. A String value or Variant that evaluates to the bookmark for the record from which the GetRows operation should begin. You can also use a BookmarkEnum value.
Fields
Optional. A Variant that represents a single field name or ordinal position, or an array of field names or ordinal position numbers. ADO returns only the data in these fields.
Remarks
Use the GetRows method to copy records from a Recordset into a two-dimensional array. The first subscript identifies the field and the second identifies the record number. The array variable is automatically dimensioned to the correct size when the GetRows method returns the data.

If you do not specify a value for the Rows argument, the GetRows method automatically retrieves all the records in the Recordset object. If you request more records than are available, GetRows returns only the number of available records.

If the Recordset object supports bookmarks, you can specify at which record the GetRows method should begin retrieving data by passing the value of that record's Bookmark property in the Start argument.

If you want to restrict the fields that the GetRows call returns, you can pass either a single field name/number or an array of field names/numbers in the Fields argument.

After you call GetRows, the next unread record becomes the current record, or the EOF property is set to True if there are no more records.
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9926635
Like squarehead suggested, you should be using getrows method.

Here is why you should be using getrows..
http://www.learnasp.com/advice/whygetrows.asp
http://www.learnasp.com/learn/dbtablegetrowsnonum.asp

Here is one of the best articles i've found on getrows...Go through all the 6 lessons.. Its pretty cool
http://authors.aspalliance.com/mbrink1111/getrows/getrows.asp

Cheers!!
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9927512
Hi ferenczy,

A recordset is a good array, why not return a disconnected recordset.


Dim rsNews
Set rsNews = GetNewsData(poConnection)

While not rsnews.eof and not rsnews.bof
  response.write(rsNews.Fields("NCount").value)
  response.write(rsNews.Fields("NewsDate").value)
  response.write(rsNews.Fields("SuperScript").value)
  response.write(rsNews.Fields("NYear").value)
  response.write(rsNews.Fields("NewsData").value)
  response.Write("<br>")
  rsNews.Movenext
Wend


Function GetNewsData(poConnection)
  ' Returns a disconnected recordset

  Dim rs
  Dim lsSQL
  Dim loCommand
     
     lsSQL = "SELECT TOP 6" & _
                    "NCount, NewsDate, SuperScript, NYear, NewsData " & _
               "FROM " & _
                    "dbo.tblNews" & _
               "ORDER BY " & _    
                    "NCount DESC"

     Set loCommand = Server.CreateObject("ADODB.Command")
     Set rs = Server.CreateObject("ADODB.Recordset")
     rs.CursorLocation = adUseClient
     rs.LockType = adLockReadOnly

     
     Set loCommand.ActiveConnection = poConnection
     
     loCommand.CommandType = adCmdText
     loCommand.CommandText = lsSQL
     
     Set rs = loCommand.Execute
     Set rs.ActiveConnection = Nothing
     Set loCommand = Nothing
     GetNewsData = rs
     Set rs = Nothing

End Function


Alan :)


0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:ferenczy
ID: 9929281
Thx guys some great stuff here... But I have one question so far... what is the difference between a record set and a disconnected recordset?

ap - thx for the links .. I'll hit them later today.

SquareHead - thx tons, I'll check that out later this afternoon.

Alan - thx as well.

I'll be most likely awarding the points later tonight after I've had a day to muddle around with this stuff. I love this place.... so full of people so willing to help others. You guys are great.
0
 

Author Comment

by:ferenczy
ID: 9930136
Error Type:
ADODB.Recordset (0x800A0E79)
Operation is not allowed when the object is open.


I get that error when I use yoru code. :( hate errors lol

any ideas?
0
 

Author Comment

by:ferenczy
ID: 9930138
sorry that was when using alans code
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9931233
Hi ferenczy,

Sorry my friend, I usually test things before posting but I am in the middle of a rebuild at home and IIS is not all systems go yet, think this is good code, will test it for you when I get to work in a couple of hours. A dis-connected recordset is exactly as the name suggests a recordset that is dis-connected from it's source, meaning that you can still manipulate the dataset, add-modify-delete etc.. but the source remains unchanged. I often open an empty recordset based on the table that the data being collected will eventually end up in. I dont want anonymous users adding records to my tables, but they can add to a disconnected recordset, which I then persist as xml and mail to myself as an attachment using cdo.sys. I then have other sql mail processes that parse the mail into a staging table for perusal by an operator before being added to the datasource proper.

If this aint right, let me know, I 'll get to testing it for you.

Alan :)


Function GetNewsData(poConnection)
  ' Returns a disconnected recordset
  ' else returns empty recorset

  Dim rs
  Dim lsSQL
  Set GetNewsData = Nothing
     
     lsSQL = "SELECT TOP 6" & _
                    "NCount, NewsDate, SuperScript, NYear, NewsData " & _
               "FROM " & _
                    "dbo.tblNews" & _
               "ORDER BY " & _    
                    "NCount DESC"

     Set rs = Server.CreateObject("ADODB.Recordset")
     rs.CursorLocation = adUseClient
     rs.LockType = adLockReadOnly

     poConnection.execute lsSQL rs
     
     Set rs.ActiveConnection = Nothing   'disconnect the recordset
     Set GetNewsData = rs
     
     Set rs = Nothing

End Function
0
 

Author Comment

by:ferenczy
ID: 9931328
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/testingRS.asp, line 40, column 27
poConnection.execute lsSQL rs
-------------------------------^

my new found error :)

0
 

Author Comment

by:ferenczy
ID: 9931748
maybe if I added the other code I have so far... it will most likly help out. btw thx tons for the help.

'**********************************************
' MAIN
'**********************************************
Sub Main

Dim loConnection
Dim rsNews


            Set loConnection = Server.CreateObject("ADODB.Connection")

            loConnection.ConnectionTimeout = Application("ConnectionTimeout")
            loConnection.CommandTimeout = Application("CommandTimeout")

            loConnection.Open Application("ConnectionString"), Application("ConnectionID"), Application("ConnectionPassword")



%>
<html>



<head>

<title>Testing Disconnected Recordset</title>
<body>

<%


            
            Set rsNews = GetNewsData(loConnection)

                  While not rsNews.eof and not rsNews.bof
                        response.write(rsNews.Fields("NCount").value)
                        response.write(rsNews.Fields("NewsDate").value)
                        response.write(rsNews.Fields("SuperScript").value)
                        response.write(rsNews.Fields("NYear").value)
                        response.write(rsNews.Fields("NewsData").value)
                        response.Write("<br>")
                        rsNews.Movenext
                  Wend



                  loConnection.Close
            Set loConnection = Nothing
%>


</body>

</html>


<%
End Sub
%>
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 9931872

Hi ferenczy,

Tested...

<%@ Language = VBScript %>
<%
Option Explicit
<!-- metadata type="typelib" name="ADO Type Library" uuid="00000206-0000-0010-8000-00aa006d2ea4" -->
%>

<%



Dim rsNews, poConnection, sDataConnect, sDataPath

'Set poConnection = Server.CreateObject("ADODB.Connection")
'sdataPath = Server.MapPath(".") & "\db1.mdb"

'sDataConnect = adoConnectJet40(sdataPath , "")

'poConnection.Open sDataConnect

Set rsNews = GetNewsData(poConnection)

  While Not rsNews.EOF And Not rsNews.BOF
        response.Write (rsNews.Fields("NCount").Value)
        response.Write (rsNews.Fields("NewsDate").Value)
        response.Write (rsNews.Fields("SuperScript").Value)
        response.Write (rsNews.Fields("NYear").Value)
        response.Write (rsNews.Fields("NewsData").Value)
        response.Write ("<br>")
        rsNews.MoveNext
   Wend

'Response.Write("rsNews done")
'Response.end


Function GetNewsData(poConnection)
  ' Returns a disconnected recordset
  ' else returns empty recorset

  Dim rs
  Dim lsSQL
  Set GetNewsData = Nothing
     
     lsSQL = "SELECT TOP 6" & _
                    "NCount, NewsDate, SuperScript, NYear, NewsData " & _
               "FROM " & _
                    "dbo.tblNews" & _
               "ORDER BY " & _    
                    "NCount DESC"

     
     
     Set rs = Server.CreateObject("ADODB.Recordset")
     rs.CursorLocation = 3       'adUseClient
     rs.LockType = 1             'adLockReadOnly
     rs.open lsSQL, poConnection, 0, 1
     
     Set rs.ActiveConnection = Nothing
     Set GetNewsData = rs
     
     Set rs = Nothing

End Function

Public Function adoConnectJet40(psDataPath, psFilePassword)
 
  ' returns Jet 4.0 ADO connect string:
  '   "Provider=Microsoft.Jet.OLEDB.4.0
  '     ;Data Source=C:\xxx\DB1.mdb
  '     ;Jet OLEDB:Database Password=xxx"
 
  Dim sProvider, sDataSource, sDBPassword

  sProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
  sDataSource = ";Data Source=" & psDataPath
  If psFilePassword = "" Then
    sDBPassword = ""
  Else
    sDBPassword = ";Jet OLEDB:Database Password=" & psFilePassword
  End If

  adoConnectJet40 = sProvider & sDataSource & sDBPassword

End Function

%>

Alan :)

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now