从asp.net中的表单插入表格

Question

我有一个页面，您可以填写一些信息，并根据该信息向数据库插入新行。这里是一个充满形式的截图：

这里是我的代码，点击提交按钮，当插入到数据库：

protected void CreateCourseButton_Click(object sender, EventArgs e) 
{ 
    SqlConnection con = new SqlConnection(); 
    con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=University;Integrated Security=True;Pooling=False"; 

    string query1 = "insert into Courses(CRN,CourseName,StudyLevel,Capacity,Instructor,Credits,Prerequisite) values (" 
     + courseID.Text + "," + courseName.Text + "," + studyLevel.SelectedValue + "," + capacity.Text + "," + "Admin," + credits.Text + "," + prereq.Text + ")"; 



    SqlCommand cmd1 = new SqlCommand(query1, con); 
    con.Open(); 
    cmd1.ExecuteNonQuery(); 
    con.Close(); 
} 

的问题是，我得到以下错误，当我点击提交：34

Server Error in '/Bannerweb' Application. 

Incorrect syntax near the keyword 'to'. 

Description: An unhandled exception occurred during the execution of the current web  
request. Please review the stack trace for more information about the error and where 
it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the 
keyword 'to'. 

Source Error: 


Line 32:   SqlCommand cmd1 = new SqlCommand(query1, con); 
Line 33:   con.Open(); 
Line 34:   cmd1.ExecuteNonQuery(); 
Line 35:   con.Close(); 
Line 36:  } 

Source File: c:\Banner\Bannerweb\Pages\CreateCourse.aspx.cs Line: 34 

Stack Trace: 


[SqlException (0x80131904): Incorrect syntax near the keyword 'to'.] 
    System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean  
breakConnection) +2084930 
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean  
breakConnection) +5084668 
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234 
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, 
SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject 
stateObj) +2275 
    System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean 
async) +228 
    System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result,  
String methodName, Boolean sendToPipe) +326 
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 
    CreateCourse.CreateCourseButton_Click(Object sender, EventArgs e) in 
c:\Banner\Bannerweb\Pages\CreateCourse.aspx.cs:34 
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118 
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112 

线路是：

cmd1.ExecuteNonQuery(); 

任何人都可以帮我解决这个错误吗？

感谢

Answer 1

修改您Insert查询像

string query1 = "insert into Courses(CRN,CourseName,StudyLevel,Capacity,Instructor,Credits,Prerequisite) values (" 
     + courseID.Text + ",'" + courseName.Text + "'," + studyLevel.SelectedValue + "," + capacity.Text + "," + "Admin," + credits.Text + "," + prereq.Text + ")"; 

问题二

如果它是保存然后ExecuteNonQuery将返回1其他0，所以通过使用返回的值，你可以检查并应用条件。

希望你能理解。

Answer 2

看起来你需要添加周围​​报价。同样使用SQL parameterized queries，所以你不容易受到SQL Injection的影响。

'" + courseName.Text + "' 

将评估为：

'Intro to comp' 

http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/

Answer 3

你要通过所有的控制值内'更新您的SQL查询是这样的：

string query1 = "insert into 
Courses(CRN,CourseName,StudyLevel,Capacity,Instructor,Credits,Prerequisite) values ("+ 
"'" + courseID.Text + "'" + "," + "'" + courseName.Text + "'" + "," + 
"'" + studyLevel.SelectedValue + "'" + "," + "'" + capacity.Text + "'" + 
"," + "'Admin'," + "'" + credits.Text + "'" + "," + "'"+prereq.Text +"'" + ")"; 

//返回受查询影响的行数

int a= cmd1.ExecuteNonQuery(); 
if(a>0) 
{ 
//inserted 
} 
else 
{ 
//not inserted 
} 

检查here了解更多详情。

Answer 4

发生此错误是因为缺少插入值之间的''''。不管怎么说最好的方法是使用Parameters收集这样的：

string query1 = "insert into Courses(CRN,CourseName,StudyLevel,Capacity,Instructor,Credits,Prerequisite) values (@crn, @cursename, @studylevel, @capacity, @instructor, @credits, @prerequesite)"; 

SqlCommand cmd1 = new SqlCommand(query1, con); 
cmd1.Parameters.AddWithValue("@crn", courseID.Text); 
//add the rest 

con.Open(); 
cmd1.ExecuteNonQuery(); 
con.Close(); 

Answer 5

这个错误可能是从Course name场，在那里你必须在值空间来。只需修复它，您可以将TextBoxes的值包装为'字符。

但是，这是一个巨大的安全漏洞。如今，您必须使用参数，如您插入必须像：

SqlConnection con = new SqlConnection(); 
con.ConnectionString = "..."; 

string query1 = "insert into Courses(CRN,CourseName,StudyLevel,Capacity,Instructor,Credits,Prerequisite)"+ 
    " values (@CRN, @CourseName, ...)"; 



SqlCommand cmd1 = new SqlCommand(query1, con); 

// Insert parameters 
cmd1.Parameters.AddWithValue("@CRN",courseID.Text); 
... 

con.Open(); 
cmd1.ExecuteNonQuery(); 
con.Close(); 

您必须使用参数来保护自己免受SQL注入攻击。

Answer 6

试试这个

string query1 = "insert into Courses(CRN,CourseName,StudyLevel,Capacity,Instructor,Credits,Prerequisite) 
     values ('"+ courseID.Text +"','"+ courseName.Text + "','" + studyLevel.SelectedValue +"', '" + capacity.Text +"','" + "Admin" +"','"+credits.Text + "','" + prereq.Text +"') "; 

你的查询语法是完全错误的。

Answer 7

protected void Button1_Click(object sender, EventArgs e) 
    { 
     SqlConnection conn = new SqlConnection("Data Source=D1-0221-37-393\\SQLEXPRESS;Initial Catalog=RSBY;User ID=sa;[email protected]"); 
     conn.Open(); 
     string EmployeeId = Convert.ToString(TextBox1.Text); 
     string EmployeeName = Convert.ToString(TextBox2.Text); 
     string EmployeeDepartment = Convert.ToString(DropDownList1.SelectedValue); 
     string EmployeeDesignation = Convert.ToString(DropDownList2.SelectedValue); 
     string DOB = Convert.ToString(TextBox3.Text); 
     string DOJ = Convert.ToString(TextBox4.Text); 
     SqlCommand cmd = new SqlCommand("insert into Employeemaster values('" + EmployeeId + "','" + EmployeeName + "','" + EmployeeDepartment + "','" + EmployeeDesignation + "','" + DOB + "','" + DOJ + "')", conn); 
     cmd.ExecuteNonQuery(); 

    }