Inserting into table from a form in asp.net

Question

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:

enter image description here

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:

cmd1.ExecuteNonQuery();

Can anyone help me with this error?

Thanks

• Hans Kesting: Try and print out that query1, you could see for yourself. Hint: mind the quotes.
• Arran: This is part of the problem with mangling SQL queries like you are, you run into situations where the SQL query doesn't 'quite' match up. You should use parameterised queries and let the provider do most of the work for you. I would take the query you are generating and run it on the database yourself - you'll see what the problem is there.
• Rahul: show me the Line: 34 of CreateCourse.aspx.cs page
• yrazlik: @Rahul line 34 is cmd1.ExecuteNonQuery(); you can see also in the edited question
• Rahul: just change your insert query as i mentioned and check that you found that error again or nor,if your datafield type id nvarchar or varchar then use '"++"' like that in your query for that field.

Answer 1

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

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

• yrazlik: Thank you, i solved the problem. I have one more little question: I want to do something like, if (inserting into database is succesfull) do something else do another thing. Is there a boolean function like this to check?
• gzaxx: ExecuteNonQuery() returns how many rows were affected so if it returns 0 it means no row has been inserted. More here

Answer 2

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

'" + courseName.Text + "'

Will evaluate to:

'Intro to comp'

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

• yrazlik: Thank you, i solved the problem. I have one more little question: I want to do something like, if (inserting into database is succesfull) do something else do another thing. Is there a boolean function like this to check?
• Darren: @bigO - ExecuteNonQuery() returns an integer (Number of rows affected) ... you could do: var results = cmd1.ExecuteNonQuery(); And then check if results > 1

Answer 3

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

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.

• yrazlik: Thank you, i solved the problem. I have one more little question: I want to do something like, if (inserting into database is succesfull) do something, else do another thing. Is there a boolean function like this to check?
• oleksii: @bigO I think you can use 2 things. 1 Is to use the return value of the cmd1.ExecuteNonQuery, from msdn: For ... INSERT ... statements, the return value is the number of rows affected by the command. ... For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.. And 2 you can catch any exception to do error handling, if this is what you need (but don't use exceptions to control normal execution flow)

Answer 4

You have to pass value of all control inside 'Update your sql query like 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 +"'" + ")";

//returns number of row effected by query

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

check here for more details.

• yrazlik: Thank you, i solved the problem. I have one more little question: I want to do something like, if (inserting into database is succesfull) do something else do another thing. Is there a boolean function like this to check?

Answer 5

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.

Answer 6

Modify your Insert query like

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 ExecuteNonQuery will return you 1 else 0, so by using return's value you may check and apply your condition.

Hope you understand.

• yrazlik: Thank you, i solved the problem. I have one more little question: I want to do something like, if (inserting into database is succesfull) do something else do another thing. Is there a boolean function like this to check?
• Sobhan: you can take a label to show success/failure text... label1.Text="Successfully Inserted"
• Alexander: This code is severely vulnerable to sql injection. Avoid building dynamic sql by string concatenation. Use sql parameters if possible.

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