CODEWITHSUNDEEP
c#sql-server
patreilly
patreilly

Reputation: 39

C# - Insert into SQL Server not making changes

New to C#.

I am trying to do a simple INSERT SQL statement using windows forms. What should happen is when the button is pressed it will execute an SQL statement but when I do press it nothing happens.

Is there something I'm missing?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace windowFormsApp_test
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'por_testDataSet.tbl_persons' table. You can move, or remove it, as needed.
            this.tbl_personsTableAdapter.Fill(this.por_testDataSet.tbl_persons);

        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connectionString = "Data Source=DC-POR\\SQLEXPRESS;Initial Catalog=por_test;Integrated Security=True;";

            SqlConnection con = new SqlConnection(connectionString);

            con.Open();

            string sql = "INSERT into tbl_persons (person_id,p_name,p_surname) VALUES (55,'TEST','test')";
            SqlCommand cmd = new SqlCommand(sql);

            con.Close();
        }

Upvotes: 0

Views: 73

Answers (1)

Nishant Shrivastava
Nishant Shrivastava

Reputation: 389

You need to call ExecuteNonQuery to actually do the insert.

The code should be as follows:

private void button1_Click(object sender, EventArgs e)
{
        string connectionString = "Data Source=DC-POR\\SQLEXPRESS;Initial Catalog=por_test;Integrated Security=True;";

        SqlConnection con = new SqlConnection(connectionString);
        con.Open();

        string sql = "INSERT into tbl_persons (person_id,p_name,p_surname) VALUES (55,'TEST','test')";
        SqlCommand cmd = new SqlCommand(sql);
        cmd.Connection = con;
        cmd.ExecuteNonQuery();

        con.Close();
    }

You shouldn't pass the values directly to the insert statements. Rather you should use the parameters.add to pass the value. So better approach to execute the SQL is below

private void button1_Click(object sender, EventArgs e)
{
    string connectionString = "Data Source=DC-POR\\SQLEXPRESS;Initial Catalog=por_test;Integrated Security=True;";

    using(SqlConnection con = new SqlConnection(connectionString))
    {             
        string sql = @"INSERT into tbl_persons (person_id, p_name, p_surname) VALUES (@param1, @param2, @param3)";

       using(var cmd = new SqlCommand())
       {
           cmd.Connection = conn;
           cmd.CommandType = CommandType.Text;
           cmd.CommandText = sql;

           cmd.Parameters.AddWithValue("@param1", 55);  
           cmd.Parameters.AddWithValue("@param2", 'TEST');  
           cmd.Parameters.AddWithValue("@param3", 'test1');

           con.Open();
           cmd.ExecuteNonQuery();
        }
    }
}

Upvotes: 2

Related Questions