Incorrect Syntax Near “.” during DataAdapter.Fill

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

Tags: ,

2 Responses to “Incorrect Syntax Near “.” during DataAdapter.Fill”

  1. Bob Says:

    Thanks very much for this solution, trying to narrow this error down was driving me crazy for about two hours.

    Muchly appreciated.

    • baraholka1 Says:

      Bob,

      Pleased to be instrumental in the restoration of your sanity.
      My ferrett and I are also perfectly normal.

      Regards,

      Barra

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: