locked
How to insert new row in client database RRS feed

  • Question

  • Hello,

    I followed the tutorial "Tutorial: Synchronizing SQL Server and SQL Server Compact" (http://msdn.microsoft.com/en-us/library/ff928494.aspx) everything worked fine.

    After the last step, when I want to add an new row in the client database (dbf database file) I receive the following error "The number of columns in the query and the table must match. [ Number of columns in query = 3, Number of columns in table = 6]"

    I know that the synk framework has inserted Sync columns at the end of the tables I want to edit on the client device, but do I have to manage these columns ? And if yes, what must I exactely fill in these cols ?

    Thank you in advance for your answer

    J-D Gasser

    Friday, November 22, 2013 9:00 AM

Answers

  • Try explicitly specifying the columns for the insert: Insert into Products(id, name, listprice) values(6, 'test', 67)
    Monday, December 2, 2013 2:16 PM

All replies

  • which step in the tutorial are you having issues?

    and what columns that has been added are you referring to?

    Thursday, November 28, 2013 1:31 AM
  • Hello June,

    In fact I just want to add new rows in the SQL Server Compact database which is synchronized with the SQL Server Database and it is not possible.

    I try to do it once every step of the tutorial has been followed. What I want is just to be able to take the database with me on my mobile device and be able to add new items in the products table, then sync these new items with the database which is on my desktop PC.

    In order to add new rows I use the following lines of code:

    clientConn.Open();
    using(SqlCeCommand l_scClient = new SqlCeCommand("INSERT INTO Products VALUES (6, 'Test', 67)", clientConn))
    l_scClient.ExecuteNonQuery();
    clientConn.Close();

    The thrid line of code generates "The number of columns in the query and the table must match" Exception.

    I dont want to change the structure of teh database, just add new rows in the Products table. I'm sure it is possible, but how ?

    Thank you in advance for your answer.

    Greetings

    J-D Gasser


    Thursday, November 28, 2013 9:10 AM
  • can you post your table structure before and after you provisioned it for sync?

    you shouldn't have to do anything special to add records to a table that's been provisioned for sync

    Saturday, November 30, 2013 2:09 AM
  • Hello,

    here is a screen copy of SQL Server Management Studio.

    You will see that on the left panel, in the table structure we can only see 3 columns, the three that was created with the database. But if I do a SELECT request on the datatable, then I can see three new columns which were added by the client provision process.

    Products Table structure after client provision.

    Thank you in advance for your help.

    Greetings

    J-D Gasser

    Monday, December 2, 2013 7:29 AM
  • Try explicitly specifying the columns for the insert: Insert into Products(id, name, listprice) values(6, 'test', 67)
    Monday, December 2, 2013 2:16 PM
  • Hello June,

    Thank you ! It works.

    Greetings

    J-D Gasser

    Monday, December 2, 2013 3:01 PM