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

x
?
Solved

Copy rows from SQLCe to SQl Server .net

Posted on 2010-08-29
11
Medium Priority
?
685 Views
Last Modified: 2013-12-05
Hello,

I'm having a bit of trouble copying table data from a SQLCe Database into a SQL Server DB(2005).  The Tables are not identical and do not have identities.  The Key is a created key.  I want all records from the SQLCe table (dbRemote.Cache) but only inserted into certain columns of the Sql Server table(dbMaster.tableMaster).  AFter the insert the dbRemote.Cache data will be removed or copied over to a backup table in the dbRemote so I will move all records each INSERT.  The code is not finding the dbRemote.Cache table in the noted location below.  I've tried several formats including a hard coded path to the db as "IN C:\path to db" like MS Access but no luck.  Any ideas?

Specific Error is: SQLException was unhandled  Invalid object name 'dbRemote.Cache'.

        Dim strConn As String
        strConn = "Data Source=MACENROE;Initial Catalog=dbMaster;Integrated Security=True"
        Dim sqlCon As New SqlConnection(strConn)
        sqlCon.Open()
        Dim sqlCom As New SqlCommand()
        If Not sqlce.State = ConnectionState.Open Then sqlce.Open()
        sqlCom = New SqlCommand("INSERT INTO dbMaster.tableMaster SELECT RecordID, Col1, Col2, Col3, etc FROM dbRemote.Cache", sqlCon)
        sqlCom.ExecuteNonQuery()  <---Invalid object name 'dbRemote.Cache'.

Thanks
0
Comment
Question by:zipnotic
  • 5
  • 4
11 Comments
 

Author Comment

by:zipnotic
ID: 33557813
For development both dbs are on the same machine but the SQLCE will be on a sometimes connected laptop and the Master will be on a server.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33584516
You could not use combined command. You should separate SELECT from dbRemote and INSERT into dbMaster.
Use command parameters on INSERT command and fill it by another SELECT command on different connection to dbRemote database.
0
 

Author Comment

by:zipnotic
ID: 33589321
Thanks for your response.  A clarification:
Will the new CMD execute based on a SQLCE dataadapter or do I somehow need to transfer/copy the data to a SQLDataAdapter Before creating the SQL Insert command?

Dim cmd As SqlCeCommand = Nothing
Dim adp As SqlCeDataAdapter = Nothing



Try
    adpCE = New SqlCeDataAdapter()
   
    Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)

    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adp.InsertCommand = cmd
    '
    adp.Update(ds.Tables(0))
CATCH
END TRY
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:patrikt
ID: 33590731
You have to change connection between two steps. It is not possible with one adapter.
Simpliest solution is to copy data to new dataset and save it with the new adapter.
0
 

Author Comment

by:zipnotic
ID: 33591492
I'm trying to install SP1 and SQL 2008 at the moment so I can't test this now but would this work then:
Thanks for your help.


Dim cmd As SqlCeCommand = Nothing
Dim adp As SqlCeDataAdapter = Nothing



Try
   
   
    Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)
    Dim dsCopy as dataset = ds.Copy()


    Dim adpSQL as New SQLDataAdapter()
    adpSQL.Fill(dsCopy)
    'Can I go straight from original ds like:  adpSQL.Fill(ds) ?????


    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adpSQL.InsertCommand = cmd
    '
    adpSQL.Update(ds.Tables(0))
CATCH
END TRY
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33594316
.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33594325
See // comments in code.
Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)
	//No need to copy.
    //Dim dsCopy as dataset = ds.Copy()


    Dim adpSQL as New SQLDataAdapter()
	//No need to Fill. You already have full dataset.
    //adpSQL.Fill(dsCopy)
    'Can I go straight from original ds like:  adpSQL.Fill(ds) ?????


    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
	//If Emploee ID has to be saved too add it there.
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adpSQL.InsertCommand = cmd
    '
	//You have to set RowState for all rows to Added. In other case no Insert command will be issued by DataAdapter.
    adpSQL.Update(ds.Tables(0))

Open in new window

0
 

Author Comment

by:zipnotic
ID: 33594497
OK, Think I got it.  I only need 1 dataset filled by the SELECT against the CE Database but then reissue/create the new CMD for the SQL EXpress database using parameters.  

Is the RowState automatically set for all rows to be added or is there another command for that?  (Installing SQL Express 2008 has been quite the project....)

Dim conn As New SqlCeConnection("Data Source = MyCEDatabase.sdf")
    Dim ConnEX As New SqlConnection("Data Source = MySQL2008Expressdatabase.ldf")

    ' Create the SelectCommand
    '
    cmd = conn.CreateCommand()
    cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
    adp.SelectCommand = cmd

    ' Populate the dataset with the results from the SELECT statement
    '
    Dim ds As New DataSet()
    adp.Fill(ds)

    ' Create the InsertCommand
    '
    cmd = connEX.CreateCommand()
      //If Emploee ID has to be saved too add it there.
    cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"

    Dim p As SqlParameter = Nothing

    p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
    p.SourceVersion = DataRowVersion.Original

    p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
    p.SourceVersion = DataRowVersion.Original

    adp.InsertCommand = cmd
    '
      //You have to set RowState for all rows to Added. In other case no Insert command will be issued by DataAdapter.
    adp.Update(ds.Tables(0))
;
0
 
LVL 12

Accepted Solution

by:
patrikt earned 2000 total points
ID: 33594863
RowState is set to Unmodified after Fill operation. It will be automaticaly set in case of change in Table, but you do no change to data so you have to set it manualy.
Before Update operation you should iterate all rows and set it Added (will cause InsertCommand) or Modified (will cause UpdateCommand). It is needed to force datatable to thing that rows are new/changed.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
IF you are either unfamiliar with rootkits, or want to know more about them, read on ....
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

926 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