C# and SQLCE Insert statement not inserting data into database
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: C# and SQLCE Insert statement not inserting data into database

  1. #1
    Join Date
    Nov 2008
    Posts
    9

    C# and SQLCE Insert statement not inserting data into database

    I'm using Microsoft Visual Studio 2005 and SQLCE to create a windows mobile 5.0 application. I've got this working but the "INSERT" statement doesn't actually insert the data into the database. It doesn't produce errors or anything.

    HELP!!! please

    Thanks

    Code:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlServerCe;
    using System.IO;
    using System.Reflection;
    
    namespace DeviceApplication1
    {
        public partial class frmCars : Form
        {
            string connectionString = ("Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) + "\\CarsGalore.sdf;Persist Security Info=False;");
    
            public frmCars()
            {
                InitializeComponent();
                fnGetConnectedToDatabase();
            }
    
            private void mnuContinue_Click(object sender, EventArgs e)
            {
                int no = Convert.ToInt32(noTextBox.Text);
    
                // Open the connection using the connection string.
                using (SqlCeConnection connection = new SqlCeConnection(connectionString))
                {
                    connection.Open();
    
                    // Insert into the SqlCe table. ExecuteNonQuery is best for inserts.
                    using (SqlCeCommand com = new SqlCeCommand("INSERT INTO Customer (No, Name, Address) Values(@no,@name,@address)", connection))
                    {
                        com.Parameters.AddWithValue("@no", no);
                        com.Parameters.AddWithValue("@name", nameTextBox.Text);
                        com.Parameters.AddWithValue("@address", addressTextBox.Text);
                        
                        com.ExecuteNonQuery();
                    }
                    connection.Close();
                }
    
    
               if ((rdbYes.Checked) == true)
                {
                    Form hireForm = new frmHire();
                    hireForm.Show();            
                }
                else if ((rdbNo.Checked) == true)
                {
                    this.Close();
                }
                else
                {
                    MessageBox.Show("Please select yes or no!");
                }
            }
    
            private void mnuExit_Click(object sender, EventArgs e)
            {
                this.Close();
            }
    
            private void frmCars_Load(object sender, EventArgs e)
            {
                if (CarsGaloreDataSetUtil.DesignerUtil.IsRunTime())
                {
                    // TODO: Delete this line of code to remove the default AutoFill for 'carsGaloreDataSet.Customer'.
                    this.customerTableAdapter.Fill(this.carsGaloreDataSet.Customer);
                }
    
                Globals g = Globals.GetInstance();
                noTextBox.Text = g.CustNo.ToString();
                nameTextBox.Text = g.CustName.ToString();
                addressTextBox.Text = g.CustAddress.ToString();
                
            }
    
            private void fnGetConnectedToDatabase()
            {
                string connectionString = ("Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) + "\\CarsGalore.sdf;Persist Security Info=False;");
    
                SqlCeConnection connection = new SqlCeConnection(connectionString);
    
                string selectStatement = "SELECT * FROM Customer";
                SqlCeCommand selectCommand = new SqlCeCommand(selectStatement, connection);
    
                SqlCeDataAdapter customerDataAdapter = new SqlCeDataAdapter(selectCommand);
                SqlCeCommandBuilder builder = new SqlCeCommandBuilder(customerDataAdapter);
    
                DataSet carsGaloreDataSet = new DataSet();
    
                try
                {
                    //open the connection to the database
                    connection.Open();
                }
                catch (SqlCeException ex)
                {
                    MessageBox.Show("Error in connection ..." + ex.Message);
                }
    
                //Gets a collection that provides the master mapping
                // between a source table and a DataTable
                customerDataAdapter.TableMappings.Add("Table", "Customer");
    
                /*A data adapter object utilizes the Fill method
                to populate a DataSet or a DataTable object with data
                retrieved by a SELECT command. */
                customerDataAdapter.Fill(carsGaloreDataSet);
            }
        }
    }

  2. #2
    Join Date
    Mar 2008
    Location
    IRAN
    Posts
    811

    Re: C# and SQLCE Insert statement not inserting data into database

    do you have such problem with Update and Delete aslo?
    before testing delete backup the database.

    if you have problem with all of them then you may have a major probelm if not perhaps the user you use it to connect to database may have not privilege for insert.

    i saw your code and everything seems okay.

    also test this:

    Code:
    int result = -1;
    result = com.ExecuteNonQuery();
    then debug your code and watch the locals window to see what happens to result variable.
    it would be better that you place a Break Point before it and when it reached press F11 to see the result in locals window.
    Please rate my post if it was helpful for you.
    C#, C++, PHP, ASP.NET
    SQL Server, MySQL
    DirectX
    MATH
    Touraj Ebrahimi
    [toraj_e] [at] [yahoo] [dot] [com]

  3. #3
    Join Date
    Nov 2008
    Posts
    9

    Re: C# and SQLCE Insert statement not inserting data into database

    I inserted the code
    Code:
    int result = -1;
    result = com.ExecuteNonQuery
    i debugged it and result changed to 0 and then to 1.

    i have another question.
    [qoute]
    the user you use it to connect to database may have not privilege for insert.
    [/quote]

    How do you change the users priviilege's for the insert??

  4. #4
    Join Date
    Mar 2008
    Location
    IRAN
    Posts
    811

    Re: C# and SQLCE Insert statement not inserting data into database

    Quote Originally Posted by pansylea
    i debugged it and result changed to 0 and then to 1.
    so it seems that the insertion has been done succesfully; just refresh the database to see the result.
    Please rate my post if it was helpful for you.
    C#, C++, PHP, ASP.NET
    SQL Server, MySQL
    DirectX
    MATH
    Touraj Ebrahimi
    [toraj_e] [at] [yahoo] [dot] [com]

  5. #5
    Join Date
    Nov 2008
    Posts
    9

    Re: C# and SQLCE Insert statement not inserting data into database

    it's still not showing in the database when i exit the program

    i have another question.
    the user you use it to connect to database may have not privilege for insert.
    How do you change the users priviilege's for the insert??

  6. #6
    Join Date
    Mar 2008
    Location
    IRAN
    Posts
    811

    Re: C# and SQLCE Insert statement not inserting data into database

    Select Database (for your project)> Users > Right-Click on the user > select Permissions > select Insert For desired tables in the database
    Please rate my post if it was helpful for you.
    C#, C++, PHP, ASP.NET
    SQL Server, MySQL
    DirectX
    MATH
    Touraj Ebrahimi
    [toraj_e] [at] [yahoo] [dot] [com]

  7. #7
    Join Date
    Nov 2008
    Posts
    9

    Re: C# and SQLCE Insert statement not inserting data into database

    I'm using Microsoft Visual Studio 2005 and i do dont see where you set the permissions.

  8. #8
    Join Date
    Jul 2009
    Posts
    1

    Re: C# and SQLCE Insert statement not inserting data into database

    Hi

    Did you solve the problem with "inserting not working with sql ce" ?

    what is the solution, I too facing the exact problem what you faced......


    Regards
    Abinash

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center