Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Deriving a dataset from the data  retrieved via the SQLDatasource control

Posted on 2006-06-28
6
Medium Priority
?
608 Views
Last Modified: 2012-05-05
I have retrieved data from an Oracle database using a SqlDataSource control.  I use it as a datasource for a gridview control.   I also have a 3rd party control that requires a Dataset for binding .  Can I derive a dataset from the data  retrieved via the SQLDatasource control?  Thanks
daforga
0
Comment
Question by:daforga
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
deanvanrooyen earned 2000 total points
ID: 17005083
i thought there would be an easier way, there might be...
but this works - the gridview1 is set to the sqldatasource


        System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(SqlDataSource1.SelectCommand, SqlDataSource1.ConnectionString);
        DataSet ds = new DataSet();

        sda.Fill(ds);

        this.GridView2.DataSource = ds;
        this.GridView2.DataBind();
0
 

Author Comment

by:daforga
ID: 17005541
deanvanrooyen,
Thanks for the timely reply.  
Your approach seems very sound.  However I get an error:
                     ORA-01008: not all variables bound
This is due, I think, to the fact that my origional SQLDatasource command text is parameterized.  I am now wrestling with trying to add that to your proposed formula.  

Here's  the line as I have it :

        Dim da As OracleClient.OracleDataAdapter = New OracleClient.OracleDataAdapter _
                            (SqlDataSource1.SelectCommand, SqlDataSource1.ConnectionString)

In the source of my aspx page, the control parameter is added thusly:
               <SelectParameters >
                    <asp:ControlParameter Name="dept_no" ControlID ="txtSelectDept"  
                            PropertyName="Text" />
                </SelectParameters>
This invisible textbox is filled from an indexchanged event in a grid od company departments.  It drives the query that fills the second grid with specific data.

Any ideas on How to add the input parameter to this mix?

daforga

0
 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 17014623
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:deanvanrooyen
ID: 17014756
i tried programtically changing the sql datasoruce select paramters and got errors so I tried this rather, bit more work but it works and give the flexibility of changing the sql to whatever you need plus you can add as many parameters as you have @ place holders in the sql. this code should also be safe from sql injection.

        string strcon = SqlDataSource1.ConnectionString;

        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock FROM [Alphabetical list of products] where supplierid = @id", new System.Data.SqlClient.SqlConnection(strcon));

        sqlCmd.Parameters.Add("@id", SqlDbType.Int ).Value  = GridView1.Rows[GridView1.SelectedIndex].Cells[3].Text;

        System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
        sda.SelectCommand = sqlCmd;
        DataSet ds = new DataSet();
        sda.Fill(ds);


        this.GridView2.DataSource = ds;
        this.GridView2.DataBind();


good luck
0
 

Author Comment

by:daforga
ID: 17036873
Thanks so much for the great input.
daforga
0
 

Author Comment

by:daforga
ID: 17036890

This also works in Oracle adapter if you substitute the @ for a :
Thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Screencast - Getting to Know the Pipeline
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question