CODEWITHSUNDEEP
asp.netdatabasevisual-studio-2013sqlexceptionssms
user3577397
user3577397

Reputation: 473

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

Upvotes: 0

Views: 183

Answers (3)

Steve
Steve

Reputation: 216273

You are using a reserved keyword TABLE and the first thing to do is to change that name in something more comprehensible. If you cannot change that name then you should use the square brakets around that name.

But this is just the first problem. Then you have another one.
You try to create your sql command concatenating strings but you put a textbox object in the concatenation instead of the Text property of the textbox.

SqlCommand cmd = new SqlCommand("INSERT INTO [TABLE] VALUES('" + txtFName.Text + "',....

And this is still very wrong because this result in code open to SQL Injection, so what you really need to do is start using a parameterized query

SqlCommand cmd = new SqlCommand("INSERT INTO [TABLE] VALUES(@fname, @lname, ..... ");
cmd.Parameters.AddWithValue("@fname", txtFName.Text);
cmd.Parameters.AddWithValue("@lname", txtLName.Text);
... and so on for all the others fields ....

Upvotes: 2

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84735

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.

Upvotes: 2

display name
display name

Reputation: 4185

Table is a keyword. You should create a new name for your table.

Reference: http://msdn.microsoft.com/en-us/library/ms189822%28v=sql.105%29.aspx

Upvotes: 2

Related Questions