Incorrect Syntax with keyword with

Question

0.00/5 (No votes)

See more:

Please i need help...

I get NO errors when i type few sentences in the CommentsTextbox but when i type long sentences and more words and i try to Update or Insert i get the errors below:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a Semicolon.

this is my Sql String:

SQL

"update set IDNo='" + IDNOTextBox.Text + "',Comments='" + CommentsTextBox.Text + "' where Idno like '" + TextBox1.Text + "%'"

i will appreciate it if i a get an answer.....thanks in advance

Posted 28-Jul-14 5:07am

TugBest

Updated 28-Jul-14 5:12am

Leo Chapiro

v2

Add a Solution

Comments

Leo Chapiro 28-Jul-14 11:13am

And where is here the keyword "with"?

TugBest 28-Jul-14 11:35am

i didnt even use any 'WITH' in the Update statement . But it updates successfully when i type just few words and sentences. But when i Type something like this : : : : : : : :::

"So as you can see; an index is not something publishers put at the end of the book because of tradition. It is included to help readers locate important information contained in the text.
Indexes should support all levels of user subject experience. Some are expert with the book title and can easily understand your index topics and reach what they are looking for, but others are novice readers with the book title and may find difficulties if your index topics are hard to be understood till they reach what they are looking for."""""

WHEN I TYPE somthing sentences and text as many as the few sentences above.. it will give error like::::::

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2014-07-28T05:20:00

Solution 1

Congratulations - you've just introduced a serious security vulnerability[^] into your code! :doh:

Parameterized queries aren't difficult. They will fix this security vulnerability. And they will most likely fix the error you're seeing as well.

VB.NET

Dim connectionString As String = ConfigurationManager.ConnectionStrings("YourConnectionStringName").ConnectionString
Dim commandText As String = "UPDATE YourTableName SET IDNo = @IDNo, Comments = @Comments WHERE IDNo Like @OriginalIDNo + '%'"

Using connection As New SqlConnection(connectionString)
Using command As New SqlCommand(commandText, connection)
    command.CommandType = CommandType.Text
    
    command.Parameters.AddWithValue("@IDNo", IDNoTextBox.Text)
    command.Parameters.AddWithValue("@Comments", CommentsTextBox.Text);
    command.Parameters.AddWithValue("@OriginalIDNo", TextBox1.Text);
    
    connection.Open()
    command.ExecuteNonQuery()
End Using
End Using

Posted 28-Jul-14 5:20am

Richard Deeming

Comments

TugBest 28-Jul-14 13:04pm

this side below gives error like:::::: Object reference not set to an instance of an object.

Dim connectionString As String = ConfigurationManager.ConnectionStrings("provider=sqloledb;Data Source=(local);Initial Catalog=AdmINDB;Integrated Security=SSPI").ConnectionString

Richard Deeming 28-Jul-14 13:06pm

You need to pass the name of the connection string as defined in your configuration file.

If you're just hard-coding the connection string within the application, then change that line to:

Dim connectionString As String = "provider=sqloledb;Data Source=(local);Initial Catalog=AdmINDB;Integrated Security=SSPI"

TugBest 28-Jul-14 13:14pm

Thank very Much Richard for Your answers ...i really appreciate it

but i got another error at this side below like::::::Keyword not supported: 'provider'.

Using connection As New SqlConnection(connectionString)

Richard Deeming 28-Jul-14 13:21pm

You're using an old OLEDB connection string. The simplest fix is to remove the provider=sqloledb; part from your connection string.

Alternatively, you'll need to change the code to use OleDbConnection and OleDbCommand instead of their Sql* equivalents. However, if you do that, you'll lose support for named parameters.

TugBest 28-Jul-14 14:18pm

thank you very much for all you answers ........I do apppreciate your great intellectual knowledges tanks a lot....

Dave Kreskowiak · Accepted Answer · 2014-07-28T05:21:00

Your entire problem is that you're not using parameterized queries in your SQL code.

Google for "sql injection attack[^]" and "C# SQL Parameterized Queries[^]" for information on what you're doing wrong, why it's so bad and a huge security risk, and what to do about it.

Also, you can NOT use the LIKE operator on non-string fields, like your IDNO field. It's probably numeric which is why you're getting the error.