Hi There,
Im doing a lot of access to a db and retrieval of all informaiton in numerous tables. Now i know that should be using stored procedures bu i am trying to get my head into functions and thus have the below question.

Ive created a function shown below called getALL. I want it to return a recordset so as I can then process the information retrieved.
In my sub, how do i use this function and continue processing after the retrieval of the recordset. I start erroring when i attempt to code use and though sumone could look over.

cheers

my function which is defined in a SSI
Conn (the connection) is defined in another SSI.

function getAll(tableName) 
    
                Set RS = Server.CreateObject("ADODB.Recordset") 
	strSQL = "SELECT * FROM "&dbName
	RS.Open strSQL, Conn 
                getAll=RS
	
end function

sub getSelectTable(tableName)  

                tabN=tableName
	function call here i assume
	RS.MoveFirst 

                'process recordset here

Pass the contents of the rs in an array, it you try to pass the rs it will error due to the variable scope. RS is a local to the function therefore is only valid inside the function.
This way you can ensure that RS is closed and destroyed after each use.

Also you should not use * with ADO to select all columns, use a fieldlist even if all columns are requiredd. The use of * makes the DB call less efficient, as the SQL server has to parse the table twice, once to retrieve the column names and a second time to retrieve the data.

function getAll(tableName) 
    
                Set RS = Server.CreateObject("ADODB.Recordset") 
	strSQL = "SELECT fieldlist FROM " & tableName
	RS.Open strSQL, Conn 
                getAll=RS.GetRows()
                RS.close
                set rs = nothing
	
end function

DevGuru GetRows() reference

HI thanks for that although I am confused then what I do with the array. Do i process like a recordSet or transfer array to the record set?

Steve

The data is returned in a two dimension array the first dimension is the columns, the second the rows. So it's simply processed as any other array.
number of columns = ubound(data_array,1)
number of rows (recordcount) = ubound(data_array,2)

To recover the last record (for instance) it would be

for i = 0 to ubound(data_array,1)
response.write data_array(i,ubound(data_array,2))
next

the first record would be

for i = 0 to ubound(data_array,1)
response.write data_array(i,lbound(data_array,2))
next

This is also where using an explicit fieldlist is important because the columns will always be returned in the same order (the fieldlist) even if the table gets restructured.

It can be a little daunting at first and you'll probably code it the wrong way around a few times, but it is worth persevering and learning as the speed increase in queries can be noticeable on a busy database.

wicked. thanks very much