Posts Tagged ‘Reptilian Brain Stem’

Incorrect Syntax Near “.” during DataAdapter.Fill

September 24, 2009

Warning: Some of the links in this post may distress Cat Fanciers.

What Have I Done To Deserve This ?
Incorrect Syntax near “.” on a DataAdapter Fill means the SQL for your Select Command in your SQLCommand object has a syntax error in it…same as what I did. The bit in the double quotes after ‘Near’ shows what caused the SQL Parser to choke on its own giblets. Hey, look on the bright side. Mine happened while I was doing a practical test during a job interview.

Due to its initial obscurity and brevity, this error caused stage one panic (Brain leaking out of nostil, inflamed Hello Kitty, gargantuan screaming noise from reptilian brain stem), but a few Googles put me right.

How To Fix It

Well, correct the syntax error in the SQL obviously.

Mine came about through a bad concatenation. Like so:

' Create a SqlConnection to the Northwind database.
Using connection As SqlConnection = New SqlConnection( _
connectionString)

' Create a SqlDataAdapter for the Suppliers table.
Dim suppliersAdapter As SqlDataAdapter = _
New SqlDataAdapter()

' A table mapping names the DataTable.
suppliersAdapter.TableMappings.Add("Table", "Suppliers")

' Open the connection.
connection.Open()
Console.WriteLine("The SqlConnection is open.")

'Bad concatenation here
Dim commandString As String = _
"SELECT SupplierID, CompanyName FROM dbo.Suppliers" & _
"WHERE supplierID > 100"

' Create a SqlCommand to retrieve Suppliers data.
Dim suppliersCommand As SqlCommand = New SqlCommand( _
commandString, connection)
suppliersCommand.CommandType = CommandType.Text

' Set the SqlDataAdapter's SelectCommand.
suppliersAdapter.SelectCommand = suppliersCommand

' Fill the DataSet.
Dim dataSet As DataSet = New DataSet("Suppliers")
'Incorrect Syntax Crash Here
suppliersAdapter.Fill(dataSet)

You Bad Concatenation, You

As you can see above, there is no space between my WHERE clause and the SELECT clause.

The resulting command was thus:
“SELECT SupplierID, CompanyName FROM dbo.SuppliersWHERE supplierID > 100”

There is no table called dbo.SuppliersWHERE and no where clause in the query at all.
Fixing it entailed simply inserting a space at the end of the SELECT clause.

Dim commandString As String = _
“SELECT SupplierID, CompanyName FROM dbo.Suppliers “ & _
“WHERE supplierID > 100”

Advertisements