Any parameter binding error SQL Server 2012 R2

[Former Member]

I'm connecting to SQL Server 2012 R2.

I have a simple query in a data window with a single argument serial_no. When I do a retrieve on the datawindow and enter a value 16299, and run the query I get an error invalid character ':'. when I look at the trace log, the data window has not resolved the argument to the value 16299. I have tried this with Ole DB connection, ADO connection, and SNC SQL Native. I have also tried setting parameters in the connection DisableBind and BindSPInput both 0 and 1. No change. Am I supposed to use a different character like ? or @ instead of colon : ??

Query:

SELECT batch_weight_history.serial_no, batch_weight_history.quantity_required, batch_weight_history.quantity_weighted, batch_weight_history.material_id, batch_weight_history.hand_add, batch_weight_history.batch_no, ABS(batch_no) FROM batch_weight_history WHERE ( BATCH_WEIGHT_HISTORY.SERIAL_NO = :serial_no ) and ( BATCH_WEIGHT_HISTORY.BATCH_NO < 0 )

Trace Log:

/*---------------------------------------------------*/ /* 10/17/2017 18:16 */ /*---------------------------------------------------*/ (00F38EA0): DIALOG CONNECT TO TRACE SNC SQL NATIVE CLIENT(OLE DB): (00F38EA0): LOGID=cgt (00F38EA0): SERVER=CGTSVR-DB (00F38EA0): DBPARM=Provider='SQLNCLI11',Database='CGT',BindSPInput=1,ProviderString='DisableBind=0'

(DBI_DIALOG_CONNECT) (2.694 MS / 2.694 MS) (00F38EA0): (DBI_ADJUST_CONNECT) (0.001 MS / 2.695 MS) (00F38EA0): (DBI_CASE_SENSITIVE) (0.000 MS / 2.695 MS) (00F38EA0): PREPARE: (00F38EA0): SELECT batch_weight_history.serial_no, batch_weight_history.quantity_required, batch_weight_history.quantity_weighted, batch_weight_history.material_id, batch_weight_history.hand_add, batch_weight_history.batch_no, ABS(batch_no) FROM batch_weight_history WHERE ( BATCH_WEIGHT_HISTORY.SERIAL_NO = :serial_no ) and ( BATCH_WEIGHT_HISTORY.BATCH_NO < 0 )(DBI_PREPARE) (0.797 MS / 3.492 MS) (00F38EA0): *** ERROR 102 ***(rc -1) : SQLSTATE = 42000 Microsoft SQL Server Native Client 11.0 Incorrect syntax near ':'.

[Former Member]

Hi Jason,

The dbparm should be like this :

SQLCA.DBParm = "Database='easdemodb',Provider='SQLNCLI11',BindSPInput=1"

HTH.

Abdallah.

[luisdarui]

looks like the error comes from "= :serial_no"

You can try for example enable SQL Profiler trace and check what is the exact statement is sent to SQL Server.

SQL Server parameters have a "@" character, e.g. @p1