Initializing null value

Jes Sie

Once again I need your help. I have a webpage in my web application. On page load, it looks up the credit limit and the accounts receivable of an agent. Below is my code and works fine if the accounts receivable has value:

private void GetCreditLimit()
{
using (SqlConnection con = DBConnection.GetDbCon())
{
SqlCommand cmd = new SqlCommand("spGetAgentCreditLimitAndARBalance", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@AgentID", lblAgentCode.Text);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
try
{
lblCreditLimit.Text = ds.Tables[0].Rows[0]["CreditLimit"].ToString();
lblARBalance.Text = ds.Tables[0].Rows[0]["ARBalance"].ToString();
}
catch (Exception ex)
{
throw ex;
}
}
}
}

But when the accounts receivable has no value, it gives me this error:

Server Error in '/' Application.
There is no row at position 0.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: There is no row at position 0.

When I will deploy this program, all the accounts receivable will be set to null or zero. This means I will be facing a problem when a certain agent first time uses the program. Can someone advice or show me on how to deal with this error?

Thanks for your help.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
Accepted answer
you are getting error becouse you dont have rows in dataset and you are taking value at first row first column so you will be getting error in

ds.Tables[0].Rows[0]["CreditLimit"].ToString();
ds.Tables[0].Rows[0]["ARBalance"].ToString();

you can skip this error using try catch without throwing error as shown below

try
{
ds.Tables[0].Rows[0]["CreditLimit"].ToString();
ds.Tables[0].Rows[0]["ARBalance"].ToString();
}catch{}

or before taking rows position you can check if rows are exist as below

if(ds.Tables[0].Rows.Count>0)
{
ds.Tables[0].Rows[0]["CreditLimit"].ToString();
ds.Tables[0].Rows[0]["ARBalance"].ToString();
}
Jes Sie9y ago
Thanks for your suggestions. This is what I did based on your recommendation #1
declare @CL table
    (
        [AgentID] [nvarchar](50),
        [CreditLimit] [nvarchar](50)
    )
    INSERT into @CL
    SELECT
            a.AgentID,
            a.CreditLimit
    FROM [dbo].[Agents] a
    --WHERE a.AgentID = '0001'

    declare @ar table
    (
        [AgentID]   nvarchar(50),
        [COA_Code]  nvarchar(50),
        [Debit]     [decimal](18, 2),
        [Credit]    [decimal](18, 2)
    )

    insert into @ar
    SELECT
            s.[AgentID],
            s.[COA_Code],
            sum(ISNULL(s.Debit,0)),
            sum(ISNULL(s.Credit,0))
    FROM [dbo].[SalesDetails_eCI] s
    WHERE [COA_Code] = 'Accounts Receivable'
    GROUP BY s.[AgentID], s.[COA_Code]

    SELECT
            c.AgentID,
            c.CreditLimit,
            ISNULL(r.[Debit],0) - ISNULL(r.[Credit],0) [ARBalance]

    FROM @CL c left outer join @ar r on c.AgentID = r.AgentID

    WHERE C.AgentID = @AgentID
Your recom helped a lot!
Rafnas T P9y ago
there is a way you can skip this error by not to throw exception
remove throw ex from your catch block as below
private void GetCreditLimit()
        {
            using (SqlConnection con = DBConnection.GetDbCon())
            {
                SqlCommand cmd = new SqlCommand("spGetAgentCreditLimitAndARBalance", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@AgentID", lblAgentCode.Text);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    try
                    {
                        lblCreditLimit.Text = ds.Tables[0].Rows[0]["CreditLimit"].ToString();
                        lblARBalance.Text = ds.Tables[0].Rows[0]["ARBalance"].ToString();

                    }
                    catch (Exception ex)
                    {
                        //throw ex;
                    }
                }
            }
        }
Amit Gupta9y ago
In your SP, ARBalance column couldnt be found, I think you misspelled. AccountsReceivable as ARBalance.

Do check and try
Jes Sie9y ago
@Amit Gupta, thanks for the reply. I got the same error. I changed already my sp (see below snipet)
ALTER PROCEDURE [dbo].[spGetAgentCreditLimitAndARBalance]
    -- Add the parameters for the stored procedure here
        @AgentID nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT AgentID, COA_Code,
            sum(ISNULL(Debit,0)) as debit,
            sum(ISNULL(Credit,0)) as credit,
            sum(ISNULL(debit,0) - ISNULL(credit,0)) as AccountsReceivable
    FROM dbo.SalesDetails_eCI
    where AgentID=@AgentID and  COA_Code = 'Accounts Receivable'
    group by AgentID, COA_Code
END
Also did as you instructed:
var arBal = ds.Tables[0].Rows[0]["ARBalance"];
lblARBalance.Text = arBal == DBNull.Value ? "0" : Convert.ToString(arBal);
But I got the same error.
Amit Gupta9y ago
Strange! When there is no data, the error would be null exception. Anyway!!

There are 2 options
1. Modify your store procedure, while checking the ARBalance to 0 when there is null value like ISNULL(ARBalance, 0)

2. Before converting to ToString(), check the value is null
var arBal= ds.Tables[0].Rows[0]["ARBalance"];
lblARBalance.Text = arBal==DBNull.Value? "0": Convert.ToString(arBal);