Please help figure out what's wrong with this database update statement from text boxes in C#
comm.CommandText =
"UPDATE studbiodata([Registration Number], Surname, Othernames, Email,"
+ " Phone, Address, Sex, [Birth Date], Religion, [Marital Status],"
+ " [No. of Children], [Next of Kin], Relationship, [Next of Kin Address])"
+ "SET ([Registration Number] ='" + txtRegNo.Text
+ "', Surname = '" + txtSurname.Text
+ "', Othernames = '" + txtOthernames.Text
+ "', Email = '" + txtEmail.Text
+ "', Phone = '" + txtPhone.Text
+ "', Address = '" + txtMainAdd.Text
+ "', Sex = '" + txtSex.Text
+ "', [Birth Date]= '" + txtBirthDate.Text
+ "', Religion = '" + txtReligion.Text
+ "', [Marital Status]= '" + txtMarital.Text
+ "', [No.of Children] = '" + txtChildren.Text
+ "', [Next of Kin]= '" + txtKin.Text
+ "', Relationship ='" + txtRelationship.Text
+ "', [Next of Kin Address]= '" + txtKinAdd.Text
+ "' WHERE [Registration Number]= '" + txtRegNo.Text + "')";
The error message I get after trying to update from a web-form is:
System.Data.开发者_如何学GoSqlClient.SqlException (0x80131904): Incorrect syntax near '('. Incorrect syntax near the keyword 'SET'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Transcript.studentprofileedit.btnUpdate_Click(Object sender, EventArgs e) in C:\Users\Administrator.femzy-PC\Documents\Visual Studio 2010\Projects\Transcript Reloaded\Transcript\studentprofileedit.aspx.cs:line 119
The first thing I see wrong is that it's a classic example of how to make your site vulnerable to SQL injection. This is exactly how NOT to code a sql statement using user input.
Probably what's happening is that the user is entering something with a single quote (apostrophe) and it's messing with the statement.
https://www.owasp.org/index.php/SQL_injection
http://en.csharp-online.net/ASP.NET_Security_Hacks%E2%80%94Avoiding_SQL_Injection
Use parameterized queries or stored procedures with parameters as your absolute first step. That will eliminate erros caused by unexpected user input ("O'Connor" as a last name, for example, would cause an error in a statement coded this way). Then if there are still problems, go back and look at the statement. You can use user input (almost every program does) - you just have to do it safely.
(last edit - one more good article)
http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html
The syntax is wrong, you don't need to specify the column list first.
UPDATE studbiodatarital SET [Registration Number] ='" + txtRegNo.Text + "', Surname = '" + txtSurname.Text + "', Othernames = '" + txtOthernames.Text + "', Email = '" + txtEmail.Text + "', Phone = '" + txtPhone.Text + "', Address = '" + txtMainAdd.Text + "', Sex = '" + txtSex.Text + "', [Birth Date]= '" + txtBirthDate.Text + "', Religion = '" + txtReligion.Text + "', [Marital Status]= '" + txtMarital.Text + "', [No.of Children] = '" + txtChildren.Text + "', [Next of Kin]= '" + txtKin.Text + "', Relationship ='" + txtRelationship.Text + "', [Next of Kin Address]= '" + txtKinAdd.Text + "' WHERE [Registration Number]= '" + txtRegNo.Text + "')
精彩评论