CODEWITHSUNDEEP
c#sql-server-ce
user1366440
user1366440

Reputation: 375

SQL Server Compact Insertion

i want to insert data into sql server Compact edition the database table screenshot is Here >>> i Want to add data in users the addition script is as follows

SqlCeConnection Con = new SqlCeConnection();
Con.ConnectionString = "Data Source = 'Database.sdf';" +
                       "Password='Password';";
Con.Open();
int Amount=Convert.ToInt32(AmBox.Text),
Code=Convert.ToInt32(MCode.Text),
Num=Convert.ToInt32(MNum.Text);
string Name=Convert.ToString(NBox.Text),
FName=Convert.ToString(SOBox.Text),
Address=Convert.ToString(AdBox.Text);

SqlCeCommand Query =new SqlCeCommand("INSERT INTO Users VALUES " + 
                        "(++ID,Name,FName,Address,Code,Num,Amount)",Con);
Query.ExecuteReader();

When it runs it generates an error SAYING "The column name is not valid [Node Name (if any) =,Column name=ID ]

I don't figure out the problem kindly tell me thanks!

Upvotes: 2

Views: 4099

Answers (1)

Steve
Steve

Reputation: 216303

You should change your code to something like this

using(SqlCeConnection Con = new SqlCeConnection("Data Source = 'Database.sdf';" + 
                                                "Password='Password';")
{ 
    Con.Open(); 
    SqlCeCommand Query = new SqlCeCommand("INSERT INTO Users " + 
                             "(Name,FName,Address,MCode,MNum,Amount) " +
                             "VALUES (@Name,@FName,@Address,@Code,@Num,@Amount)",Con); 

    Query.Parameters.AddWithValue("@Name", NBox.Text);
    Query.Parameters.AddWithValue("@FName", SOBox.Text)); 
    Query.Parameters.AddWithValue("@Address",AdBox.Text)); 
    Query.Parameters.AddWithValue("@Code", Convert.ToInt32(MCode.Text));
    Query.Parameters.AddWithValue("@Num", Convert.ToInt32(MNum.Text));
    Query.Parameters.AddWithValue("@Amount" , Convert.ToInt32(AmBox.Text));
    Query.ExecuteNonQuery(); 
}
  • The using statement guarantees the correct disposing of the connection
  • The Parameter collection avoid Sql Injection Attacks and quoting problems
  • Use of ExecuteNonQuery because this is an insert query.
  • Removed the ++ID, it is not a valid value to pass to the database

If the ID field is an Identity column, then you don't pass any value from code, but let the database calculate the next value.
Also, I'm not sure you really need the single quote in your connection string around the data source and password keys.

EDIT --- Sometimes the .SDF database could be located in a different folder. (Modern operating systems prevent writing in the application folder).
In this case is necessary to set the path to the SDF file in the connection string.
For example, the SDF could be located in a subfolder of the C:\ProgramData directory.

string conString = "Data Source=" + 
        Path.Combine(
               Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData),
               "MyAppData\\database.sdf") + ";Password=yourPassword;";

Upvotes: 6

Related Questions