ExecuteScalar not throwing exception

Question

I am trying to inser data in main and detail tables.

My Main Table Qry: Insert INTO tblMain();

Then I am doing:

declare @OrderID as int; 
set @OrderID=scope_identity();
Update Orders SET OrderID=(LTRIM(RTRIM(OrderID))+ RIGHT('0000000'+CAST(@OrderID AS VARCHAR(7)),7)) WHERE OrderId=@OrderID;

SELECT @OrderID

Then I am inserting in Detail table using value of @OrderID as Insert into tbl_Details(OrderID,SKU,amount) Values(@OrderID,NULL,20 );

If in tbl_Details, SKU is not null, I am not getting exception if i run all query in ExecuteScalar at once and passing SKU as null.

My Execute funcion is:

public static double ExecuteScalarQuery(string SQL, ref string ErrMsg)
{
    ErrMsg = "";
    using (SqlConnection conn = new SqlConnection(GetDBConn()))
    {
        SqlTransaction otrans = null;
        double dblRetVa = 0;
        try
        {
            conn.Open();
            otrans = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand(SQL, conn);

            cmd.Transaction = otrans;
            dblRetVa = double.Parse(cmd.ExecuteScalar().ToString());
            otrans.Commit();
            conn.Close();
        }
        catch (Exception ex)
        {
            ErrMsg = ex.ToString().Trim();
            if (otrans != null) otrans.Rollback();
        }
        return dblRetVa;
    }
}

I have the solution but for that i will have to change the signature of my function as below:

public static double ExecuteNonQueryWithSelect(string SQL, string sQry2,string SelectColID , ref string ErrMsg)
{
    ErrMsg = "";
    using (SqlConnection conn = new SqlConnection(GetDBConn()))
    {
        SqlTransaction otrans = null;
        double dblRetVal = 0;
        try
        {
            conn.Open();
            otrans = conn.BeginTransaction();

            SqlCommand cmd = new SqlCommand(SQL, conn);

            cmd.Transaction = otrans;
            dblRetVal = double.Parse(cmd.ExecuteScalar().ToString());

            SqlCommand cmd2 = new SqlCommand(sQry2.Replace(SelectColID.Trim(), dblRetVal.ToString()), conn);

            cmd2.Transaction = otrans;
            cmd2.ExecuteNonQuery();

            otrans.Commit();
            conn.Close();
        }
        catch (Exception ex)
        {
            ErrMsg = ex.ToString().Trim();
            if (otrans != null) { otrans.Rollback(); dblRetVal = 0; }
        }
        return dblRetVal;
    }
}

what i want is whether i can do ant thing in my old function so that it throw exception if some not nullable field in detail table is passed as null without changing signature of my function.

score 0 · Answer 1 · answered Oct 18 '16 at 13:50

0

did you try using executenonquery()? Try using DBNull instead of null in your validation because you are comparing with numbers not strings.

answered Oct 18 '16 at 13:50

GNMercado

433
5
9

I cant use executenonquery since i needed orderid in return and also i am concatinating main and detail query in one string and passing to the function. The same function is working is if there is any incorrect column in detail but not working for not nullable fields when passing as null. – ManojK Oct 19 '16 at 05:03
I dont want to change the signature of ExecuteScalarQuery function. – ManojK Oct 19 '16 at 05:53
Try changing `if (otrans != null) otrans.Rollback();` to `if (otrans != DbNull.Value) otrans.Rollback();` – GNMercado Oct 19 '16 at 05:55
tried if ((otrans != null) && (!otrans.Equals(DBNull.Value))) otrans.Rollback(); – ManojK Oct 19 '16 at 06:13
try removing `((otrans != null) &&` – GNMercado Oct 19 '16 at 06:15

ExecuteScalar not throwing exception

1 Answers1