communication error from aSaCommand.ExecuteScalar()

[Former Member]

the target stored proc has been tested from isql. the c# code calling the SP is as follows

---

internal static bool splits_ins(ref long? Split_id, string Fi_Sym, DateTime split_dt, short From_Qty, short to_Qty,
    DateTime? Process_dt, string Splt_remarks, out byte[] time_stamp, out string updated_by, out string Msg)
{
    time_stamp = null; updated_by = ""; Msg = ""; 
    SAConnection myConnection = ModuleDb.myConnection;

    SACommand myCmd = new SACommand("dba.splits_ins", myConnection);
    myCmd.CommandType = System.Data.CommandType.StoredProcedure;

    if (myConnection.State.CompareTo(ConnectionState.Broken) == 0 |
    myConnection.State.CompareTo(ConnectionState.Closed) == 0)
        myConnection.Open();

    #region "splits_ins_Parm setup"
    SAParameter parm0 = new SAParameter("@Split_id", SADbType.BigInt);
    parm0.Precision = 0;
    parm0.Scale = 0;
    parm0.Size = 8;
    parm0.Direction = ParameterDirection.InputOutput;
    parm0.IsNullable = false;
    parm0.SourceColumnNullMapping = false;
    parm0.Value = Split_id;
    myCmd.Parameters.Add(parm0);

    SAParameter parm1 = new SAParameter("@Fi_Sym", SADbType.VarChar);
    parm1.Precision = 0;
    parm1.Scale = 0;
    parm1.Size = 8;
    parm1.Direction = ParameterDirection.Input;
    parm1.IsNullable = false;
    parm1.SourceColumnNullMapping = false;
    parm1.Value = Fi_Sym;
    myCmd.Parameters.Add(parm1);

    SAParameter parm2 = new SAParameter("@split_dt", SADbType.Date);
    parm2.Precision = 0;
    parm2.Scale = 6;
    parm2.Size = 4;
    parm2.Direction = ParameterDirection.Input;
    parm2.IsNullable = false;
    parm2.SourceColumnNullMapping = false;
    parm2.Value = split_dt;
    myCmd.Parameters.Add(parm2);

    SAParameter parm3 = new SAParameter("@From_Qty", SADbType.SmallInt);
    parm3.Precision = 0;
    parm3.Scale = 0;
    parm3.Size = 2;
    parm3.Direction = ParameterDirection.Input;
    parm3.IsNullable = false;
    parm3.SourceColumnNullMapping = false;
    parm3.Value = From_Qty;
    myCmd.Parameters.Add(parm3);

    SAParameter parm4 = new SAParameter("@to_Qty", SADbType.SmallInt);
    parm4.Precision = 0;
    parm4.Scale = 0;
    parm4.Size = 2;
    parm4.Direction = ParameterDirection.Input;
    parm4.IsNullable = false;
    parm4.SourceColumnNullMapping = false;
    parm4.Value = to_Qty;
    myCmd.Parameters.Add(parm4);

    SAParameter parm5 = new SAParameter("@Process_dt", SADbType.Date);
    parm5.Precision = 0;
    parm5.Scale = 6;
    parm5.Size = 4;
    parm5.Direction = ParameterDirection.Input;
    parm5.IsNullable = false;
    parm5.SourceColumnNullMapping = false;
    parm5.Value = Process_dt;
    myCmd.Parameters.Add(parm5);

    SAParameter parm6 = new SAParameter("@Splt_remarks", SADbType.VarChar);
    parm6.Precision = 0;
    parm6.Scale = 0;
    parm6.Size = 255;
    parm6.Direction = ParameterDirection.Input;
    parm6.IsNullable = false;
    parm6.SourceColumnNullMapping = false;
    parm6.Value = Splt_remarks;
    myCmd.Parameters.Add(parm6);

    SAParameter parmTimestamp = new SAParameter("@time_stamp", SADbType.varbinary);
    parmTimestamp.Precision = 0;
    parmTimestamp.Scale = 6;
    parmTimestamp.Size = 8;
    parmTimestamp.Direction = ParameterDirection.Output;
    parmTimestamp.IsNullable = false;
    parmTimestamp.SourceColumnNullMapping = false;
    //parmTimestamp.Value = time_stamp;
    myCmd.Parameters.Add(parmTimestamp);

    SAParameter parmUpd_by = new SAParameter("@updated_by", SADbType.VarChar);
    parmUpd_by.Precision = 0;
    parmUpd_by.Scale = 0;
    parmUpd_by.Size = 16;
    parmUpd_by.Direction = ParameterDirection.Output;
    parmUpd_by.IsNullable = false;
    parmUpd_by.SourceColumnNullMapping = false;
    //parmUpd_by.Value = updated_by;
    myCmd.Parameters.Add(parmUpd_by);

    SAParameter parmMsg = new SAParameter("@Msg", SADbType.VarChar);
    parmMsg.Precision = 0;
    parmMsg.Scale = 0;
    parmMsg.Size = 250;
    parmMsg.Direction = ParameterDirection.InputOutput;
    parmMsg.IsNullable = false;
    parmMsg.SourceColumnNullMapping = false;
    myCmd.Parameters.Add(parmMsg);

    #endregion "splits_ins_Parm setup"

    Msg = ""; bool bOk = false; Split_id = null;
    time_stamp = null; int iRowAffected = 0;
    updated_by = null;
    try
    {
        //iRowAffected = myCmd.ExecuteNonQuery();
        iRowAffected = (int)myCmd.ExecuteScalar();
        bOk = true;
    }
    catch (Exception er){ Msg=er.Message; Split_id = null;}
    myConnection.Close();
    myCmd.Dispose();

    if (bOk)
    {
        Split_id = (long)parm0.Value;
        Msg = (string)parmMsg.Value;

        time_stamp = (DateTime)parmTimestamp.Value;
        updated_by = (string)parmUpd_by.Value;
    }
    return bOk;
}

