how to insert a new line in a table of a .sdf file smart device application

bacem smari

Hi everybody, I am trying a smart device application. the problem is after i execute a querry that should insert a new line in a table, i go to check if the line existe really in the table then i don't find it. this is the code i used. Thank you for your help in advance.

private void menuItem4_Click(object sender, EventArgs e)
{
string wCS = @"Data Source =\Storage Card\ModeDifféré\BaseGmaoLocale.sdf;";
SqlCeConnection sqlceconn = new SqlCeConnection(wCS);
SqlCeCommand command2 = sqlceconn.CreateCommand();
command2.CommandText = "INSERT INTO compteurs2 ([com], [quantite],[datequantite] ) Values(@com,@qtte,SYSDATE)";
command2.ExecuteNonQuery();

        //Add the parameters
        SqlCeParameter com = new SqlCeParameter("@com", SqlDbType.NVarChar);
        com.Value = comComboBox.SelectedItem.ToString();

        command2.Parameters.Add(com);
        SqlCeParameter qtte = new SqlCeParameter("@qtte", SqlDbType.Float);
        qtte.Value = float.Parse(textBox1.Text);

        command2.Parameters.Add(qtte);
    }

Stanciu Vlad

I think you should execute the command afther you add the parameters to it, or else the insert will fail (as it has so far) havning nothing to insert. Something like this:

private void menuItem4_Click(object sender, EventArgs e) {
string wCS = @"Data Source =\Storage Card\ModeDifféré\BaseGmaoLocale.sdf;";
SqlCeConnection sqlceconn = new SqlCeConnection(wCS);
SqlCeCommand command2 = sqlceconn.CreateCommand();
command2.CommandText = "INSERT INTO compteurs2 ([com], [quantite],[datequantite] ) Values(@com,@qtte,SYSDATE)";
//command2.ExecuteNonQuery(); -- a little mistake

SqlCeParameter com = new SqlCeParameter("@com", SqlDbType.NVarChar);
com.Value = comComboBox.SelectedItem.ToString();
command2.Parameters.Add(com);

SqlCeParameter qtte = new SqlCeParameter("@qtte", SqlDbType.Float);
qtte.Value = float.Parse(textBox1.Text);
command2.Parameters.Add(qtte);

command2.ExecuteNonQuery();
}

Oh and, I an not 100% sure, but I think that SYSDATE is not supported in SQL CE, the one that should work is GETDATE().

I have no smart signature yet...

bacem smari

thank you for your suggestion but it still doesn't work.

Stanciu Vlad

What is the code you run now and what si the result (any exceptions?).

I have no smart signature yet...

bacem smari

this is the code:

private void menuItem4_Click(object sender, EventArgs e)
{
DateTime t = DateTime.Now;
string wCS = @"Data Source =\Storage Card\ModeDifféré\BaseGmaoLocale.sdf;";
SqlCeConnection sqlceconn = new SqlCeConnection(wCS);
SqlCeCommand command2 = sqlceconn.CreateCommand();
command2.CommandText = "INSERT INTO compteurs2 ([com], [quantite],[datequantite] ) Values(@com,@qtte,@dat)";

        //Add the parameters
        SqlCeParameter com = new SqlCeParameter("@com", SqlDbType.NVarChar);
        com.Value = comComboBox.SelectedItem.ToString();

        command2.Parameters.Add(com);
        SqlCeParameter qtte = new SqlCeParameter("@qtte", SqlDbType.Float);
        qtte.Value = float.Parse(textBox1.Text);

        command2.Parameters.Add(com);
        SqlCeParameter dat = new SqlCeParameter("@dat", SqlDbType.Float);
        dat.Value = t;

        command2.Parameters.Add(dat);
        command2.ExecuteNonQuery();
    }

and i didn't see any exception.

Stanciu Vlad

I think that: a) you connection is not opened b) yout command is not attached to that connection c) adding twice the com parameter does not solve anything d) the @dat parameter should by datetime Most probably this code should work:

private void menuItem4_Click(object sender, EventArgs e)
{
// define the connection string
string wCS = @"Data Source =\Storage Card\ModeDifféré\BaseGmaoLocale.sdf;";

// create a connection - this way it will be disposed and closed at the en of the using block
using(SqlCeConnection sqlceconn = new SqlCeConnection(wCS))
{
// define the insert statement
string insertString = "INSERT INTO compteurs2 ([com], [quantite],[datequantite] ) Values(@com,@qtte,@dat)";

  // create the command based on the insert string and the connection
  SqlCeCommand command2 = sqlceconn.CreateCommand(insertString, sqlceconn);
  
  // define the parameters
  SqlCeParameter com = new SqlCeParameter("@com", SqlDbType.NVarChar);
  SqlCeParameter qtte = new SqlCeParameter("@qtte", SqlDbType.Float);
  SqlCeParameter dat = new SqlCeParameter("@dat", SqlDbType.DateTime);

  // attach values to these parameters
  com.Value = comComboBox.SelectedItem.ToString();
  qtte.Value = float.Parse(textBox1.Text);
  dat.Value = DateTime.Now;

  // attach the parameters to the command
  command2.Parameters.Add(com);
  command2.Parameters.Add(qtte);
  command2.Parameters.Add(dat);

  // open the conenction
  command2.Connection.Open();
  // execute the insert
  command2.ExecuteNonQuery();

}
// note that here the connection is closed
}

I have no smart signature yet...

bacem smari

thank you but for the line:

SqlCeCommand command2 = sqlceconn.CreateCommand(insertString,sqlceconn);

the following error occurs: Error 1 No overload for method 'CreateCommand' takes '2' arguments C:\Users\Admin\Documents\Visual Studio 2008\Projects\GMAOMobile\ModeDifféré\Energie.cs 107 41 ModeDifféré what is the problem?

Stanciu Vlad

My bad, try

SqlCeCommand command2 = sqlceconn.CreateCommand();
command2.CommandText = insertString;

or

SqlCeCommand command2 = new SqlCeCommand(insertString,sqlceconn);

Both do the same thing.

I have no smart signature yet...

bacem smari

the error was repaired but nothing happened in data base. There is is no line inserted.

Stanciu Vlad

How do you check if your line has been inserted?

I have no smart signature yet...

bacem smari

i open the table from visual studio after closing the application and i check if there is a new line inserted.

Stanciu Vlad

Most probably the table opened from visual studio is just a local copy of the database file. Your operation is made on the smart device simulator, on that database. You can download the file from the smart device and check the row is inserted, or you can execute a select command on the device to check the row count.

I have no smart signature yet...