Search code examples
asp.netdatabasevisual-studio-2013sqlexceptionssms

Unhandled SqlException in user code

I'm trying to build a web form that can send information to a database via ASP.Net and Microsoft SQL Server Management Studio.

enter image description here

enter image description here

It says there's incorrect syntax near Table, which is the name of the table in the database I'd like to store the values.

If you need me to post more code for clarification, I'll do that. Does anyone know what could be wrong?

I appreciate any help you can give

Thanks

EDIT:

So I changed my table name to mynameTable and I'm getting this error. It says string or binary data would be truncated

enter image description here

Solution

  • You have at least four issues; two of them cause your INSERT statement to fail (see bold headings below), and two are design flaws (see additional suggestions under each heading below). While I listed the design flaws as "additional suggestions" only, it's actually important to fix these, too.

    1. TABLE is a SQL keyword.

    TABLE is a SQL keyword. (And so is Table, since SQL syntax is case-insensitive.)

    If you actually named your table Table, you need to "escape" the name by putting it in square brackets:

    INSERT INTO [Table] …
--          ^     ^
--         add these!

    Additional suggestion: Change the table's name so that it hints at what kind of data or facts are stored in the table. Consider that everyone knows that Table is a table. But it is not obvious that Table contains some kind of personal data; so call your table Customers, Employees, Hairdressers, etc. — or if there really isn't any more specific term, call it Persons.

    2. You cannot concatenate a string and a TextBox.

    It doesn't make sense to append a TextBox into a string:

    "… VALUES ('" + txtFName + "', …"

    It should be:

    "… VALUES ('" + txtFName.Text + "', …"
//                      ^^^^^

    Additional suggestion: You are basically dynamically creating a SQL statement using string concatenation. The way how you're doing it opens up the possibility of SQL injection attacks. (This is a kind of security hole; please research this if you don't know what it is!)

    Please use parameterized queries instead:

    var cmd = new SqlCommand("INSERT INTO Persons (FirstName, LastName, …) VALUES (@FirstName, @LastName, …)", connection);
cmd.Parameters.AddWithValue("@FirstName", txtFName.Text);
cmd.Parameters.AddWithValue("@LastName", txtLName.Text);
…

    And your SQL injection security hole is gone. Also, you won't have to worry about correctly escaping quote characters (') that could have been entered by the user in the textboxes.