How to insert into a Service-based Database

Question

public partial class Form1 : Form
{
    SqlConnection cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Dimmer\Documents\Visual Studio 2013\Projects\Manage components\Manage components\Database1.mdf;Integrated Security=True");

    SqlCommand cmd = new SqlCommand();
    SqlDataReader dr;

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        cmd.Connection = cn;
        loadlist();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        if (txtid.Text != "" & txtname.Text != "")
        {
            cn.Open();
            cmd.CommandText = "insert into info (id,name) values ('"+txtid.Text+"'.'"+txtname.Text+"')";
            cmd.ExecuteNonQuery();
            cmd.Clone();
            MessageBox.Show("Record instered!");
            txtid.Text = "";
            txtname.Text = "";
            loadlist();
        }
    }
}

I am new to C# and I have been trying for some hours with a insert code to a service-based database. I have tested the connection to it and it works.

I got this error message:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near 'xxxx'.

Where xxxx is what I insert into my 2nd textbox. The code stops at

cmd.ExcecuteNonQuery();

I have been searching for an answers for hours, I believe there is something wrong with the database.

Sorry if this code looks ugly, but I had some problems with spaces :P

Answer 1

You didn't tell us what are txtid.Text and txtname.Text exactly but..

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

cmd.CommandText = "insert into info (id,name) values (@id, @name)";
cmd.Parameters.AddWithValue("@id", txtid.Text);
cmd.Parameters.AddWithValue("@name", txtname.Text);
cmd.ExecuteNonQuery();

Looks like you're reusing a connection and you probably have not closed it last time.

You should always close a connection immediately as soon as you're finished with it. Use using statement like;

using(var cn = new SqlConnection(connectionString))
using(var cmd = new SqlCommand(query, cn))
{
    if (txtid.Text != "" & txtname.Text != "")
    {
       cmd.CommandText = "insert into info (id,name) values (@id, @name)";
       cmd.Parameters.AddWithValue("@id", txtid.Text);
       cmd.Parameters.AddWithValue("@name", txtname.Text);
       cn.Open();
       cmd.ExecuteNonQuery(); 
       cn.Close();
       ...
    }
}