Link to home
Start Free TrialLog in
Avatar of jknj72
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.CreateStoredProcCommand("GetAllVendors")
            With cmdVendors
                If SortField = "" Then
                    SortField = "Company"
                End If
                .Parameters.Add(New OracleParameter("vSortField", OracleDbType.Varchar2)).Value = SortField
                .Parameters.Add(New OracleParameter("c_cursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
            End With

            Dim dsVendors = GenericDbAccess.CreateDataSet(cmdVendors, "GetAllVendors")
            Dim dtVendors As New DataTable
            dtVendors = dsVendors.Tables("GetAllVendors")

            '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("UnderContruction.aspx")
        End Try
    End Sub
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Make the select a string and concatenate the vSortField to it.  Otherwise you are ordering by the 'string' itself.

Try this:
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;

Open in new window

Avatar of jknj72

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72

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!!!
Avatar of jknj72

ASKER

great job again!!
No problem.  Glad to help.