Error when executing SQL

Question

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

Nick Craver · Accepted Answer · 2010-03-05 19:21:00Z

5

You need to quote when working with GUIDS like this:

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

answered Mar 5, 2010 at 19:21

Nick Craver

631k138 gold badges1.3k silver badges1.2k bronze badges

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

Comments

rosscj2533 · Accepted Answer · 2010-03-05 19:21:08Z

3

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

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

answered Mar 5, 2010 at 19:21

rosscj2533

9,3437 gold badges41 silver badges58 bronze badges

Comments

davek · Accepted Answer · 2010-03-05 19:21:47Z

1

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

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

answered Mar 5, 2010 at 19:21

davek

23k11 gold badges80 silver badges97 bronze badges

Comments

David-W-Fenton · Accepted Answer · 2010-03-05 21:02:06Z

1

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).

answered Mar 5, 2010 at 21:02

David-W-Fenton

23.2k4 gold badges48 silver badges59 bronze badges

Collectives™ on Stack Overflow

Error when executing SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related