CODEWITHSUNDEEP
sqlms-accesssql-delete
JPJedi
JPJedi

Reputation: 1508

Error when executing SQL

I get the following error when I try and execute the code down below. I have added the SQL string I pass as well. What am I missing?

Error:

Syntax error (missing operator) in query expression 'TOURID = 25e5eed3-ea84-4c30-a660-2c880253da60'

My query,

DELETE * FROM TOURS WHERE TOURID = 25e5eed3-ea84-4c30-a660-2c880253da60;

My code,

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\_Development\Data\filename.accdb;Persist Security Info=False;"
Dim DBConnection As New System.Data.OleDb.OleDbConnection(connectionString)
Try
    DBConnection .Open()
    Dim cmd As New System.Data.OleDb.OleDbCommand(sql, DBConnection )
    Dim sqlAdapterTOURS As New System.Data.OleDb.OleDbDataAdapter(cmd)
    cmd.CommandType = CommandType.Text
    cmd.ExecuteNonQuery()
Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    DBConnection .Close()
End Try

Upvotes: 0

Views: 126

Answers (4)

David-W-Fenton
David-W-Fenton

Reputation: 23067

Access/Jet/ACE is not very GUID-friendly. Michael Kaplan wrote about this many years ago:

Replication and GUIDs, the Good, the Bad, and the Ugly

Depending on the context, you may need to use the VBA functions for working with GUIDs, i.e., StringFromGUID() and GUIDFromString(). I'm not sure exactly what you have to do in a SQL context, though. I do know you can't do a join between two GUID fields (you have to use implicit joins, i.e., using a WHERE clause instead).

Upvotes: 1

davek
davek

Reputation: 22925

Guess: your GUID-type entry should be quoted, maybe?:

DELETE FROM TOURS WHERE TOURID = '25e5eed3-ea84-4c30-a660-2c880253da60'

Upvotes: 1

rosscj2533
rosscj2533

Reputation: 9323

Your value should be surrounded by single quotes, like this:

DELETE * FROM TOURS WHERE TOURID = '25e5eed3-ea84-4c30-a660-2c880253da60';

Upvotes: 3

Nick Craver
Nick Craver

Reputation: 630627

You need to quote when working with GUIDS like this:

DELETE * 
FROM TOURS 
WHERE TOURID = '25e5eed3-ea84-4c30-a660-2c880253da60';

Upvotes: 5

Related Questions