Gridview & SQL data source

I have a few Stored procedures that return values, some need parameters passing and others don't.

Up to now to access that data in a Web App I have called this procedure using VB after seting up a data block etc.

However I notice that the Web Developer express edition has some tools that look like they should help. I have used the in-built tools to create a SQL data source linked to a details view. I then specified that this should connect to my database (sql2005 hosted) and then specified Custom SQL or Stored procedure.

From the drop down I can then select the SP that purely returns values. When I try to test this query I get an error to say that the query did not return any data tables.

Is there a way to get values returned from a SP in this way?

Regards

Clive

You can use events like (Updating, Updated, Selecting, Selected etc) to do your cutomization. For example, if your method returns an output parameter then:

Sub OnDSUpdatedHandler(ByVal sourceAs Object,ByVal eAs SqlDataSourceStatusEventArgs)If e.AffectedRows > 0Then' Perform any additional processing, such as setting a status label. Label1.Text = Request.LogonUserIdentity.Name & _" changed user information sucessfully!" dim outputvalue asString outputvalue = e.Command.Parameters("yourparamname").Value.ToString()Else Label1.Text ="No data updated!"End If End Sub'OnDSUpdatedHandler

As you can see, I haven't done any kind of error checking. Please ensure that your code does. If you need more help, reply to the post explaining the problem.

|||

I think my problem is earlier than this stage to be honest.

For instance I have a SQL 2005 Stored Procedure that returns values from a table I can run this in SQL server itself and it works fine.

When definign this SP as a datasource using the detailsview object in Visual Web Developer I get the error I mentioend above when runnign the test option while I am stil in the wizard. So it looks liek some problem with callign the SP, yet it works OK natively.

|||

DetailsView, FormView, Gridview etc controls are designed to show a record/records. So, if you are using a SqlDataSource that just returns a single value, you cannot bind that to abovementioned controls. Can you explain why do you need to have a Detailsview that shows only single value?

Whenever asking a question, please add as much info as possible. Otherwise, we have to guess other parts (as you can see from my previous post) which is obviously not what you are looking for. Provide more info about your problem/scenario.

|||

I have a stored procedure that takes no paramteres but returns some values which are calculated from a number of tables to returns some summary results. if I execute the SP from SQL manager I get 5 values returned which appear as output parameters.

I was hoping to be able to link these quickly and easily into a web page using a SQL data source linking to this SP as it's datasource.

I was hoping that the details view would then display the values returned.

There maybe an easier or better way to do this. I am a beginner with all this, and wanted to explore how to use a stored procedure as a datasource.

Regards

Clive

|||

If the stored procedure returns resultset (table with columns and rows) then it would show nicely in the DetailsView, GridView etc. I don't think these controls have builtin ability to look for output parameters and bind those values. I think you should do this programmatically.

Connect to db, run the storedproc, get the results. Then you can either manually populate the values in controls or you can create a collection of values and bind it to a dataaware control. If you need sample code, then reply to the post mentioning your preference.