Inserting data into database using SQL help

[ads1188]

Hi all

I am trying to enter data into textboxes which will then store the data entered into a database. When I debug everything starts but when I click Add, the database stays the same. I think I might be missing something in my code. Any help is appreciated.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace Acc
{
    public partial class New_Customer : Form
    {
        public SqlCeConnection cn = new SqlCeConnection(@"Data Source = C:\Projects\Acc\Acc\Customers.sdf");

        public New_Customer()
        {
            InitializeComponent();
        }

        

        private void New_Customer_Shown(object sender, EventArgs e)
        {
            try
            {
                cn.Open();
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.ExitThread();
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            // nothing will happen if A/C ref and customer name are empty
            if (txtACRef.Text != "" && txtCustomerName.Text != "")
            {
                btnAdd.Enabled = true;
            }
            else
            {
                btnAdd.Enabled = false;
            }
        }

        private void btnAdd_Click_1(object sender, EventArgs e)
        {
            
            SqlCeCommand cn = new SqlCeCommand("INSERT INTO tblCustomers (Customer_Ref, Customer_Name) VALUE(@Customer_Ref, @Customer_Name)");
            cn.Parameters.AddWithValue("@Customer Ref", txtACRef.Text);
            cn.Parameters.AddWithValue("@Customer Name", txtCustomerName.Text);
        
            
            }
        
    }

    }

[ads1188]

I think I am missing the line

cn.ExecuteNonQuery();

at the end but it says it is not initialized. Can anyone point me in the right direction. Thanks

[dickersonka]

Where are you setting the connection to the command? My advice is to not set the connection open on the form shown. Only use the connection when you are going to run a sqlcommand / query. That way, when you are done with it you can close it.

[ads1188]

Where are you setting the connection to the command? My advice is to not set the connection open on the form shown. Only use the connection when you are going to run a sqlcommand / query. That way, when you are done with it you can close it.

I have taken your advice and moved open connection to when the data is being entered. I still think my code is incorrect as I may have missed some things out. If someone has any time I hope they wouldn't mind having a look as I have been trying to figure this out for the last 3 days on my own hitting a brick wall as you can see below.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace Acc
{
    public partial class New_Customer : Form
    {
        public SqlCeConnection cn = new SqlCeConnection(@"Data Source = C:\Projects\Acc\Acc\Customers.sdf");

        public New_Customer()
        {
            InitializeComponent();
        }
 
        private void New_Customer_Load(object sender, EventArgs e)
        {

        }

        
        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            // nothing will happen if A/C ref and customer name are empty
            if (txtACRef.Text != "" && txtCustomerName.Text != "")
            {
                btnAdd.Enabled = true;
            }
            else
            {
                btnAdd.Enabled = false;
            }
        }

        private void btnAdd_Click_1(object sender, EventArgs e)
        {
            string Customer_Ref = txtACRef.Text;
            string Customer_Name = txtCustomerName.Text;

            SqlCeCommand cn = new SqlCeCommand("INSERT INTO tblCustomers (Customer_Ref, Customer_Name) VALUE(@Customer_Ref, @Customer_Name)");
            cn.Parameters.AddWithValue("@Customer_Ref", txtACRef.Text);
            cn.Parameters.AddWithValue("@Customer_Name", txtCustomerName.Text);

            cn.Open();                //opens connection
            cn.ExecuteNonQuery();     //writes to the database
            MessageBox.Show("Update Successful!!");
          }

                cn.Close();
            }                      //closes the else block
    
            } 
        
        }                                                
       }
    }

[dickersonka]

For one your command is named the same as your connection.

Make it like this and try to run it, then let me know what errors you are getting

SqlCeCommand command = new SqlCeCommand("INSERT INTO tblCustomers (Customer_Ref, Customer_Name) VALUE(@Customer_Ref, @Customer_Name)");

[ads1188]

For one your command is named the same as your connection.

