Problem with SQL...

[CCG3]

I am trying to run an SQL statement to delete a record in my Access 2003 database (in VB.Net 2005). In my table I have 2 fields. 1. VisitMRNo-DataType=Text 2. VisitDate- DataType=Date/Time and Format=Short Date Right now, this is the code that I am using… Dim MR As String Dim SelectedVisitDate As Date Dim SQL As String = String.Empty MR = Me.txtMRNo.Text SelectedVisitDate = Me.cbobxVisitDate.Text.ToString() 'initialize database connection Using Connection As New OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Costco\My Documents\Visual Studio Projects\AnesXL.mdb") 'setup SQL command to delete records with matchin info SQL = "Delete * FROM Pat20VisitDate WHERE VisitMRNo='" & MR & "' AND VisitDate='" & SelectedVisitDate & "'" Try 'connect to database and delete the records Connection.Open() Dim Command As New OleDbCommand(SQL, Connection) Command.ExecuteNonQuery() Catch ex As Exception MsgBox("Delete of Visit Date failed") Exit Sub End Try Once it gets to line Command.ExecuteNonQuery I get an Error: Data type mismatch in criteria expression. I have watched both of these variables and they look fine in the Watch Window. How can I tell if what I am passing over is in the wrong format? I really apperciate any help that I can get with this. Thanks!

[Dave Herren]

What is the value of SQL at the time of the exception?

topcoderjax - Remember, Google is your friend.

[CCG3]

Hey TCJ, thanks for your reply. According to the VB watch window this is the value of my SQL..."Delete * FROM Pat20VisitDate WHERE VisitMRNo='8822' AND VisitDate='12/12/2000'"

[Christian Graus]

CCG3 wrote:

VisitMRNo

I'd guess this is a number, and you're passing it as a string ( so remove the quotes )

Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

[CCG3]

No, VisitMRNo is setup as a Text so I am passing it a string. But just so you know, I have already tried that. I have tried this with no quotes on either of them and on neither of them and I still get the same message.

[Dave Herren]

Answered in another post, but for other users with a similar problem # signs need to be around dates. Other rules for access sql text: dates should be the date with pound signs ex. #08/25/06# boolean should be true or false (no quotes) ex. true numbers should be just the number(no quotes) ex. 25 text is enclosed in single quotes ex. 'Test'

topcoderjax - Remember, Google is your friend.