The SQL statement works properly in SQL Server database, but errors in vb.net

Question

I need to insert new record into a SQL Server database, but get

Incorrect syntax error

The strange thing is when I try to query the same statement in SQL Server itself, it works properly.

The code in vb.net is as follows:

insertSql = "INSERT INTO Seg_LINE VALUES (" & OBJECTID & ", 'test" + "', '" + "test" + "','" + DrainName + "'," & UID & ")"
logger.Info("insert sql = " + insertSql)
Dim cmdInsert As New SqlClient.SqlCommand(insertSql, Sqlconnection) 
cmdInsert.ExecuteNonQuery()

The OBJECTID and UID are number parameters.

I cannot figure out what's wrong with my code, I am using vb.net(vs2102).

This code is vulnerable to sql injection attacks... it's practically begging to get hacked. — Joel Coehoorn
– Joel Coehoorn, Commented Oct 17, 2014 at 3:21
Hi, the sql statement logged is : insert sql = INSERT INTO Seg_LINE VALUES (635,'test','test','Kallang',93571) — user30643
– user30643, Commented Oct 17, 2014 at 3:55

Joel Coehoorn · Accepted Answer · 2014-10-17 04:08:09Z

2

Most likely you have a DrainName value with a single quote in it. You're lucky the query is just failing, and not executing unwanted commands on your DB server. Don't use string concatenation like that to build queries! You need to use query parameters, like this:

insertSql = "INSERT INTO Seg_LINE VALUES (@ObjectID, 'test', 'test', @DrainName, @UID)"
logger.Info("insert sql = " + insertSql)
Dim cmdInsert As New SqlClient.SqlCommand(insertSql, Sqlconnection)
'I'm guessing at these parameter types. Use the actual db types of the columns
cmdInsert.Parameters.Add("@ObjectID", SqlDbType.Int).Value = OBJECTID
cmdInsert.Parameters.Add("@DrainName", SqlDbType.NChar, 50).Value = DrainName
cmdInsert.Parameters.Add("@UID", SqlDbType.Int).Value = UID
cmdInsert.ExecuteNonQuery()

Changing the code this way will also likely fix your syntax error.

edited Oct 17, 2014 at 4:08

answered Oct 17, 2014 at 3:26

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

3 Comments

user30643 Over a year ago

Hi Joel, thanks a lot for the reply, but the result is the same. When I tried to create a new function and use the same code, it works properly.

user30643 Over a year ago

Another thing to mention is although there has error, the data is store into the db table, but the program not proceed further.

user30643 Over a year ago

Hi Joel, when I change ExecuteNonQuery to ExecuteScalar, it works properly, thanks for the attention.

Collectives™ on Stack Overflow

The SQL statement works properly in SQL Server database, but errors in vb.net

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related