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:

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

Solution 1

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.

Solution 2

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

Solution 3

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/

Solution 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.

Solution 5

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.

View more solutions