---

the stored proc is ALTER PROCEDURE "dba"."Splits_ins"(inout @Split_id bigint, in @FI_Sym VARCHAR(8), in @Split_dt DATE, in @From_Qty tinyint, in @TO_Qty tinyint, in @Process_dt DATE default null, in @Splt_remarks VARCHAR(255) default null, out @TIME_stamp TIMESTAMP default null, out @updated_by VARCHAR(16) default null, out @msg varchar(250)) AS BEGIN // 140301 tested select dba._getOsUserName(), current timestamp, '' into @updated_by, @TIME_stamp,@Msg if exists ( select 1 from dba.splits where Fi_Sym =@Fi_Sym and split_dt=@split_dt) begin set @msg='duplicate row - ignored' return $f_duplicateKey() end

insert into "dba"."Splits"(Split_id, Fi_Sym, split_dt, From_Qty, to_Qty, Process_dt,
 Splt_remarks, time_stamp, updated_by)
Values(@Split_id, @Fi_Sym, @split_dt, @From_Qty, @to_Qty, @Process_dt,
  @Splt_remarks, @time_stamp, @updated_by)
if (@@rowcount > 0)
begin
    select @@identity into @Split_id
    commit
end
else

begin rollback set @msg = 'failed to update into dba.splits with split_ID='||@Split_id||', Fi_Sym=' ||@Fi_Sym||', split_dt'||', split_dt='||@split_dt||', From_Qty='||@From_Qty ||',... SqlCode='||SqlCode||', error='|| @@error ||', SQLSTATE='|| @@sqlstatus return @@error end

return 1

END /* test Begin declare @Split_id bigint declare @FI_sym varchar(8) declare @Split_dt datetime declare @From_Qty tinyint declare @TO_Qty tinyint declare @Process_dt datetime declare @Splt_remarks varchar(255) declare @TIME_stamp timestamp declare @updated_by varchar(16) select null, 'BCE.TO','1948-10-04', 1,4,null,null,null, null into @Split_id, @FI_Sym, @Split_dt,@From_Qty,@to_Qty,@Process_dt,@Splt_remarks, @TIME_stamp,@updated_by

exec Splits_ins @Split_id, @FI_Sym, @Split_dt,@From_Qty,@to_Qty,@Process_dt,@Splt_remarks, @TIME_stamp,@updated_by end */

---

I actually tried different approach for the time stamp, treated as object and datetime in c# but all end up the same communication errors.

what did I go wrong?

[VolkerBarth]

If you expect (or use) a return value in your stored procedure then shouldn't there be a parameter with direction "ReturnValue"?

---

I ain't saying this is the cause of the error here. What exact message are you getting?

[chris_keating]

Previously, I posted that the time_stamp column was being handled as BINARY in the code sample provided but in SQL Anywhere it is a timestamp. The actual issue appears to be that there is multiple out parameters. If you pass the time_stamp as a binary and there are multiple out parms, the error is a communication error - the connection is in fact dropped. If you pass the time_stamp as a DateTime (as it should be), the exception reported is "Object reference not set to an instance of an object."

I will investigate this further and report back.

[chris_keating]

