C# – How to insert into a Service-based Database

c++sql-server-express

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 😛

Best Solution

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();
       ...
    }
}
Related Question