Link to home
Create AccountLog in
Avatar of TechNeck
TechNeck

asked on

Problem Updating SQL Table with a Dataset generated from an OracleDataAdapter

We have several Oracle Databases at multiple locations, so in order to keep them configured with the same settings, we have created a DLL to read a Dataset from Oracle. That is working fine, but I am now trying to use this object to populate a SQL2000 Table with that Dataset.

Thought it would be easy to create the Dataset with an OracleDataAdapter and then Update using a SQLDataAdapter.

Following is the code:

        Dim spec As New Specification
        Dim oraDS As New DataSet
        Dim sqlDS As New DataSet

        Set_Oracle()    'Sets Oracle DB Connection
        Connect_Oracle()     'Connects to Oracle
        oraDS = spec.GetDataSet()     'Calls object to get DataSet
        dgResults.SetDataBinding(oraDS, "Specifications")    'Write Dataset to Datagrid to ensure data is there
        sqlDS = oraDS     'Sets the SQL Dataset equal to the Oracle Dataset

        Connect_SQL()    'Connects to SQL
      
        Dim sqlDA As New SqlDataAdapter("Select * FROM NewDS", ns.ConnectionString)   'Creates SQL DataAdapter

        dgSQLResults.SetDataBinding(sqlDS, "Specs")     'Write the SQL Dataset to a Datagrid to verify data

        sqlDA.Update(oraDS.Tables(0))      'Updates the SQLDataAdapter with the Oracle DataSet


I get no error messages, but I also do not update the SQL Database with the changes.
One thing to note: The SQL Table does not have any data in it currently and I am trying to populate it with the data from Oracle.

PLease help!!
ASKER CERTIFIED SOLUTION
Avatar of Dabas
Dabas
Flag of Australia image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Dabas is correct.  The dataset keeps track of the status of the rows in the dataset and will only update/insert/delete those that have changed since the last fill.

If you want to insert them all, then just change the rowstate on each row in the table to New.  Then do the update.