Click here to Skip to main content
15,888,733 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi

I am doing some basic insert to database.sdf file using tables from EF,all connections work but am having problems inserting,deleting,updating values to the database. Y is that?What am i misng?

Code to insert
C#
string strConnection = ConfigurationManager.ConnectionStrings["Database1Entities"].ConnectionString;
           using (var conn = new SqlCeConnection(string.Format("Data Source=Database1.sdf;", strConnection)))
           {
               conn.Open();

               try
               {
                   SqlCeCommand dbCmd = new SqlCeCommand();

String sqlAddNew = "INSERT INTO tbl_Credantials (password,username ) Values(@password,@username)";
               dbCmd = new SqlCeCommand(sqlAddNew, conn);


dbCmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = txtuserID.Text;
dbCmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = txtUsername.Text;

                   dbCmd.ExecuteNonQuery();
                                 }
               catch (SqlCeException)
               {
                   throw;
               }
               finally
               {
                   if (conn.State == ConnectionState.Open) conn.Close();
               }
           }



App.Config file

XML
<configuration>
  <connectionStrings>
    <add name="Database1Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=|DataDirectory|\Database1.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>
Posted
Comments
Wendelius 13-Aug-12 14:11pm    
What problem are you facing? If you're gettin an error, please post the whole message.
Anele Ngqandu 13-Aug-12 15:01pm    
ther is no error Mika...it just dont insert in the database

Check that the ExecuteNonQuery actually makes the insert. The method returns an int which tells you how many records were affected by the command. So in your INSERT the results should be 1 (one record added)

Another thing you can check is that your connection string isn't pointing to an SDF file which would be overwritten when you run the application. For example if the SDF is used from bin/debug directory and you have included the SDF in your project, the compiler may overwrite the file when you recompile the application. This would cause you to loose all changes that may have been done earlier.
 
Share this answer
 
Comments
Anele Ngqandu 13-Aug-12 16:06pm    
i c now...intresting, now y did it copy the database to the bin??because i created the database nd put it with the rest of the pages.thanx thou..
Wendelius 13-Aug-12 23:34pm    
You're welcome :)
Volynsky Alex 13-Aug-12 18:05pm    
Good answer
Wendelius 13-Aug-12 23:34pm    
Thanks :)
Volynsky Alex 14-Aug-12 5:00am    
You are welcome
First ensure your connection string is right that points your database.You used SqlDbType.NVarChar...so you need to ensure the text length limit of your textboxes.
Instead of that you can use SqlDbType.VarChar.
 
Share this answer
 
Comments
Anele Ngqandu 13-Aug-12 15:41pm    
connection string works, points to my database, it identifies the error when i change column name. the dbtype limit is 100 for the nvarchars so i dont think it has to do with the text length limit of textboxes.also i changed the dbtype to SqlDbType.VarChar but nothing seems to work.
Volynsky Alex 13-Aug-12 18:05pm    
Good answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900