Get the ODBC table and paste it into the SQL Server CE database

Question

I have an ODBC database connection for which I need one data table. A table has about 20 rows and a couple of thousand rows of data.

I intend to insert this table into my local SQL Server CE database, where I can use it for future reference. Both connections have been tested and work.

My attempt was to simply insert a single column so that everything was simple (I am new to C #, programming and stackoverflow).

OdbcConnection c = new OdbcConnection(ConnectionString1); SqlCeConnection d = new SqlCeConnection(ConnectionString2); c.Open(); d.Open(); string sqlC = "SELECT * FROM ODBCTABLE WHERE ODBCCOLUMN='12345'"; OdbcCommand commandC = new OdbcCommand(sqlC, c); string sqlD = "INSERT INTO SQLCETABLE(SQLCECOLUMN) VALUES (@sql)"; SqlCeCommand commandD = new SqlCeCommand(sqlD, d); OdbcDataReader reader = commandC.ExecuteReader(); while (reader.Read()) { string x = reader[0].ToString(); commandD.Parameters.Add("@sql",SqlDbType.NVarChar, 5).Value = x; commandD.ExecuteNonQuery(); } c.Close(); c.Dispose(); d.Close(); d.Dispose(); 

I get an error. An SqlCeParameter with this name is already contained in this SqlCeParameterCollection.

• Why is this wrong?
• Is there any way to fix this?
• Are there any better ways to do this translation? (I believe sqlbulktransfer does not exist for odbc)
• Being my first post on Stackoverflow, did I start posting the question correctly?

Answer 1

Change this part of your code

 commandD.Parameters.Add("@sql",SqlDbType.NVarChar, 5); while (reader.Read()) { string x = reader[0].ToString(); commandD.Parameters["@sql"].Value = x ; commandD.ExecuteNonQuery(); } 

The problem arises because in each loop you repeat adding the same named parameter to the collection, which leads to the error above.
Moving the parameter creation outside the loop and updating only the value inside the loop should fix the error.

Yes, I think you posted the question correctly.