C# Sybase AseClient Invalid amount of parameters

Question

I'm using prepared statement for the sql below

SELECT
t.baseSystem 'source',
count(t.filenumber) 'fileCount'
FROM orders t JOIN customer c ON t.customer = c.customerid
WHERE
t.entrydate = @loaddate
AND (t.baseSystem in ('GFQWE','GHOTR','EFXSDE','EBDBDL','AUTO','MDFX')
            OR t.baseSystem LIKE 'STPALLOC_%')
AND t.trdsource in (0,22)
AND t.refType NOT LIKE '%Far%'
AND c.type IN ('BANK','NBNK','BFAC')
AND t.reference NOT IN ('QDisk', 'DSSDOL', 'DEFG', 'FGYT')
GROUP BY t.baseSystem

UNION

SELECT
CASE 
            WHEN t.baseSystem = 'GFQWE' THEN 'FGQC'
            WHEN t.baseSystem = 'GHOTR' THEN 'OUY UI'
            ELSE t.baseSystem
END 'source',
count(t.filenumber) 'fileCount'
FROM orders t JOIN customer c ON t.customer = c.customerid
WHERE
t.entrydate = @loaddate
AND t.baseSystem in ('GFQWE','GHOTR')
AND t.broker = 'P'
AND t.trdsource in (0,22)
AND t.refType NOT LIKE '%Far%' 
AND c.type IN ('BANK','NBNK','BFAC')
AND t.reference NOT IN ('QDisk', 'DSSDOL', 'DEFG', 'FGYT')
GROUP BY
CASE 
                WHEN t.baseSystem = 'GFQWE' THEN 'FGQC'
                WHEN t.baseSystem = 'GHOTR' THEN 'OUY UI'
                ELSE t.baseSystem
END

C# code

DataTable result = new DataTable();
AseConnection aseConnection = GetAseConnection(connection);
try
{
      AseCommand command = new AseCommand(sql, aseConnection);
      command.CommandText = sql;
      AseParameter parameter = new AseParameter("@loaddate", strDate);
      command.Parameters.Add(param);
      command.Prepare();
      using (AseDataAdapter adapter = new AseDataAdapter(command))
      {
          adapter.Fill(result);
      }
 }
 finally
 {
      aseConnection.Close();
  }
  return result;

This code works fine on my local, but throws the below error on server. Having a hard time since i cannot reproduce the same error on my local.

Sybase.Data.AseClient.AseException: Invalid amount of parameters. at Sybase.Data.AseClient1.AseCommand.CheckResult(Int32 res) at Sybase.Data.AseClient1.AseCommand.SetCommandStatement(CommandBehavior commandBehavior) at Sybase.Data.AseClient1.AseCommand.Execute(CommandBehavior commandBehavior) at Sybase.Data.AseClient1.AseCommand._ExecuteReader(CommandBehavior commandBehavior) at Sybase.Data.AseClient1.AseCommand.ExecuteReader(CommandBehavior commandBehavior) at Sybase.Data.AseClient.AseCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

Have you confirmed that the database on the server has the same schema? — Kevin Raffay
yes, i tried to run it on the same db from my local and it works, but throws up when running on server — SDF

LuisM LuisM · Accepted Answer · 2017-12-07T22:01:41

Adding ;NamedParameters=false to the connection string as mentioned in the link below worked for me

Problems with running Sybase SQL Script from C# - too many parameters

C# Sybase AseClient Invalid amount of parameters

1 Answers