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

Inserting into table from a form in asp.net

提问by yrazlik

I have a page that you fill some information and according to that information i insert a new row to the database. Here is the screenshot of the form that is filled:

我有一个页面，您可以填写一些信息，并根据该信息向数据库中插入一个新行。这是填写的表格的屏幕截图：

Here is my code to insert into database when clicked submit button:

这是我单击提交按钮时插入数据库的代码：

 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();
}

The problem is, i get the following error when i click submit:

问题是，当我单击提交时出现以下错误：

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

Line 34 is:

第 34 行是：

cmd1.ExecuteNonQuery();

Can anyone help me with this error?

谁能帮我解决这个错误？

Thanks

谢谢

采纳答案by Rahul

Modify your Insertquery like

修改您的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 + ")";

Second problem

第二个问题

If it's save then ExecuteNonQuerywill return you 1else 0, so by using return's value you may check and apply your condition.

如果保存，ExecuteNonQuery则将返回给您1else 0，因此通过使用返回值，您可以检查并应用您的条件。

Hope you understand.

希望你能理解。

回答by Darren

Looks like you need to add quotes around Course Name. Also use SQL parameterized queriesso you are not vulnerable to SQL Injection.

看起来您需要在 周围添加引号Course Name。也使用SQL parameterized queries这样你就不容易受到SQL Injection.

'" + courseName.Text + "'

Will evaluate to:

将评估为：

'Intro to comp'

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

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

回答by Arshad

You have to pass value of all control inside 'Update your sql query like this:

您必须在'更新您的 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 +"'" + ")";

//returns number of row effected by query

//返回查询影响的行数

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

check herefor more details.

查看此处了解更多详情。

回答by gzaxx

This error happens because you are missing '' between values inserted. Anyways best approach is to use Parameterscollection like that:

发生此错误是因为您在插入的值之间缺少 ''。无论如何，最好的方法是使用这样的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();

回答by oleksii

This error is probably coming from the Course namefield, where you have spaces in the value. To merely fix it you can wrap the values of the TextBoxesinto the 'char.

此错误可能来自Course name字段，其中值中有空格。只需修复它，您可以将 的值包装TextBoxes到'字符中。

But, this is a huge security leak. Nowadays, you must use parameters, such as your insert must look like:

但是，这是一个巨大的安全漏洞。如今，您必须使用参数，例如您的插入内容必须如下所示：

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();

You must use parameters to protect yourself from SQL-injection attacks.

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

回答by Sagar Hirapara

Try this

尝试这个

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 +"') ";

Your query syntax is totally wrong.

您的查询语法完全错误。

回答by Nitin

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;Password=BMW@721");
        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();

    }