Solved

Deriving a dataset from the data  retrieved via the SQLDatasource control

Posted on 2006-06-28
6
587 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 500 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
When should I use a controller? 3 34
Reading the Web.Config using IIS 7.5? 4 36
imap mails 1 22
insert value of checklistbox checked 4 26
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…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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