Data insertion in sql through C#

Question

I have successfully created connection of database but now I'm having problem in insertion of data. Here is my code:

String Connection = null;
SqlConnection con;
SqlCommand cmd;
String sql = null;
Connection="Data Source=DELL\\SQLEXPRESS; initial Catalog= BSSE;Integrated Security=True";
con = new SqlConnection(Connection);
sql = "INSERT INTO Records (Roll_No,Name,Marks) VALUES (" + textBox1.Text + "," + textBox2.Text + "," + textBox3.Text + ");";
try
{
    con.Open();
    cmd = new SqlCommand(sql, con);
    cmd.ExecuteNonQuery();
    MessageBox.Show ("Success of data insertion ");
    cmd.Dispose();
    con.Close();
}
catch (Exception ex)
{
    MessageBox.Show("Can not open connection ! ");
}

Maybe you can study this one: http://stackoverflow.com/a/5480368/1050927 — Prisoner, Nov 01 '16 at 04:12

score 1 · Accepted Answer · answered Nov 01 '16 at 04:46

First, your SQL statement is incorrect. You are missing single quote between values field. Later, you build SQL statement by using string concatenation and this is dangerous because can be exposed to SQL Injection. Use Parameterized Query instead.

try
{
    con.Open();
    cmd = new SqlCommand("INSERT INTO Records (Roll_No,Name,Marks) VALUES (@rollNo, @Name, @Marks)", con);
    cmd.Parameters.AddWithValue("@rollNo", textBox1.Text);
    cmd.Parameters.AddWithValue("@Name", textBox2.Text);
    cmd.Parameters.AddWithValue("@Marks", textBox3.Text);
    cmd.ExecuteNonQuery();

    MessageBox.Show ("Success of data insertion ");
    cmd.Dispose();
    con.Close();
}
catch (Exception ex)
{
    MessageBox.Show("Can not open connection ! ");
}

You're welcome. Please tick the green tick to mark it as answer. — Hermanto, Nov 02 '16 at 01:10

score 0 · Answer 2 · answered Nov 01 '16 at 04:34

Check your connection string. I usually write it as:

string Connection = @"Data Source=DELL\SQLEXPRESS;Initial Catalog = BSSE; Integrated Security = true";
If the roll number is supposed to be an integer, you need to parse it.

int.Parse(textBox1.Text)

score 0 · Answer 3 · answered Nov 01 '16 at 04:39

I suggest to use store procedures instead of sending blocks of SQL code from the c# Application, here is a reference to the SQL Store Procedures: https://msdn.microsoft.com/en-us/library/ms190782.aspx. You can reduce the possibility of SQL injection by adding parameters to your query instead of plain text, also you need to validate the input. You can create calls with parameters too. There are many ways to call a SQL database query from C#, Here is more information about Store Procedures that can give you a clue: http://csharp-station.com/Tutorial/AdoDotNet/Lesson07

Data insertion in sql through C#

3 Answers3