Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 613
  • Last Modified:

Deriving a dataset from the data retrieved via the SQLDatasource control

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
daforga
Asked:
daforga
  • 3
  • 3
1 Solution
 
deanvanrooyenCommented:
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
 
daforgaAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
deanvanrooyenCommented:
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
 
daforgaAuthor Commented:
Thanks so much for the great input.
daforga
0
 
daforgaAuthor Commented:

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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now