SQL Server : data not being inserted

Question

I am writing a sample application to insert data into a SQL Server database using C#. Data is not persisting in the database.

Below is my code:

conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Insert into Record (ID,Name) values ('" + txtId.Text + "' , '" + txtName.Text + "')";

cmd.ExecuteNonQuery();
//cmd.Clone();
conn.Close();

The values are not persisted. There is no error when I insert the values. When I changed my command to:

"Insert into Database.dbo.Record (ID,Name) values ('"

it throws an exception:

Incorrect syntax near the keyword 'Database'.

Why is my SQL Server database not being updated?

Answer 1

Your code as is, is possibly open to SQL injection - you should use parameters.

Also check the table names and primary keys, e.g. if ID is an Identity column it is automatically generated when you insert, so then you'll just INSERT INTO Record (Name) VALUES ('Bob')

Otherwise if ID is a primary key you'll likely have to check you are not trying to insert duplicates.

Please use parameters! (adjust the SqlDbType's if needed)

You should also use a using statement over the connection to properly dispose resources, and ideally use a transaction for updates:

string sqlQuery = "INSERT INTO Record (ID, Name) VALUES (@id, @name); ";

using (SqlConnection conn = new SqlConnection(connectionString)) {
    conn.Open();
    SqlTransaction tran = conn.BeginTransaction();

    try {
        SqlCommand command = new SqlCommand(sqlQuery, conn, tran);

        SqlParameter in1 = command.Parameters.Add("@id", SqlDbType.NVarChar);
        in1.Value = txtId.Text;
        SqlParameter in2 = command.Parameters.Add("@name", SqlDbType.NVarChar);
        in1.Value = txtName.Text;

        command.ExecuteNonQuery();

        tran.Commit();
    } catch (Exception ex) {
        tran.Rollback();    
        //...
    }
}