SQL Exception in run time

Question

I am trying to add values to a database using WPF form.

This is my code:

private void Insertbtn_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection("Data Source=DESKTOP-HSIK0SQ; Initial Catalog=Demo; Integrated Security=SSPI");
    conn.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO Student VALUES ("+ RollNumebrtxt + "," + FNametxt + "," + Coursetxt + ")";
    
    int count = cmd.ExecuteNonQuery();

    MessageBox.Show(count + " record saved successfully");

    conn.Close();
}

When I am hitting the insert button:

This is the exception being thrown:

I am sure that whichever the labels and text boxes I have added they have unique name in the property. It is throwing exception while executing the query command

I am not sure what I could miss here?

**Parametrise** your code. Your application is wide upon to injection attacks and writing good, secure code will also solve the problem you are encountering. — Thom A, Sep 18 '22 at 12:54
This is just for practice.. do yo solution for the problem I stated? — Mr. A, Sep 18 '22 at 13:14
[Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — Thom A, Sep 18 '22 at 13:15
I understand that but, is that the solution to above problem? — Mr. A, Sep 18 '22 at 13:20
Yes, the solutions is to use parameters, **always**. Side note: your connection and command objects need `using` to dispose them — Charlieface, Sep 18 '22 at 13:32
As I said, writing good, secure code will resolve the problem too. If you *parametrise* your code you won't get the SQL syntax error you are getting. — Thom A, Sep 18 '22 at 13:33

score 2 · Accepted Answer · edited Sep 18 '22 at 22:56

2

Make sure to surround these values with single quotes '', and if these variables are the TextBoxes, then you have to call .Text on them to get the proper values..

cmd.CommandText = "Insert into Student values ('"+ RollNumebrtxt.Text + "','" + FNametxt.Text + "','" +
        Coursetxt.Text + "');";

Better to parametrised your query..

Replace

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Student VALUES ("+ RollNumebrtxt + "," + FNametxt + "," + Coursetxt + ")";

With

SqlCommand cmd = new SqlCommand("INSERT INTO Student VALUES(@RollNumber, @FName, @CourseName)", conn);
cmd.Parameters.Add("@RollNumber", SqlDbType.Int).Value = int.Parse(RollNumebrtxt.Text);
cmd.Parameters.Add("@FName", SqlDbType.VarChar, 100).Value = FNametxt.Text;
cmd.Parameters.Add("@CourseName", SqlDbType.VarChar, 100).Value = Coursetxt.Text;

edited Sep 18 '22 at 22:56

Charlieface

52,284
6
19
43

answered Sep 18 '22 at 12:40

Muhammad Sulaiman

2,399
4
14
28

And when someone inevitably has an apostrophe in their name? – Thom A Sep 18 '22 at 12:55
Or, what about when someone enters their course as `Maths'); DROP TABLE Student;--` or [Little Bobby Tables](https://xkcd.com/327/) enrolls? – Thom A Sep 18 '22 at 13:00
@Larnu that will throw a run time exception – Mr. A Sep 18 '22 at 13:28
1

Even for practice this is wrong. Use parameters, it's not difficult – Charlieface Sep 18 '22 at 13:31
@Mr.A I've updated the answer, better to use the parametrised version.. – Muhammad Sulaiman Sep 18 '22 at 13:43
*"that will throw a run time exception"* no, no it wouldn't @MuhammadSulaiman . – Thom A Sep 18 '22 at 13:47
@Larnu wrong mention :) – Muhammad Sulaiman Sep 18 '22 at 13:48
@MuhammadSulaiman why use of parameters is recommended? how use of parameters makes code safe? – Mr. A Sep 18 '22 at 13:48
Have you read the question I linked @Mr.A ? You wouldn't be asking that if you did. – Thom A Sep 18 '22 at 13:49
1

See the example Larnu has posted of using `Maths'); DROP TABLE Student;` in some of the fields.. the simicolon `;` will end the query and starts another query that can drop tables from db and that will cause damage to your application/system – Muhammad Sulaiman Sep 18 '22 at 13:50

SQL Exception in run time

1 Answers1