Make it like this and try to run it, then let me know what errors you are getting

SqlCeCommand command = new SqlCeCommand("INSERT INTO tblCustomers (Customer_Ref, Customer_Name) VALUE(@Customer_Ref, @Customer_Name)");

I changed the cn to command and have posted the code below. I get errors at the moment which error CS1022 last 2 } not recognised

Any ideas on how I can sort this out? Many thanks

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace Accounting_Software_Csharp_V1._0
{
    public partial class New_Customer : Form
    {
        
        public SqlCeConnection cn = new SqlCeConnection(@"Data Source = C:\Users\Adnan\Documents\Visual Studio 2010\Projects\Accounting Software Csharp V1.0\Accounting Software Csharp V1.0\Customers.sdf");
        
        public New_Customer()
        {
            InitializeComponent();
        }
         
        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            // nothing will happen if A/C ref and customer name are empty
            if (txtACRef.Text != "" && txtCustomerName.Text != "")
            {
                btnAdd.Enabled = true;
            }
            else
            {
                btnAdd.Enabled = false;
            }
        }

        private void btnAdd_Click_1(object sender, EventArgs e)
        {
            string Customer_Ref = txtACRef.Text;
            string Customer_Name = txtCustomerName.Text;         

            SqlCeCommand command = new SqlCeCommand("INSERT INTO tblCustomers (Customer_Ref, Customer_Name) VALUE(@Customer_Ref, @Customer_Name)");
            command.Parameters.AddWithValue("@Customer_Ref", txtACRef.Text);
            command.Parameters.AddWithValue("@Customer_Name", txtCustomerName.Text);

            command.Connection=cn;
            command.Connection.Open(); //opens connection
            
            try
            {
            command.ExecuteNonQuery();
            MessageBox.Show("Update Successful!!");
            }
            catch{}
          }
            } 
        }                                                
       }
    }

[ads1188]

I have deleted the last 2 brackets and now the program will debug. I enter the data into the 2 textboxes but when I close the program the database hasnt changed. Is there something I am not doing right? I think I may need a close function after the add but im not sure.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace Accounting_Software_Csharp_V1._0
{
    public partial class New_Customer : Form
    {
        
        public SqlCeConnection cn = new SqlCeConnection(@"Data Source = C:\Users\Adnan\Documents\Visual Studio 2010\Projects\Accounting Software Csharp V1.0\Accounting Software Csharp V1.0\Customers.sdf");
        
        public New_Customer()
        {
            InitializeComponent();
        }
         
        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            // nothing will happen if A/C ref and customer name are empty
            if (txtACRef.Text != "" && txtCustomerName.Text != "")
            {
                btnAdd.Enabled = true;
            }
            else
            {
                btnAdd.Enabled = false;
            }
        }

        private void btnAdd_Click_1(object sender, EventArgs e)
        {
            string Customer_Ref = txtACRef.Text;
            string Customer_Name = txtCustomerName.Text;         

            SqlCeCommand command = new SqlCeCommand("INSERT INTO tblCustomers (Customer_Ref, Customer_Name) VALUE(@Customer_Ref, @Customer_Name)");
            command.Parameters.AddWithValue("@Customer_Ref", txtACRef.Text);
            command.Parameters.AddWithValue("@Customer_Name", txtCustomerName.Text);

            command.Connection=cn;
            command.Connection.Open(); //opens connection
            
            try
            {
            command.ExecuteNonQuery();
            MessageBox.Show("Update Successful!!");
            }
            catch{}
          }
            } 
        }                                                
       }
    }

[dwarvenassassin]

Have you tried the INSERT statement directly (ie. on your DB server rather than through your program? Are you SURE the update is working?
Just because command.ExecuteNonQuery(); doesn't bomb out, it doesn't mean the SQL operation was successful.
You are not checking any returned values in your code. How do you know if it worked if you never check how many rows were affected?