Solved

Copy rows from SQLCe to SQl Server .net

Posted on 2010-08-29
11
675 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

830 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