communication error from aSaCommand.ExecuteScalar()

0

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?

asked 03 Mar '14, 02:45

G_G's gravatar image

G_G
46●3●3●9
accept rate: 0%

edited 03 Mar '14, 02:48

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?

(03 Mar '14, 03:59) Volker Barth

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.

(03 Mar '14, 11:51) 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.

(03 Mar '14, 14:08) Chris Keating
One Answer:
active answersoldest answersnewest answerspopular answers
0

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;
    }
permanent link

answered 03 Mar '14, 16:07

G_G's gravatar image

G_G
46●3●3●9
accept rate: 0%

edited 04 Mar '14, 03:56

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.

(04 Mar '14, 04:02) G_G

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

(04 Mar '14, 06:17) G_G
Replies hidden

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...)

(04 Mar '14, 07:03) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×124
×93
×14
×9

question asked: 03 Mar '14, 02:45

question was seen: 2,895 times

last updated: 04 Mar '14, 07:03

Related questions

error within Stored Proc, but sqlcode = 0

what is wrong is this storeproc inout parm

Breaking a sybase stored procedure

DBISQL 12.0.1.3324 does not display the single result set of a stored procedure by default

Run a Stored Procedure from a Command Button in Infomaker

Merge and Insert from stored procedure

SQL Error Cursor not open

Protecting intellectual property

Entity Framework, Connection Pooling and connection-scoped variables

How to left join to a stored procedure