Click here to Skip to main content
15,667,466 members
Please Sign up or sign in to vote.
2.00/5 (3 votes)
See more:
Hello,

I am getting the following error - Incorrect Syntax near ','.

The program is falling over on:

DataAdapter.InsertCommand.ExecuteNonQuery();


When I run the following statement:

SqlConnector.Insert("INSERT INTO Customer (custID, title, firstName, lastName, address1, address2, address3, address4, postCode, phoneNumber, mobileNumber, email, fax) VALUES ("+txtBxAccountNumber.Text+", "+txtBxTitle.Text+", "+txtBxFirstName.Text+", "+txtBxLastName.Text+", "+txtBxAddressLine1.Text+", "+txtBxAddressLine2.Text+", "+txtBxCity.Text+", "+txtBxCounty.Text+", "+txtBxPostCode.Text+", "+txtBxLandlineNumber.Text+", "+txtBxMobileNumber.Text+", "+txtBxEmailAddress.Text+", "+txtBxFaxNumber.Text+")");


Any pointers are grateful.

Thanks

Glen
Posted
Comments
abbaspirmoradi 13-Jan-14 5:53am    
when one of your textbox's text is null or empty this error happened..

Yet another SQL query built by concatenating string fields obtained from user input.

This is a very bad practice; you have to use parameterized queries if you do not want to leave your code opened to SQL Injection attacks.

Something like:
C#
string query = "INSERT INTO Customer (custID, title, firstName, lastName, address1, address2, address3, address4, postCode, phoneNumber, mobileNumber, email, fax) VALUES (@accountNumber, @bxTitle, @bxFirstName, @bxLastName, @bxAddressLine1, @bxAddressLine2, @bxCity, @bxCounty, @bxPostCode, @bxLandlineNumber, @bxMobileNumber, @bxEmailAddress, @bxFaxNumber)";
SqlCommand cmd = new SqlCommand(query, sqlCon); // sqlCon is of type SqlConnection

// Here for a string column that do not accept a null value:
if (!string.IsNullOrEmpty(txtBxAccountNumber.Text)) {
   cmd.Parameters.AddWithValue("@accountNumber", txtBxAccountNumber.Text);
}
else {
   throw new ArgumentException("AccountNumber field cannot be empty");
}

// OR (if custId column is of integer type)
int accountNumber;
if (int.TryParse(txtBxAccountNumber.Text, out accountNumber)) {
   cmd.Parameters.AddWithValue("@accountNumber", accountNumber);
}
else {
   throw new ArgumentException("AccountNumber is not a valid integer value");
}

// ... and so on with all other parameters


About SQL Injection Attacks:
SQL injection attack[^]

Hope this helps.
 
Share this answer
 
The root of your problem is that you are concatenating strings of values from TextBoxes to create an SQL statement. This is not a best practice.

It is a best practice to use a parameterized SQL statement rather than concatenated string values in SQL statements. This prevents SQL Injection Attacks and provides better performance. See Microsoft help file documentation for SQLParameter Class[^].

Another benefit of using the SQLParameter class is that if one of your variables contains an embedded apostrophe, it does not cause a problem. With concatenated strings in your SQL statement and an apostrophe in one of the variables, you would get a syntax error.
 
Share this answer
 
v3
hi for number field you do like this
C#
"+txtBxAccountNumber.Text+"


for text fields ->
C#
'"+txtBxTitle.Text+"' ,


Take care of quotes :)

Its better to use String.Format[^]
 
Share this answer
 
v2
Comments
Dave Kreskowiak 13-Jan-14 9:55am    
The problem with this solution is what if the user input contains a ' mark?? It'll crash the query again. This is not the correct solution to the problem.
Karthik_Mahalingam 13-Jan-14 10:03am    
yes agreed.
Clearly most of your data are string values. Clearly some of them contains chars (space or comma or others) that are brake the flow of the SQL you have.
You must enclose the string values in single quote...
SQL
VALUES ('"+txtBxAccountNumber.Text+"', '"+txtBxTitle.Text+"',...
 
Share this answer
 
Comments
Dave Kreskowiak 13-Jan-14 9:56am    
Wrong. Clearly, the correct solution is to NEVER use string concatenation to build an SQL query like this. The correct solution is to use Parameterized queries! Think about it. What if the user input contains a ' character?? Your entire query just crashed.
Kornfeld Eliyahu Peter 13-Jan-14 10:00am    
You right. I was too lazy to complete the answer... mea culpa!
To seek forgiveness I upvoted phil.o's answer... :-)
your text is returning null or empty value...
 
Share this answer
 
Comments
Dave Kreskowiak 13-Jan-14 10:09am    
Uhhh, Textboxes never return a NULL value and even if an empty string was returned, this is not the problem at all.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900