Solved

Recordset syntax

Posted on 2000-05-17
6
700 Views
Last Modified: 2011-09-20
I have two tables customers and addresses. I want to get all addresses belonging to a given customer using stored procedure, a wrapper function and finaly use this function in my asp file.

here is the customer table.
-------------------------------------------------------------
CREATE PROCEDURE spAddress(@companyName varchar(50) )
as
Select *
From
Customers Inner Join addresses on
Customers.RecNumber = addresses.RecNumber
Where
Customers.CompanyName=@CompanyName
-------------------------------------------------------------

------------------------------------------------------------------------------------
here is the wrapper function

function wrpGetAddress(Conn, CompanyName)
 Dim cmd
 Set cmd = Server.CreateObject("ADODB.Command")
 
  With cmd
   .ActiveConnection = objConn
   .CommandText = "usr.spAddress"
   .CommandType = adCmdStoredProc
 End With
 
Set param = cmd.CreateParameter("companyName", advarchar, adParamInput, 50, CompanyName)
cmd.Parameters.Append param

 set wrpGetAddress = cmd.Execute      
end function

------------------------------------------------------------------------------------

Now I use the above function in my asp page.


Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Database, User, Password

CompanyName = "IBM"

Set Rst = Server.CreateObject("ADODB.Recordset")
 Rst = wrpGetAddress(Conn, CompanyName)

Rst.moveLast

--------------------------------------------------------------------------------------

Error is

Relationship
 
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Rst.MoveLast'

/temp/test.asp, line 50
 

I want to use the stored procedure and the wrapper function. But I need to tell that the returned recordset must be dynamic. I don't know the syntax
0
Comment
Question by:yassin092898
  • 2
  • 2
  • 2
6 Comments
 
LVL 5

Expert Comment

by:aioudine
ID: 2819907
Open RecordSet with Client Side cursor
something like that
-----------------
  sConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=zulu;Data Source=DATACENTER;Connect Timeout=2000"


  Set oConn = Server.CreateObject("ADODB.Connection")
  oConn.Open  sConnection
               
  Set oCmd = Server.CreateObject("ADODB.Command")
  Set oCmd.ActiveConnection = oConn
  oCmd.CommandText =  cProcedureName
  oCmd.CommandType = adCmdStoredProc
  oCmd.CommandTimeout = 120

  Set oRs= Server.CreateObject("ADODB.Recordset")
  oRs.ActiveConnection = oConn
  oRs.CursorLocation = adUseClient
  oCmd.Parameters.Append

oCmd.CreateParameter( "@Id", adInteger, adParamInput, ,iId )                
  oRs.Open oCmd

0
 
LVL 5

Expert Comment

by:aioudine
ID: 2819917
other wise you may use
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
0
 

Author Comment

by:yassin092898
ID: 2821232
I have been trying to implement your suggestions but it seems I am having hard time making it work.
All I want is to make the wrapper function return a dynamic recordset and I assign it a record set I declare in the main asp file.
If that can't be done please let me know.
I would appreciate if you can modify my wrapper function and the main asp file separately and show me the result.

Preferably, you may cut and past my entire original question and modify the wrapper function which lives different file than the calling main asp file.

Than you
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:ssripathy
ID: 2847042
yassin,
Instead of passing objects as argument parameters which is an inefficient method you could alter your code.
There are many methods you can accomplish what you have asked. But before I elaborate, your error for "rs.MoveLast property not supported" it could be either that your procedure or your cmd.Execute returned an empty recordset or the driver you are using for the connection genuinely does not support the property.
'******************************
'An example wrapper function to assist you
'*******************************
Function GetColumns(Byval strConnectionString , ByVal as_tablename )

    Dim lvar
    Dim intRecord
    Dim cmdcolumnlist
    Dim rscolumnlist
    Dim cn
      
      set cn = Server.CreateObject("ADODB.Connection")
      cn.Open strConnectionString
      set cmdcolumnlist = Server.CreateObject("ADODB.Command")
    Set cmdcolumnlist.ActiveConnection = cn
   
    cmdcolumnlist.CommandText = "sp_columns"
    cmdcolumnlist.CommandType = adCmdStoredProc
    cmdcolumnlist.Parameters.Refresh
    'Check the return codes
    cmdcolumnlist.Parameters(1).Value = as_tablename
    cmdcolumnlist.Parameters(2).Value = "dbo"
    cmdcolumnlist.Parameters(3).Value = "TestDatabase"
    Set rscolumnlist = cmdcolumnlist.Execute()
'return the recordset in a variant array
    lvar = rscolumnlist.GetRows()
    GetColumns = lvar
    cn.Close
   
    Set cn = Nothing
    Set rscolumnlist = Nothing
    Set cmdcolumnlist = Nothing
End Function

You could now use this wrapper function
<%
 Dim ivar_test
 Dim i
 ivar_test = GetColumns("FILEDSN=test.dsn", "Employees")
'lvar is a 2D array
For intRecord = 0 to Ubound(lvar , 2)  
 Response.write lvar(1, intRecord) & " " & lvar(2, intRecord)
'......your code for manipulation
'please remember you could have easily 'returned a ADODB or ADOR Recordset and manipulated the recordset.



Next
%>
'Hope this helps.
0
 

Accepted Solution

by:
ssripathy earned 50 total points
ID: 2847050
'sorry, need a correction in the last part of the code to replace ivar_test with lvar

'You could now use this wrapper function
<%
 Dim lvar
 Dim i
 lvar = GetColumns("FILEDSN=test.dsn", "Employees")
'lvar is a 2D array
For intRecord = 0 to Ubound(lvar , 2)  
 Response.write lvar(1, intRecord) & " " & lvar(2, intRecord)
'......your code for manipulation
'please remember you could have easily 'returned a ADODB or ADOR Recordset and manipulated the recordset.



Next
%>
'Hope this helps.

0
 

Author Comment

by:yassin092898
ID: 2847589
Comment accepted as answer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get hold of longitude and latitude in iframe string 11 51
is this a cms? 8 69
Session on Html 8 36
Table doesn't show the lines! 3 23
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

679 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