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″

OracleDataProvider: ORA-01861 Literal Does Not Match Format String

Got the above error calling a Stored Procedure via an OracleCommand.ExecuteNonQuery.

Not being an Oracle savant, I Googled the error and found it can come about when an Oracle TO_DATE function call cannot match the string literal with the supplied date picture (Excuse my atavistic lapse into COBOL jargon) in the Format string.

For example, TO_DATE(‘23/02/2009′, ‘DD-MON-YY’) produces ORA-01861 because the Format string specifies three character month and two character years, but received a two character month and a four character year.

In my case. however, the crash occurred on line one of the Stored Proc where a DATE parameter was listed in the contract viz:

CREATE OR REPLACE pCalculateStuff(
inDodgyDate IN Date,
inStudentNumber IN VARCHAR2,
inOrg_Number IN VARCHAR2)

and yet Oracle was still complaining with the above error.

Moreover, the parameters were explicitly named in the OracleParamter array I was using to populate the OracleCommand object which called the Stored Proc. I was expecting the parameters to resolve by name but they didn’t.

What I found was that the order of the parameters in the OracleParameter array must match the order of the parameters in the Stored Procedure, even though the OracleParameters are all explictly named in the .NET code

I had the following:

oraParams(0) = New OracleParameter("inStudentNumber", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(0).Value = studentNum.ToString()
oraParams(1) = New OracleParameter("inOrgTradeAutoNum", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(1).Value = orgTradeAutoNum.ToString()
oraParams(2) = New OracleParameter("inDateProcessing", OracleDbType.Date, ParameterDirection.Input)
oraParams(2).Value = termStartDate

oraCmd.CommandText = strStoredProcName
oraCmd.CommandType = CommandType.StoredProcedure

If (params.Length > 0) Then
For intParamCount = 0 To params.Length - 1
oraCmd.Parameters.Add(params(intParamCount))
Next
End If

' Code crashes here, giving ORA-01861
oraCmd.ExecuteNonQuery()

So, when I called ExecuteNonQuery on the StudentNumber was being bound to inDodgyDate, because it occupied parameter index 0 in the OracleParameters array so that ExecuteNonQuery threw ORA-01861 because ‘211946′ failed to parse into a Date

The fix was merely to re-order the position of the params in the OracleParameters array so that it matched the order in the Stored Procedure viz:

-- Make inDodgyDate the first parameter in the array
oraParams(0) = New OracleParameter("inDodgyDate", OracleDbType.Date, ParameterDirection.Input)
oraParams(0).Value = termStartDate

Lesson Learnt
The name of the OracleParameter is irrelevant when parameter binding. The order in which they are added to the OracleCommand is the only thing that matters.

Addendum 27-March-2009
Looks like I could have save myself a lot of trouble by setting

oraCmd.BindByName = True