Connection to Database Closed before data input

Question

I am using Visual Studio 2008, with Microsoft Server Compact 3.5 and the script is written for Windows Mobile 6.5.3 Professional.

I have a small form script that connects to a database file, after inputting data into the form, when you select the button, the data should update or write the input data to the database.

Somewhere in my script I close the connection before putting the data to the database.

The connection the database says connection success! And when I run script builder, I do not get any errors. The message on the windows mobile emulator is: "ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed."

As you can see, I am a novice at Visual Studio.

Here is the Form script:

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

namespace testdbconnection
{
public partial class Form1 : Form
{
    public SqlCeConnection cn = new SqlCeConnection(@"Data Source = C:\Users\...\test.sdf");

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            cn.Open();
        }
        catch (SqlCeException ex)
        {
            MessageBox.Show(ex.Message);
            Application.Exit();
        }
    }

    private void TextBoxes_TextChanged(object sender, EventArgs e)
    {
        if (textBox1.Text != "" && textBox2.Text != "")
        {
            button1.Enabled = true;
        }
        else
        {
            button1.Enabled = false;
        }
    }

    private void button1_Click(object sender, EventArgs e)
    {
        SqlCeCommand cm = new SqlCeCommand("INSERT INTO tblUsers(userName, age) VALUES (@userName, @age)", cn);
        cm.Parameters.AddWithValue("@userName", textBox1.Text);
        cm.Parameters.AddWithValue("@age", textBox2.Text);

        try
        {
            int affectedRows = cm.ExecuteNonQuery();

            if (affectedRows > 0)
            {
                MessageBox.Show("Insert success!");
                textBox1.Text = "";
                textBox2.Text = "";
            }
            else
            {
                MessageBox.Show("Insert failed!");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

}

}

Is `Form1_Load` binded in Form1.design.cs ? Set up checkpoint at `cn.Open();` line. — General-Doomer, May 29 '15 at 07:29

score 0 · Answer 1 · answered May 29 '15 at 07:30

 private void button1_Click(object sender, EventArgs e)
    {
using (cn)
{

        SqlCeCommand cm = new SqlCeCommand("INSERT INTO tblUsers(userName, age) VALUES (@userName, @age)", cn);
        cm.Parameters.AddWithValue("@userName", textBox1.Text);
        cm.Parameters.AddWithValue("@age", textBox2.Text);

        try
        {
cn.open();
            int affectedRows = cm.ExecuteNonQuery();

            if (affectedRows > 0)
            {
                MessageBox.Show("Insert success!");
                textBox1.Text = "";
                textBox2.Text = "";
            }
            else
            {
                MessageBox.Show("Insert failed!");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

Connection to Database Closed before data input

1 Answers1