OLEDB connection syntax error in SQL

[DEK]

Getting my feet wet with OLEDB connection code. Maybe I am tired and not seeing the why, but I am receiving an error "Syntax error (Missing Operator) in query expression. Just started "learning" what I can regarding the oleDataAdapter and the DataSet....currently playing with this snippet of code. What I am trying to do is grab all corresponding fields to the DB record with a ModelNumber of 39a and place them into a dataset for retrieval. I will not be manipulating the data, just a pull and display on a form. Should I be using a Datatable instead of a dataset? %Thought I read that somewhere in this forum.

The code throws no syntax error using 'SQL = "SELECT * FROM Models "

but fails using: SQL = "Select * FROM Models WHERE ModelNumber = 39a"

My apologies for the formatting of my question and code.

Thank you

Public Sub CmdDBConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdDBConnect.Click
Dim Con As New OleDb.OleDbConnection
Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AK_TRAIN_DotNet\Data\MainDatabase.mdb"
'"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FPAnalysisTool.mdb"
Dim Cmd As New OleDb.OleDbCommand
Dim SQL As String ' The SQL statement to get the data
Dim DS As New DataSet ' Holds a copy of the data
Dim DA As OleDb.OleDbDataAdapter


Con.Open()


'SQL = "SELECT * FROM Models "


SQL = "Select * FROM Models WHERE ModelNumber = 39a"


DA = New OleDb.OleDbDataAdapter(SQL, Con)

MsgBox("The database Connection is open")



DA.Fill(DS, "Occurence")



Con.Close()
MsgBox("The database Connection is Closed")

End Sub

 

[jmcilhinney]

Your model number is a string and string literals need to be enclosed in single quotes in SQL code. That said, in a real-world app you'll usually want that value to be variable, in which case you should use a parameter. Follow the Blog link in my signature and check out my submission on ADO.NET parameters for more info.