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

Try and print out that query1, you could see for yourself. Hint: mind the quotes. — Hans Keﬆing
– Hans Keﬆing, Commented May 14, 2013 at 10:58
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. — Arran
– Arran, Commented May 14, 2013 at 10:58
@Rahul line 34 is cmd1.ExecuteNonQuery(); you can see also in the edited question — yrazlik
– yrazlik, Commented May 14, 2013 at 11:02
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. — Rahul
– Rahul, Commented May 14, 2013 at 11:05

gzaxx · Accepted Answer · 2013-05-14 11:01:04Z

6

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

answered May 14, 2013 at 11:01

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yrazlik Over a year ago

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 Over a year ago

ExecuteNonQuery() returns how many rows were affected so if it returns 0 it means no row has been inserted. More here

Darren · Accepted Answer · 2013-05-14 11:12:42Z

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/

edited May 14, 2013 at 11:12

answered May 14, 2013 at 10:58

Darren

71.2k24 gold badges141 silver badges146 bronze badges

2 Comments

yrazlik Over a year ago

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 Over a year ago

@bigO - ExecuteNonQuery() returns an integer (Number of rows affected) ... you could do: var results = cmd1.ExecuteNonQuery(); And then check if results > 1

oleksii · Accepted Answer · 2013-05-14 11:03:27Z

1

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.

answered May 14, 2013 at 11:03

oleksii

36.1k16 gold badges98 silver badges173 bronze badges

2 Comments

yrazlik Over a year ago

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 Over a year ago

@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)

Mohammad Arshad Alam · Accepted Answer · 2013-05-14 11:12:41Z

1

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.

edited May 14, 2013 at 11:12

answered May 14, 2013 at 11:00

Mohammad Arshad Alam

9,8626 gold badges41 silver badges63 bronze badges

1 Comment

yrazlik Over a year ago

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?

Hans Keﬆing · Accepted Answer · 2013-05-14 11:56:26Z

1

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.

edited May 14, 2013 at 11:56

Hans Keﬆing

39.5k10 gold badges84 silver badges119 bronze badges

answered May 14, 2013 at 11:10

Sagar Hirapara

1,6971 gold badge13 silver badges24 bronze badges

Comments

Rahul · Accepted Answer · 2013-05-14 11:15:29Z

0

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.

edited May 14, 2013 at 11:15

answered May 14, 2013 at 11:00

Rahul

5,6556 gold badges38 silver badges58 bronze badges

3 Comments

yrazlik Over a year ago

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 Over a year ago

you can take a label to show success/failure text... label1.Text="Successfully Inserted"

Alexander Over a year ago

This code is severely vulnerable to sql injection. Avoid building dynamic sql by string concatenation. Use sql parameters if possible.

Evaldas Buinauskas · Accepted Answer · 2015-10-26 07:08:54Z

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

    }

Collectives™ on Stack Overflow

Inserting into table from a form in asp.net

7 Answers 7

2 Comments

2 Comments

2 Comments

1 Comment

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

2 Comments

2 Comments

1 Comment

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related