Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Datagrid Control > Stored Procedures

Reply
Thread Tools

Stored Procedures

 
 
Anthony M. Davis
Guest
Posts: n/a
 
      05-19-2004
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


 
Reply With Quote
 
 
 
 
Cindy Winegarden
Guest
Posts: n/a
 
      05-20-2004
In news: esIC$(E-Mail Removed),
Anthony M. Davis <(E-Mail Removed)> wrote:
> 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.........
> The VFP stored procedure is defined as follows:
>
> PROCEDURE GetSurcharges
> SELECT * FROM surcharge_application
> ENDPROC


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 MCSD, Microsoft Visual FoxPro MVP
http://www.velocityreviews.com/forums/(E-Mail Removed) www.cindywinegarden.com



 
Reply With Quote
 
 
 
 
Anthony M. Davis
Guest
Posts: n/a
 
      05-20-2004
Hi Cindy. Thanks. Could you please provide an example using SQL
Pass-through?

Tony

"Cindy Winegarden" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> In news: esIC$(E-Mail Removed),
> Anthony M. Davis <(E-Mail Removed)> wrote:
> > 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.........
> > The VFP stored procedure is defined as follows:
> >
> > PROCEDURE GetSurcharges
> > SELECT * FROM surcharge_application
> > ENDPROC

>
> 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 MCSD, Microsoft Visual FoxPro MVP
> (E-Mail Removed) www.cindywinegarden.com
>
>
>



 
Reply With Quote
 
Wolfgang Schmale
Guest
Posts: n/a
 
      05-20-2004
Hi Tony!

Look at the VFP help for SQL pass-through technology

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

Mit freundlichen Grüßen

Wolfgang Schmale

[MVP für Visual FoxPro]

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


 
Reply With Quote
 
Anthony M. Davis
Guest
Posts: n/a
 
      05-21-2004
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

"Wolfgang Schmale" <(E-Mail Removed)> wrote in message
news:%23Eb$(E-Mail Removed)...
> Hi Tony!
>
> Look at the VFP help for SQL pass-through technology
>
> --
> ----------------------------------------------
>
> Mit freundlichen Grüßen
>
> Wolfgang Schmale
>
> [MVP für Visual FoxPro]
>
> --------------------------------------------
>
>



 
Reply With Quote
 
Egbert Nierop \(MVP for IIS\)
Guest
Posts: n/a
 
      05-21-2004
"Anthony M. Davis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?)
>


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...

 
Reply With Quote
 
Anthony M. Davis
Guest
Posts: n/a
 
      05-21-2004
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

"Egbert Nierop (MVP for IIS)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> "Anthony M. Davis" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?)
> >

>
> 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...
>



 
Reply With Quote
 
Anders Altberg
Guest
Posts: n/a
 
      05-21-2004
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

"Anthony M. Davis" <(E-Mail Removed)> wrote in message
news:esIC$(E-Mail Removed)...
> 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
>
>


 
Reply With Quote
 
Cindy Winegarden
Guest
Posts: n/a
 
      05-22-2004
In news: (E-Mail Removed),
Anthony M. Davis <(E-Mail Removed)> wrote:
>>> 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.........
>>> The VFP stored procedure is defined as follows:
>>>
>>> PROCEDURE GetSurcharges
>>> SELECT * FROM surcharge_application
>>> ENDPROC

>>
>> 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?


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


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 MCSD, Microsoft Visual FoxPro MVP
(E-Mail Removed) www.cindywinegarden.com



 
Reply With Quote
 
Anthony M. Davis
Guest
Posts: n/a
 
      05-22-2004
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


"Cindy Winegarden" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In news: (E-Mail Removed),
> Anthony M. Davis <(E-Mail Removed)> wrote:
> >>> 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.........
> >>> The VFP stored procedure is defined as follows:
> >>>
> >>> PROCEDURE GetSurcharges
> >>> SELECT * FROM surcharge_application
> >>> ENDPROC
> >>
> >> 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?

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

>
> 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 MCSD, Microsoft Visual FoxPro MVP
> (E-Mail Removed) www.cindywinegarden.com
>
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Debugging SQL Server 2000 Stored Procedures. lhak ASP .Net 1 10-23-2004 03:30 PM
Putting stored procedures in a dll Soumitra Banerjee ASP .Net 1 02-27-2004 01:46 AM
VB.NET Retrieving Identity form MSSQL2000 without using stored procedures Taras ASP .Net 2 10-05-2003 05:35 AM
Waiting on Stored Procedures Matt ASP .Net 2 07-18-2003 12:46 PM
Re: Your opinion about stored procedures mono ASP .Net 1 07-04-2003 07:55 PM



Advertisments