yassin092898
asked on
Recordset syntax
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=@Com panyName
-------------------------- ---------- ---------- ---------- -----
-------------------------- ---------- ---------- ---------- ---------- ---------- --------
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("compa nyName", 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 .Connectio n")
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
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=@Com
--------------------------
--------------------------
here is the wrapper function
function wrpGetAddress(Conn, CompanyName)
Dim cmd
Set cmd = Server.CreateObject("ADODB
With cmd
.ActiveConnection = objConn
.CommandText = "usr.spAddress"
.CommandType = adCmdStoredProc
End With
Set param = cmd.CreateParameter("compa
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
Conn.Open Database, User, Password
CompanyName = "IBM"
Set Rst = Server.CreateObject("ADODB
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
other wise you may use
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
ASKER
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
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
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 .Connectio n")
cn.Open strConnectionString
set cmdcolumnlist = Server.CreateObject("ADODB .Command")
Set cmdcolumnlist.ActiveConnec tion = cn
cmdcolumnlist.CommandText = "sp_columns"
cmdcolumnlist.CommandType = adCmdStoredProc
cmdcolumnlist.Parameters.R efresh
'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.d sn", "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.
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
cn.Open strConnectionString
set cmdcolumnlist = Server.CreateObject("ADODB
Set cmdcolumnlist.ActiveConnec
cmdcolumnlist.CommandText = "sp_columns"
cmdcolumnlist.CommandType = adCmdStoredProc
cmdcolumnlist.Parameters.R
'Check the return codes
cmdcolumnlist.Parameters(1
cmdcolumnlist.Parameters(2
cmdcolumnlist.Parameters(3
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.d
'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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Comment accepted as answer
something like that
-----------------
sConnection = "Provider=SQLOLEDB.1;Integ
Set oConn = Server.CreateObject("ADODB
oConn.Open sConnection
Set oCmd = Server.CreateObject("ADODB
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = cProcedureName
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 120
Set oRs= Server.CreateObject("ADODB
oRs.ActiveConnection = oConn
oRs.CursorLocation = adUseClient
oCmd.Parameters.Append
oCmd.CreateParameter( "@Id", adInteger, adParamInput, ,iId )
oRs.Open oCmd