Insert into a table from a form in asp.net

Question

I have a page on which you fill in some information, and in accordance with this information I insert a new row into the database. Here is a screenshot of the completed form:

Here is my code to insert into the database when I click the 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 that I get the following error when I click the submit button:

 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:

 cmd1.ExecuteNonQuery(); 

Can someone help me with this error?

thanks

Answer 1

Change your Insert query, for example

 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 saves, then ExecuteNonQuery will return you 1 else 0 , therefore, using the return value, you can check and apply your condition.

I hope you understand.

Answer 2

This error occurs because you did not specify "between the inserted values. It is best to use the Parameters collection:

 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 3

It looks like you need to add quotes around the Course Name . Also use SQL parameterized queries so that you are not vulnerable to SQL Injection .

 '" + courseName.Text + "' 

Will be evaluated:

 'Intro to comp' 

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

Answer 4

You need to pass the value of the entire control inside ' Update your sql query as follows:

  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 the number of rows executed on request

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

Check here for more details.

Answer 5

This error probably comes from the Course name field, where you have spaces in the value. To just fix this, you can wrap the values ​​of the TextBoxes in a ' char.

But this is a huge security leak. Currently you should use options, for example, your insert should look like this:

 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.

Answer 6

try it

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

The query syntax is completely invalid.

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