Stored Procedures

Discussion in 'ASP .Net Datagrid Control' started by Anthony M. Davis, May 19, 2004.

  1. Hi all,

    I'm a little confused about how to obtain a result set from a stored
    procedure (stored in a Visual FoxPro 8.0 database) from an ASP.NET web page
    in order to populate a WebForms DataGrid control.

    I've been experimenting using the code below but the only output i can get
    is:

    return_value
    True

    Here is attempt #1:

    OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
    selectCMD.CommandType = CommandType.StoredProcedure;
    selectCMD.CommandTimeout = 30;

    OleDbDataAdapter da = new OleDbDataAdapter();
    da.SelectCommand = selectCMD;

    conn.Open();

    DataSet ds = new DataSet();
    da.Fill(ds);

    DataView dv = new DataView(ds.Tables[0]);

    grdSurcharges.DataSource = dv;
    grdSurcharges.DataBind();

    conn.Close();


    ....and attempt #2 (same results as #1)



    OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
    selectCMD.CommandType = CommandType.StoredProcedure;
    selectCMD.CommandTimeout = 30;

    //OleDbParameter sampParm = selectCMD.Parameters.Add("RETURN_VALUE",
    OleDbType.Integer);
    //sampParm.Direction = ParameterDirection.ReturnValue;

    conn.Open();

    OleDbDataReader reader = selectCMD.ExecuteReader();

    grdSurcharges.DataSource = reader;
    grdSurcharges.DataBind();

    reader.Close();
    conn.Close();


    The VFP stored procedure is defined as follows:

    PROCEDURE GetSurcharges
    SELECT * FROM surcharge_application
    ENDPROC


    Thanks for your help,
    Tony
     
    Anthony M. Davis, May 19, 2004
    #1
    1. Advertisements

  2. In esIC$,
    Hi Tony,

    Even in Visual FoxPro this stored procedure selects some data into a local
    temporary cursor and then returns "True". Have you considered SQL
    Pass-through?
     
    Cindy Winegarden, May 20, 2004
    #2
    1. Advertisements

  3. Hi Cindy. Thanks. Could you please provide an example using SQL
    Pass-through?

    Tony
     
    Anthony M. Davis, May 20, 2004
    #3
  4. Hi Tony!

    Look at the VFP help for SQL pass-through technology

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

    Mit freundlichen Grüßen

    Wolfgang Schmale

    [MVP für Visual FoxPro]

    --------------------------------------------
     
    Wolfgang Schmale, May 20, 2004
    #4
  5. Sorry, I guess I don't understand.. Isn't SQL Pass-Through for accessing SQL
    Server data from FoxPro? Please remember my data is all in FoxPro dbf's and
    the stored procedure I'm calling via VFPOLEDB is in the container .DBC/.DCT.

    Again, all I'm trying to do is use OleDb to call a VFP-stored procedure and
    obtain a result set. I'm now wondering if it is not possible to obtain
    result sets from VFP stored procedures via OleDb?? (i.e. need to upsize to
    SQL Server if i want to make use of stored procedures in this manner?)

    Tony
     
    Anthony M. Davis, May 21, 2004
    #5
  6. Hi,

    In access, a stored procedure looks like

    PARAMETERS name Text(50), id Long;
    UPDATE tblName
    SET lastname = [name]
    WHERE name_id = [id]

    You can access or declare this stuff using ADO (use a ADODB.Command object
    with declared parameters and use the jet provider) as if it were a stored
    proc. I suspect for 90% that something equal must be possible with Foxpro...
     
    Egbert Nierop \(MVP for IIS\), May 21, 2004
    #6
  7. Hi Egbert. I'm able to call my stored procedure okay, but I'm only getting a
    return_value back as opposed to a result set (my stored proc uses SELECT
    rather than UPDATE). Here's the code I included in my original post:


    OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
    selectCMD.CommandType = CommandType.StoredProcedure;
    selectCMD.CommandTimeout = 30;

    OleDbDataAdapter da = new OleDbDataAdapter();
    da.SelectCommand = selectCMD;

    conn.Open();

    DataSet ds = new DataSet();
    da.Fill(ds);

    DataView dv = new DataView(ds.Tables[0]);

    grdSurcharges.DataSource = dv;
    grdSurcharges.DataBind();

    conn.Close();


    DataGrid Output:

    return_value
    True

    My test stored procedure is defined as:

    PROCEDURE GetSurcharges
    SELECT * FROM surcharge_application

    * note to readers: this proc will be more complex. i'm only using a
    simple SELECT for test purposes.. otherwise, i have no problem changing the
    OleDbCommand to "SELECT * FROM surcharge_application" and retrieving a
    result set
    ENDPROC

    Thanks for all your input,

    Tony

     
    Anthony M. Davis, May 21, 2004
    #7
  8. Tony
    There'll a frre public beta of VFP9 available in a few weeks. Be sure to
    check out enhancements to the OLE DB functionality. I would think there may
    be some that solve these kind of problems with stored procedures in VFP.
    -Anders
     
    Anders Altberg, May 21, 2004
    #8
  9. In news: ,
    Hi Tony,

    You asked about SQL Pass-through in your other post. Basically it's passing
    a SQL sommand in the form of a string to another database - it doesn't
    matter which is the front and which is the back end. Using SQL Pass-through
    you would send the string "SELECT * FROM surcharge_application" to the VFP
    database.

    I'm pretty sure VFP can't return a dataset from a stored procedure, but I
    haven't had time to search thoroughly. Why do you think you need to call a
    stored procedure instead of just sending the query string?
     
    Cindy Winegarden, May 22, 2004
    #9
  10. Well, the actual strored procedure would be more complex. The procedure
    I've illustrated is only for testing and for the purposes of this thread..

    Tony
     
    Anthony M. Davis, May 22, 2004
    #10
  11. Your code, looking at the syntax is correct.

    But the same with Access (and VP might be more powerfull) stored procs are
    not assumed to be really working in monolithic database. The point is that
    fox pro, needs to know how to execute a stored proc with multiple or
    difficult statements (such as IF ELSE ). But as soon as you access, the VP
    database through an OLEDB driver, the driver also should need to know how to
    access and execute it. Because of limitations of the driver and because of
    the monolithic characteristics, of VP you can't program stored procs as if
    Fox Pro (.exe) were in memory to execute it.

    I'd advise to build a dataadapter with simple SELECT/DELETE/INSERT
    statements and to combine them to have, after all, the same effect as a
    stored proc. Although, this is less efficient.

    For real stored procs, you need a non monolithic database such as SQL server
    or Oracle.
     
    Egbert Nierop \(MVP for IIS\), May 24, 2004
    #11
  12. Thanks Egbert,

    Well, I'll be upsizing to SQL Server in the near future. Appreciated your
    input.

    Tony

     
    Anthony M. Davis, May 25, 2004
    #12
  13. VFP's OLE DB driver supports 122 commands, 206 functions, 15 SYS functions
    and 40 SET commands. IF ..ELSE..ENDIF and SCAN - ENDSCAN are supported and
    VFP's entire SQL language implementation of course.
    VFP 9 is bound to enhance this in quite a few frequently requested areas.
    -Anders

     
    Anders Altberg, May 25, 2004
    #13
  14. Great.
    I'm surprised. This might void my answer to Anthony. But I was right that
    oledb/FP is a monolithic approach.
     
    Egbert Nierop \(MVP for IIS\), May 25, 2004
    #14
  15. Monolithic, well that depends on which backend you use in a VFP application,
    or what front-end you use in to drive a VFP backend.
    -Anders
     
    Anders Altberg, May 27, 2004
    #15
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.