I can reproduce the connection issue under specific conditions (a mismatched direction where the server is handling the parm as in and the client is handling the parm as Output).

You should use DateTime for timestamp in SQL Anywhere. Timestamps are date time values not binaries.

You will also need to return a result to ExecuteScalar. It does not handle return parameters.

If you were to have include a "SELECT @Split_id" which returns a resultset in TSQL, things should work better. Currently, you assigning @@identity to the first out parm.

[Former Member]

thx to everyone. the Communication error exception on myCmd.ExecuteScalar() was partly parameter type mismatch and isnullable not set properly while there was no result, result set and declaration of return type in will contribute to null exception from myCmd.ExecuteScalar().

here is almost working code - almost because no syntax or ovbisour runtime error however, the value show up in the timestamp value is incorrect (shown as short datetime ...1:53 A no second totally diferrent from isql query

---

ALTER PROCEDURE "dba"."Splits_ins"(inout @Split_id bigint, in @Fi_Sym VARCHAR(8), in @split_dt DATE, in @From_Qty tinyint, in @to_Qty tinyint, in @Process_dt DATE default null, in @Splt_remarks VARCHAR(255) default null, out @time_stamp TIMESTAMP default null, out @updated_by VARCHAR(16) default null, out @Msg varchar(250)) AS BEGIN // 140301 tested select dba._getOsUserName(), current timestamp, '' into @updated_by, @time_stamp,@Msg if exists ( select 1 from dba.splits where Fi_Sym =@Fi_Sym and split_dt=@split_dt) begin set @Msg='duplicate row - ignored' return $f_duplicateKey() end

insert into "dba"."Splits"(Split_id, Fi_Sym, split_dt, From_Qty, to_Qty, Process_dt,
 Splt_remarks, time_stamp, updated_by)
Values(@Split_id, @Fi_Sym, @split_dt, @From_Qty, @to_Qty, @Process_dt,
  @Splt_remarks, @time_stamp, @updated_by)
if (@@rowcount > 0)
begin
    select @@identity into @Split_id
    commit
end
else

begin rollback set @Msg = 'failed to update into dba.splits with split_ID='||@Split_id||', Fi_Sym=' ||@Fi_Sym||', split_dt'||', split_dt='||@split_dt||', From_Qty='||@From_Qty ||',... SqlCode='||SqlCode||', error='|| @@error ||', SQLSTATE='|| @@sqlstatus return @@error end

return 1

END /* test Begin declare @Split_id bigint declare @Fi_sym varchar(8) declare @split_dt datetime declare @From_Qty tinyint declare @To_Qty tinyint declare @Process_dt datetime declare @Splt_remarks varchar(255) declare @time_stamp timestamp declare @updated_by varchar(16) select null, 'BCE.TO','1948-10-04', 1,4,null,null,null, null into @Split_id, @Fi_Sym, @split_dt,@From_Qty,@to_Qty,@Process_dt,@Splt_remarks, @time_stamp,@updated_by

exec Splits_ins @Split_id, @Fi_Sym, @split_dt,@From_Qty,@to_Qty,@Process_dt,@Splt_remarks, @time_stamp,@updated_by end */

---

  internal static int splits_ins(ref long? Split_id, string Fi_Sym, DateTime 
        split_dt, short From_Qty, short to_Qty,
        DateTime? Process_dt, string Splt_remarks, out DateTime? time_stamp, 
        out string updated_by, out string Msg)
    {
        SAConnection myConnection = ModuleDb.myConnection;

        SACommand myCmd = new SACommand("dba.splits_ins", myConnection);
        myCmd.CommandType = System.Data.CommandType.StoredProcedure;

        if (myConnection.State.CompareTo(ConnectionState.Broken) == 0 |
        myConnection.State.CompareTo(ConnectionState.Closed) == 0)
            myConnection.Open();

        #region "split_ins_Parm setup"
        SAParameter parm0 = new SAParameter("@Split_id", SADbType.BigInt);
        parm0.Precision = 0;
        parm0.Scale = 0;
        parm0.Size = 8;
        parm0.Direction = ParameterDirection.InputOutput;
        parm0.IsNullable = true;
        parm0.SourceColumnNullMapping = false;
        parm0.Value = Split_id;
        myCmd.Parameters.Add(parm0);

        SAParameter parm1 = new SAParameter("@Fi_Sym", SADbType.VarChar);
        parm1.Precision = 0;
        parm1.Scale = 0;
        parm1.Size = 8;
        parm1.Direction = ParameterDirection.Input;
        parm1.IsNullable = false;
        parm1.SourceColumnNullMapping = false;
        parm1.Value = Fi_Sym;
        myCmd.Parameters.Add(parm1);

        SAParameter parm2 = new SAParameter("@split_dt", SADbType.Date);
        parm2.Precision = 0;
        parm2.Scale = 6;
        parm2.Size = 4;
        parm2.Direction = ParameterDirection.Input;
        parm2.IsNullable = false;
        parm2.SourceColumnNullMapping = false;
        parm2.Value = split_dt;
        myCmd.Parameters.Add(parm2);

        SAParameter parm3 = new SAParameter("@From_Qty", SADbType.SmallInt);
        parm3.Precision = 0;
        parm3.Scale = 0;
        parm3.Size = 1;
        parm3.Direction = ParameterDirection.Input;
        parm3.IsNullable = false;
        parm3.SourceColumnNullMapping = false;
        parm3.Value = From_Qty;
        myCmd.Parameters.Add(parm3);

        SAParameter parm4 = new SAParameter("@to_Qty", SADbType.SmallInt);
        parm4.Precision = 0;
        parm4.Scale = 0;
        parm4.Size = 1;
        parm4.Direction = ParameterDirection.Input;
        parm4.IsNullable = false;
        parm4.SourceColumnNullMapping = false;
        parm4.Value = to_Qty;
        myCmd.Parameters.Add(parm4);

        SAParameter parm5 = new SAParameter("@Process_dt", SADbType.Date);
        parm5.Precision = 0;
        parm5.Scale = 6;
        parm5.Size = 4;
        parm5.Direction = ParameterDirection.Input;
        parm5.IsNullable = true;
        parm5.SourceColumnNullMapping = false;
        parm5.Value = Process_dt;
        myCmd.Parameters.Add(parm5);

        SAParameter parm6 = new SAParameter("@Splt_remarks", SADbType.VarChar);
        parm6.Precision = 0;
        parm6.Scale = 0;
        parm6.Size = 255;
        parm6.Direction = ParameterDirection.Input;
        parm6.IsNullable = true;
        parm6.SourceColumnNullMapping = false;
        parm6.Value = Splt_remarks;
        myCmd.Parameters.Add(parm6);

        SAParameter parm7 = new SAParameter("@time_stamp", SADbType.DateTime);
        parm7.Precision = 0;
        parm7.Scale = 6;
        parm7.Size = 8;
        parm7.Direction = ParameterDirection.Output;
        parm7.IsNullable = true;
        parm7.SourceColumnNullMapping = false;
        myCmd.Parameters.Add(parm7);

        SAParameter parm8 = new SAParameter("@updated_by", SADbType.VarChar);
        parm8.Precision = 0;
        parm8.Scale = 0;
        parm8.Size = 16;
        parm8.Direction = ParameterDirection.Output;
        parm8.IsNullable = true;
        parm8.SourceColumnNullMapping = false;
        myCmd.Parameters.Add(parm8);

        SAParameter parm9 = new SAParameter("@Msg", SADbType.VarChar);
        parm9.Precision = 0;
        parm9.Scale = 0;
        parm9.Size = 250;
        parm9.Direction = ParameterDirection.Output;
        parm9.IsNullable = false;
        parm9.SourceColumnNullMapping = false;
        myCmd.Parameters.Add(parm9);

        #endregion "split_ins_Parm setup"

        int rc = 0;
        object orc = myCmd.ExecuteScalar();
        if (orc != null)
        {
            rc = (int)orc;
        }

        Split_id = (long)parm0.Value;
        time_stamp = (DateTime)parm7.Value;
        updated_by = (string)parm8.Value;
        Msg = (string)parm9.Value;
        myConnection.Close();
        myCmd.Dispose();

        if (Split_id != null) rc = 1;
        return rc;
    }

---

[Former Member]

still have to fix display for timestamp column as it's time component shows only hour minute, and A or P not the complete 24 hour format with seconds and ms as what would be shown in isql problem? making the row impossible to update with the criteria of original timestamp and updated_by in addition to the ID.

[Former Member]

finally, I had to tweak the query for the datagridview to replace from time_stamp to dateformat(time_stamp,'yyyy-MM-dd hh:mm:ss.SSS') as time_stamp to retain the millisecond

[VolkerBarth]

Is the format of the datetime display not something that can (and should) be configured in the UI?

What's you setting for the timestamp_format option? (Note: I can't tell whether it has any influence on the ADO.Net mapping - it should not have an influence when the mapped type is a datetime itself...)