programmatic access to sqldatasource dataset
i'd like to take advantage of all the benefits of the new sqldatasource control to do quick queries & bindings, but also retain the flexibility of the previous ways of data access (i.e. sqlcommands/sqldataadapters) to fill datasets. is there a way to have programmatic access to the dataset that is filled after a sqldatasource queries a db? i dont want to display all the columns queried in my gridview, but i want that data available for further manipulation without having to run another query. am i going about this the wrong way, or have i just not stumbled upon the solution?
thanks,
david
Did you ever find your solution for this? I have the same question.
Mitch
nope, no luck finding a solution...yet. i find it interesting that over 200 people have read my post but noone has yet posted a solution (much less a suggestion). this tells me that we aren't the only ones out there trying to get this to work. pains of living on the bleeding edge i suppose. :-| i've checked several .net 2.0 books but none go any deeper into the gridview/sqldatasource/dataset relationship than how to initially set one up...
You can certainly do something like this. I programmatically created an SqlDataSource, called the Select Method on it, retrieved the DataView, and converted the results to a DataTable that I can manipulate as I see fit.
string connectionString = "...Northwind Connection String...";
string selectSql = "SELECT [CategoryID], [CategoryName] FROM [Categories]";
DataSourceSelectArguments args = new DataSourceSelectArguments();
SqlDataSource dataSource = new SqlDataSource(connectionString, selectSql);
DataView view = (DataView)dataSource.Select(args);
DataTable table = view.ToTable();
Does that help?
Regards,
Dave
Hi David,
Did you take a look at the method SqlDataSource.GetView method? This method returns the underlying SqlDataSourceView that holds the data. Depending on what you are trying to accomplish, you can either call the SqlDataSource.Select method directly and manipulate the data you get or you could try manipulating the underlying SqlDataSourceView in a handler for the SqlDataSource.Selected event. You could also set the EnableCaching property to true for the SqlDataSource which will cache the data for you so that you don't have to hit the db again and then use the GetView method to get the view and modify the displayed data using the FilterExpression property.
Does that help?
Imran.
thanks for the suggestion. ive looked at it for a couple of days now but have not been able to come up with a solution using the GetView method. have you had any success with the implementation you describe? if you could post some sample code, that would be great.
thanks again
The post by David Hayden worked for me. I was able to use my existing SQLDataSource object and use its resultset for a DataList.
string
s_sql = ""; s_sql =
"SELECT Clients.*,CFARS.*,Therapists.* " +"FROM CFARS INNER JOIN Therapists " +"ON CFARS.TherapistID = Therapists.TherapistID INNER JOIN Clients " +"ON CFARS.ClientID = Clients.ClientID " +"WHERE Clients.SSN = '" + Request.QueryString["SSN"] + "' AND " +"CFARS.CFARSID = '" + Request.QueryString["CFARSID"] + "'";ClientDetailSource.SelectCommandType =
SqlDataSourceCommandType.Text;ClientDetailSource.SelectCommand = s_sql;
DataSourceSelectArguments args = newDataSourceSelectArguments();
DataView view = (DataView)ClientDetailSource.Select(args);
DataTable table = view.ToTable();
string
fullDate = table.Rows[0].ItemArray[7].ToString();This is called in a separate function from page load, of course this is only the stripped out essentials and not the whole function. All I needed to get out of the result set was the date field to place elsewhere on the page aside from the DataList I was already using.
Thank you Mr. Hayden.
Had the same problem. Cobbled this together from other posts and it looks promising. I have a DetailsView fed by a SqlDataSource and I wanted one field value that was in the returned Select query from a stored procedure but was not bound or displayed in the DetailsView and I couldn't find a way to get at it to use elsewhere.
First I declared an output parameter to the SelectParameters tag (input parameters omitted)
<SelectParameters><asp:Parameter Name="return_value" Direction="output" Type="Int32"/><SelectParameters>
Then I declared an output parameter in the stored procedure @return_value int OUTPUT
Then filled the variable after my full Select statement ran in the stored procedure.
SELECT conID, compID, propID......<long select statement to feed the DetailsView>
SELECT @return_value = compID from tblWhatEver WHERE whatever = @whatever
Then in my SqlDataSource_Selected event I pulled that returned value out.
dim i as integer = e.Command.Parameters("@return)value").Value
I hope this is close to what you needed.
Mike MCAD
PS If anyone finds a way to pull the full dataset out of a bound SqlDataSource control in its normal, automated select I'd sure like to see it.
Small update to the above. Because the DetailsView depends on the selection from a dropdownlist in which I put one of those dummy lines to seed it - "Please Select a Contact" the SqlDataSource_Selected event came in with a null value for that parameter value when page loaded.
I had to protect from that possiblity in the SqlDataSource Selected event like this
If Not IsDBNull(e.Command.Parameters("@return_value").Value) Then
Hi, I'm pleased this worked for you....I have been struggling with the same issue in getting data out of a programmatic SQL select statement....like....
ProfileDataSource.SelectCommandType = SqlDataSourceCommandType.Text
ProfileDataSource.SelectCommand =
"SELECT ConfID FROM ADMIN WHERE Conference_Name = PDValue" ..the Admin table only has 2 columns: ConfID and Conference_Name...
I don't understand your
DataSourceSelectArguments args = new
DataSourceSelectArguments();statement....I tried just pasting it in but got errors....must be missing something
All I want to do is get the ConfID value.......I'm a real novice but have spent hours trawling through the forums to try and find out how to do what I thought was a pretty simple thing....hope you or others can help..
Here is a simple way to get to the data stored in an SqlDataSource that is setup using the default dataset access type. This example is based on a dropdownlist that is based on a "select * from table" that only uses 1 column for the text/value on the drop down, but we want to get to the other columns for auto-population of various form fields.
We call this code when the dropdownlist changes index.... this is sample code, just dumps to the web output, but you get the idea...
Protected Sub SetModel(ByVal sender As System.Object, ByVal e As System.EventArgs)
' Get the calling drowdown that triggers this subroutine
'
Dim this As DropDownList = sender
' Setup my dataview
'
Dim dv As System.Data.DataView = CType(Me.SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)
' Get the specific row based on the pulldown item selected
'
Dim dr As DataRow = dv.Table.Rows(this.SelectedIndex)
' Show the matching data from the SQL data retrieval
'
Response.Write("Mfg: " + dr.Item("name") + "<br>")
Response.Write("Model: " + dr.Item("social") + "<br>")
Response.Write("Category: " + dr.Item("dob") + "<br>")
End Sub
HTH,
Lance Cleveland, Founder
Charleston Software Associates (charlestonsw.com)
Cyber Sprocket Labs (cybersprocket.com)
If I understand correctly, the desired solution would be some method of using
ProtectedSub
mySqlDataSource_Selected(ByVal
sender AsObject
, ByVal
e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
mySqlDataSource.SelectedDim
dv As
SqlDataSourceView = ?'''' Modify data here and then allow the normal binding process to occur
EndSub
I personally am also looking for the same solution, and am currently trying to figure this out myself. Someone had mentioned a "GetView" function that does not appear to exist - or I'm looking in the wrong place for it. Any sample code on that usage would be appreciated.
Hello,
I am pretty new to coding and am running into a problem. I have tried looking thru the forums and this thread is as close to what I need as I have been able to find.
Here is the issue.
I have 2 db's one is sql and one is oracle. I have the Gridview pulling the data from the sqlserver db.
I am using the rowdatabound to get my select parameter this select statement just verifies that the data is in the oracle table.
My problem is I am having problems getting the data out o fthe view to match against the cell in the row.
It keeps on telling me I need to use the New keyword ont he line "Dim dr As
System.Data.DataRow = dv.Table.Rows(1)"If anyone could take a peek at this I would be grateful. :)
Jaden
The code
ProtectedSub GridView2_RowDataBound(ByVal sender AsObject, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
Dim p AsNew Parameter
p.Name = "shipid"
p.Direction = Data.ParameterDirection.Input
p.Type = TypeCode.String
SqlDataSource2.SelectParameters.Add(
"p"
, e.Row.Cells(4).Text.ToString)SqlDataSource2.SelectCommand =
"select shipment_num from apps.rcv_shipment_headers where shipment_num = :shipid"
Dim
dv As
System.Data.DataView = CType
(Me
.SqlDataSource2.Select(DataSourceSelectArguments.Empty), System.Data.DataView)Dim
dr As
System.Data.DataRow = dv.Table.Rows(1)TextBox2.Text = dr.Item(1)
EndSub
Quick and dirty solution to this is to do the following...
in the HTML...
<divid="productname">
<
asp:
LabelID="Label1"runat="server"Text='
<%# this.UpdateTitle(Eval("Name")) %>'></
asp:
Label>
</
div>
In the Code...
protectedstring
UpdateTitle(object
newValue){
this
.Title = newValue.ToString();return
newValue.ToString();}
DataView view = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
DataSet ds = new DataSet();
ds.Tables.Add(table);
