none
connect to database Microsoft SQL Server Compact 3.5 sql in C# smart devices RRS feed

  • Question

  • hi all

    I have a problem to store data in a database created in VS 2008 Microsoft SQL Server Compact 3.5 

    I have my code where I keep

    /////////////////inicio guardar 


                  //cadena de conexion
                 string partDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
                 string partBD = System.IO.Path.Combine(partDir, "embarque.sdf");
                 string connectionstring = string.Empty;
                 connectionstring = string.Format(@"Data Source = {0}; Password ='root'", partBD);

                   //File.Delete("embarque.sdf");
                  // string connString = "Data Source='embarque.sdf'; LCID=1033;   Password = root; Encrypt = TRUE;";

                   //objeto de conexion
                 SqlCeConnection con = new SqlCeConnection(connectionstring);//"Data Source = ;Persist Security Info=True");
                   con.Open();
                   SqlCeCommand agregar = con.CreateCommand();
                   //comando sql
                   agregar.CommandText = "INSERT INTO ENVIO values(@RAID,@NPARTE,@CANTIDAD,@VENTANA,@LIDER,@FECHA)";
                   //con.Open();
                   try
                   {

                       foreach (DataRow row in dt.Rows)
                       {
                           agregar.Parameters.Clear();

                           agregar.Parameters.AddWithValue("@RAID", Convert.ToString(row["Raid"].ToString()));
                           agregar.Parameters.AddWithValue("@NPARTE", Convert.ToString(row["Nparte"].ToString()));
                           agregar.Parameters.AddWithValue("@CANTIDAD", Convert.ToString(row["Cantidad"].ToString()));
                           agregar.Parameters.AddWithValue("@VENTANA", Convert.ToString(row["Ventana"].ToString()));
                           agregar.Parameters.AddWithValue("@LIDER", Convert.ToString(row["Lider"].ToString()));
                           agregar.Parameters.AddWithValue("@FECHA", Convert.ToString(row["Fecha"].ToString()));

                           agregar.ExecuteNonQuery();
                       }
                       MessageBox.Show("Datos Agregados");
                   }
                   catch (Exception ex)
                   {
                       MessageBox.Show("Error al Agregar");
                   }
                   finally
                   {
                       con.Close();

                   }

                  ///////////////fin

    but I make an error of the path to open the database

    Tuesday, June 28, 2016 5:03 PM

All replies

  • What is the error and on which line does it occur?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, June 28, 2016 6:24 PM
  • Try this too:

    string partDir = Path.GetDirectoryName( System.Reflection.Assembly.GetExecutingAssembly().Location );
    string partBD = Path.Combine( partDir, "embarque.sdf" );
    string connectionstring = string.Format( @"Data Source = '{0}'; Password ='root'", partBD );
    
    Make sure that the .sdf file exists.

    Tuesday, June 28, 2016 6:35 PM
  • Tuesday, June 28, 2016 7:31 PM
  • I would suggest a fully qualified path to the database in your connection string. If the .sdf is in the same location as the application assembly then you can use Viorel's code.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, June 28, 2016 9:57 PM
  • I no longer make a mistake but does not save me the data.

     /////////////////inicio guardar 
                   //cadena de conexion
    string partDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
    string partBD = System.IO.Path.Combine(partDir, "embarque.sdf");
    string connectionstring = string.Format(@"DataSource={0}; Password ='root'", partBD);

    try
    {
    using (SqlCeConnection con = new SqlCeConnection(connectionstring))
    {
    string ConsultaSQL = "INSERT INTO ENVIO VALUES(@RAID, @NPARTE, @CANTIDAD, @VENTANA, @LIDER, @FECHA)";
            
    foreach (DataRow row in dt.Rows)
    {
                SqlCeCommand cmd = new SqlCeCommand(ConsultaSQL, con);

    cmd.Parameters.Clear();

    cmd.Parameters.AddWithValue("@RAID", Convert.ToString(row["Raid"].ToString()));
    cmd.Parameters.AddWithValue("@NPARTE", Convert.ToString(row["Nparte"].ToString()));
    cmd.Parameters.AddWithValue("@CANTIDAD", Convert.ToString(row["Cantidad"].ToString()));
    cmd.Parameters.AddWithValue("@VENTANA", Convert.ToString(row["Ventana"].ToString()));
    cmd.Parameters.AddWithValue("@LIDER", Convert.ToString(row["Lider"].ToString()));
    cmd.Parameters.AddWithValue("@FECHA", Convert.ToString(row["Fecha"].ToString()));

    con.Open();

    cmd.ExecuteNonQuery();
                
                con.Close();
      }
          
    }
       
    MessageBox.Show("Datos Agregados");
        
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message);
    }
                  ///////////////fin guardado

    Wednesday, June 29, 2016 8:38 PM
  • Hi HARIEKZ,

    >>I no longer make a mistake but does not save me the data.

    I would suggest that you could full SQL statement (as below) and check if it works for you.

    string ConsultaSQL = "INSERT INTO ENVIO (RAID,NPARTE,CANTIDAD,VENTANA,LIDER,FECHA) VALUES(@RAID, @NPARTE, @CANTIDAD, @VENTANA, @LIDER, @FECHA)";

    In addition, it the issue is still exist, could you please provide a simple demo and related database via onedrive. we'll reproduce your issue on our side and try to find a solution to resolve it.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 7, 2016 1:40 AM
    Moderator