jknj72
asked on
Calling a stored procedure In Oracle passing in a sort variable not working
I have a stored proc that Im calling from my ASP.Net application and Im passing in a sortfield and a cursor for my return values. So when I click on a column header Im calling the proc and passing in the header value as the sort field. It doesnt seem to be working though. I want to see if the proc is the problem first and then I will go into the application to possibily find the problem.
Here is the proc and the call
create or replace
procedure getallvendors (vSortField in varchar2, c_cursor OUT SYS_REFCURSOR) as
begin
open c_cursor for
SELECT Vendors.VENDORID, Vendors.COMPANY, Vendors.ADDRESS1, VENDORS.ADDRESS2, Vendors.CITY,
Vendors.STATE, Vendors.ZIP, Vendors.TELEPHONE, Vendors.FAX,
Vendors.SupportWebsite
FROM Vendors
ORDER BY vSortField
;
end getallvendors;
And here is the call in my code just in case:
Sub LoadGvVendors()
'Load the grid
Try
Dim cmdVendors = GenericDbAccess.CreateStor edProcComm and("GetAl lVendors")
With cmdVendors
If SortField = "" Then
SortField = "Company"
End If
.Parameters.Add(New OracleParameter("vSortFiel d", OracleDbType.Varchar2)).Va lue = SortField
.Parameters.Add(New OracleParameter("c_cursor" , OracleDbType.RefCursor)).D irection = ParameterDirection.Output
End With
Dim dsVendors = GenericDbAccess.CreateData Set(cmdVen dors, "GetAllVendors")
Dim dtVendors As New DataTable
dtVendors = dsVendors.Tables("GetAllVe ndors")
'Persist the table in the Session object.
Session("dtVendors") = dtVendors
'Bind the GridView control to the data source.
gvVendors.DataSource = Session("dtVendors")
gvVendors.DataBind()
Catch ex As Exception
Throw ex
'Response.Redirect("UnderC ontruction .aspx")
End Try
End Sub
Here is the proc and the call
create or replace
procedure getallvendors (vSortField in varchar2, c_cursor OUT SYS_REFCURSOR) as
begin
open c_cursor for
SELECT Vendors.VENDORID, Vendors.COMPANY, Vendors.ADDRESS1, VENDORS.ADDRESS2, Vendors.CITY,
Vendors.STATE, Vendors.ZIP, Vendors.TELEPHONE, Vendors.FAX,
Vendors.SupportWebsite
FROM Vendors
ORDER BY vSortField
;
end getallvendors;
And here is the call in my code just in case:
Sub LoadGvVendors()
'Load the grid
Try
Dim cmdVendors = GenericDbAccess.CreateStor
With cmdVendors
If SortField = "" Then
SortField = "Company"
End If
.Parameters.Add(New OracleParameter("vSortFiel
.Parameters.Add(New OracleParameter("c_cursor"
End With
Dim dsVendors = GenericDbAccess.CreateData
Dim dtVendors As New DataTable
dtVendors = dsVendors.Tables("GetAllVe
'Persist the table in the Session object.
Session("dtVendors") = dtVendors
'Bind the GridView control to the data source.
gvVendors.DataSource = Session("dtVendors")
gvVendors.DataBind()
Catch ex As Exception
Throw ex
'Response.Redirect("UnderC
End Try
End Sub
ASKER
I see what you mean and I corrected it but it is not sorting correctly...IIt doesnt seem to be doing any kind of sorting since Ive added this. Any other thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I see that you treat the select statement as a string and then execute it. I dont have it being done that way. Should I try it like that?
Fast forward 10 mins later......
I tried it and it worked. Seems like the way you call your statement within the Procedure worked for me.
Once again slight...THANK YOU!!!
Fast forward 10 mins later......
I tried it and it worked. Seems like the way you call your statement within the Procedure worked for me.
Once again slight...THANK YOU!!!
ASKER
great job again!!
No problem. Glad to help.
Try this:
Open in new window