Ho to solve syntax error in insert into statment when I try to insert data in database in ASP.NET

Question

1.00/5 (1 vote)

See more:

I am trying to insert a data using oledbCommand in my database in asp.net.I am taking the input through textboxes and inserting them using the insert statement.The insert code is writtern on button click event.But when i click on the button its given me error that "syntax error in insert into statement"

ASP.NET

protected void btn_post_Click(object sender, EventArgs e)
        {
            con.Open();
           
            cmd= new OleDbCommand("insert into tbl_blogs(User,Book_name,Blogs) values ('" + txt_user.Text + "' ,'" + txt_bnm.Text + "' ,'" + txt_review.Text + "') ",con);
         
            cmd.ExecuteNonQuery();

            con.Close();


        }

All the fields are short text and the names are also correct.

Here is the access database:-

This is the tbl_blogs table in which i am inserting.The no field is autonumber.
No	User	 Book_name	     Blogs
1	Khadija	  Harry potter	 This is one the best books that I have ever read.

THIS IS THE ERROR MESSAGE :

Syntax error in INSERT INTO statement.
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.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error:

Line 26: cmd= new OleDbCommand("insert into tbl_blogs(User,Book_name,Blogs) values ('" + txt_user.Text + "' ,'" + txt_bnm.Text + "' ,'" + txt_review.Text + "') ",con);
Line 27: // cmd.Connection = con;
Line 28: cmd.ExecuteNonQuery();
Line 29:
Line 30: con.Close();

Source File: C:\Users\HP\Desktop\khadija visual\practicewebsite\practicewebsite\blogwrite.aspx.cs Line: 28

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1216113
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +256
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +216
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +60
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +164
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +112
practicewebsite.blogwrite.btn_post_Click(Object sender, EventArgs e) in C:\Users\HP\Desktop\khadija visual\practicewebsite\practicewebsite\blogwrite.aspx.cs:28
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9796242
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +211
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1696

What I have tried:

I tried using commandtext but then got the error that object instance set to null.Cant seem to find what is causing the problem.

The value that i am trying to insert are for user = sam,book_name = Da vinci code,blog ="Best book by  far".

Posted 16-Apr-21 23:05pm

Member 15148158

Updated 16-Apr-21 23:48pm

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2021-04-16T23:12:00

Quote:
Ho to solve syntax error in insert into statment when I try to insert data in database in ASP.NET

This is SQL injection, we can help you because the syntax of your INSERT depend on the values of textboxes which we don't know.

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

OriginalGriff · Answer 2 · 2021-04-16T23:48:00

To explain what Patrice is saying: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Ho to solve syntax error in insert into statment when I try to insert data in database in ASP.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Ho to solve syntax error in insert into statment when I try to insert data in database in ASP.NET

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us