Solved

Copy rows from SQLCe to SQl Server .net

Posted on 2010-08-29
11
666 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 500 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Missing Money orders... 6 70
DataGridview Currency Formating? 22 38
Connecting two servers 30 47
BULK INSERT most recent CSV 19 19
Know what services you can and cannot, should and should not combine on your server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now