programmatic access to sqldatasource dataset

programmatic access to sqldatasource dataset

• morphius1

  Did you ever find your solution for this I have the same question.

  Mitch

  
  
  

• Larry Smith

  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

  Protected Sub GridView2_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)

  Dim p As New 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)

  End Sub

  
  

• Mitch5713

  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)
  
  

• m0

  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.
  
  

• wish1267

  DataView view = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
  
  DataTable table = view.ToTable();
  DataSet ds = new DataSet();
  ds.Tables.Add(table);
  
  
  

• MikeTennor

  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.

  
  
  

• Andrew Maddison

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

• Jonesj3599

  Quick and dirty solution to this is to do the following...

  in the HTML...

  <div id="productname" >

  <asp:Label ID="Label1" runat="server" Text='<%# this.UpdateTitle(Eval("Name")) %>'></asp:Label>

  </div>

  In the Code...

  protected string UpdateTitle(object newValue)

  {

  this.Title = newValue.ToString();

  return newValue.ToString();

  }

  
  

• sbogollu

  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 = new DataSourceSelectArguments();

  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.

  
  

• fripper

  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

  
  
  

• yudee

  If I understand correctly, the desired solution would be some method of using

  Protected Sub mySqlDataSource_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles mySqlDataSource.Selected

  Dim dv As SqlDataSourceView =

  '''' Modify data here and then allow the normal binding process to occur

  End Sub

  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.

  
  

• smigger666

  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

  
  
  

• Christian Arnold

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

  
  

• mranzani

  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

  
  
• programmatic access to sqldatasource dataset