SAP SQL Anywhere

Question

Hello,

We are trying to use named parameters to execute stored procedures but regardless of what we construct, they seem to be ignored and the order of the parameters is observed instead.

As an example below, the iclientid is the 32nd parameter but we are only passing in 5 parameters, so iclientid is going into the 5th parameter which happens to be Add1, obviously not what we want.

var customerParams = new[]
{
    new SAParameter("@icustomerid", SADbType.Char)
    {
        Value = customerId
    },
    new SAParameter("@isite", SADbType.Char)
    {
        Value = siteId
    },
    new SAParameter("@isurname", SADbType.Char)
    {
        Value = surname
    },
    new SAParameter("@iforenames", SADbType.Char)
    {
        Value = firstName
    },
    new SAParameter("@ititle", SADbType.Char)
    {
        Value = title               
    },
    new SAParameter("@iclientid", SADbType.Char)
    {
        Value = trackerId
    }
};

Are we doing something wrong, any help most appreciated!

Jeavon

Answer 1

Hi, to follow on from what Jeavon said..

We pass those parameters to the following method which executes a stored procedure

public string ExecuteScalar(string storeProcedureName, SAParameter[] parameters)
{
    string returnValue = String.Empty;

    using (var database = new SAConnection(_connectionString))
    {
        database.Open();

        using (var command = database.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storeProcedureName;
            command.Parameters.AddRange(parameters);

            var response = command.ExecuteScalar();

            if (response != null)
                returnValue = response.ToString();
        }
    }

    return returnValue;
}

It seems to ignore the name of the parameters and just put them in the order provided. We are updating a record and only really want to provide the parameters to update and not the whole range.

I've removed the '@' symbols as suggested above and changed our connection string so that it is in the following format (after a bit of digging in the docs)

HOST=222.222.222.2:222;DBN=dbname;UID=username;PWD=password;

This is connecting fine. Will this make it use the ADO.NET provider rather than go through the OLE DB? Or is there another step to it?

Answer 2

0

Hi All,

Thanks for your help here. The web guys are off to do some changes and try to make calls directly without using the steps of the embedded processes. Hopefully that will solve the problem.

Have a great Easter.

Alasdair

permanent link

answered 24 Mar '16, 07:32

RADicalSYS
331●9●15●30
accept rate: 9%

flat view

SAP SQL Anywhere

SqlAnywhere v12 (SyBase v10) ASP.NET stored procedure named parameters being ignored

Follow this question

Related questions