CODEWITHSUNDEEP
c#asp.netsqldatabase
yrazlik
yrazlik

Reputation: 10777

Inserting into table from a form in asp.net

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

Upvotes: 5

Views: 71886

Answers (7)

Nitin
Nitin

Reputation: 1

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

    }

Upvotes: 0

Sagar Hirapara
Sagar Hirapara

Reputation: 1697

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.

Upvotes: 1

Rahul
Rahul

Reputation: 5636

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.

Upvotes: 0

Darren
Darren

Reputation: 70718

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/

Upvotes: 3

Mohammad Arshad Alam
Mohammad Arshad Alam

Reputation: 9862

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.

Upvotes: 1

oleksii
oleksii

Reputation: 35895

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.

Upvotes: 1

gzaxx
gzaxx

Reputation: 17590

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

Upvotes: 6

Related Questions