Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.SqlClient.dll

Question

I'm a beginner, and new to C#, I don't know how to fix this bug, please help SQL Service is running, VS2019 is updated to the newest and I am Windows 10. It only said Exception thrown:

'System.Data.SqlClient.SqlException' in System.Data.dll

Here's my code :

private void btnInsert_Click(object sender, EventArgs e)
{ 
    conn = new SqlConnection(@"Data Source=TS-G5\SQLEXPRESS;Initial Catalog=EsoftProj;Integrated Security=True");

    cmd = new SqlCommand("INSERT INTO SES (Registration Number, Student Name, Date of Birth, Gender, Contact Number, Course enrolled in) VALUES (@Registration Number, @Student Name, @Date of Birth, @Gender, @Contact Number, @Course enrolled in)", conn);

    conn.Open();

    cmd.Parameters.Add("@Registration Number", SqlDbType.Text).Value = RegNumTB.Text;
    cmd.Parameters.Add("@Student Name", SqlDbType.Text).Value = StudentNameTB.Text;
    cmd.Parameters.Add("@Date of Birth", SqlDbType.Date).Value = DoBPick.Value.Date;
    cmd.Parameters.Add("@Gender", SqlDbType.Text).Value = GMale.Checked ? "Male" : "Female";
    cmd.Parameters.Add("@Contact Number", SqlDbType.Text).Value = ContactNumTB.Text;
    cmd.Parameters.Add("@Course enrolled in", SqlDbType.Text).Value = CourseEnrSel.GetItemText(CourseEnrSel.SelectedItem);

    cmd.ExecuteNonQuery();

    if (cmd.ExecuteNonQuery() > 0)
    {
        MessageBox.Show("Record inserted");
    }
    else
    {
        MessageBox.Show("Record failed");
    }
}

variable name in sql (like `@Date of Birth`) must not contain spaces. Use `@Date_of_Birth` instead ... Same for column names. You can also surround such identifies by square brackets `[Date of Birth]` — derpirscher, Aug 28 '21 at 07:36
Tip: the exception has a `Message`, which was displayed as part of the exception (not just the type). That message would have told you, and us, exactly what the problem was. Whenever reporting an error: always include the exact message text. — Marc Gravell, Aug 28 '21 at 07:57
Side note: connections and commands are disposable - you are responsible for making sure that happens, for example via `using` blocks/statements. I would also suggest that `conn` and `cmd` in this case should be local variables, not fields on the type; fix: `using var conn = ...` and `using var cmd = ...` (if you're using up-to-date language versions) — Marc Gravell, Aug 28 '21 at 07:59

score 2 · Answer 1 · edited Aug 28 '21 at 07:46

Most programming languages, including C# and SQL, don't like spaces inside identifiers.

In SQL Server you can get around that using []. but I'm not sure about the parameters, best to avoid spaces there.

 cmd = new SqlCommand("INSERT INTO SES([Registration Number],[Student Name], ...) 
       VALUES (@RegistrationNumber,@StudentName,...)", conn);



  cmd.Parameters.Add("@RegistrationNumber", SqlDbType.Text).Value = RegNumTB.Text;
  cmd.Parameters.Add("@StudentName", SqlDbType.Text).Value = StudentNameTB.Text;

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.SqlClient.dll

1 Answers1

Linked