CODEWITHSUNDEEP
sql-servervb.net
user3125278
user3125278

Reputation: 91

SQL Server "Incorrect syntax near" WHERE statement

I've researched and researched... yet to find a solution. I've read people having similar trouble because of the encoding, but I've tried retyping the query and even used convert to UTF-8 inside Notepad++. Any ideas?

Error:

Incorrect syntax near 'NEW'.

Query:

delete from [orgDefaults] 
where ([orgcode] = N'NEW') 
  and ([ctlName] = N'AllowReportables')

This is being executed inside a VB.NET program I've created using this OLEDB driver:

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & updates_mdb & ";Jet OLEDB:Database Password=" & Settings.Password & ";")

Upvotes: 0

Views: 289

Answers (2)

SQL Police
SQL Police

Reputation: 4196

You are using the wrong driver to connect to SQL server.

You are using the MS Access Jet Engine. But this uses another SQL syntax, that's why it does not work.

Just use the SQL Server OLEDB driver, and it will work.

Upvotes: 1

T.S.
T.S.

Reputation: 19340

Just for hack of it , try this:

Using conn As New SqlConnection("sqlServer Conn string - connectionstrings.com")
    Dim sql As String = "DELETE FROM [orgDefaults] WHERE [orgcode] = @1 AND [ctlName] = @2"
    Using cmd As New SqlCommand(sql, conn)
        cmd.Parameters.AddWithValue("@1", "NEW")
        cmd.Parameters.AddWithValue("@2", "AllowReportables")
        cmd.CommandType = CommandType.Text
        conn.Open()
        Dim retVal As Integer = cmd.ExecuteNonQuery() 
        If retVal > 0 Then 
            Debug.WriteLine("Success - deleted 1 or more records")
            ' remeber, retVal may not match num of rows deleted. 
            ' Rather, it indicates total rows affected
        Else
            Debug.WriteLine("Still Success - deleted Nothing")
        End If
    End Using
End Using

This should give you better picture [if you have some "special" issue] because Sql executed this way will match your values and field types better.

Upvotes: 0

Related Questions