CODEWITHSUNDEEP
ado.net
Sam Alex
Sam Alex

Reputation: 442

ADO.NET: ExecuteScalar() Connection problum

Here is my C# code

protected bool SaveItems(ObservableCollection<Model> Items, Int32 UserId)
    {
        if (sqlcon.State == ConnectionState.Closed)
            sqlcon.Open();
        foreach (Model Rate in Items)
        {
            SqlCommand sccmd = new SqlCommand("ItemsAdd", sqlcon);
            sccmd.CommandType = CommandType.StoredProcedure;
            sccmd.Parameters.Add("@ItemsId", SqlDbType.Int).Value = Rate.Id;
            sccmd.Parameters.Add("@RMId", SqlDbType.Int).Value = Rate.HeadId;
            sccmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = Rate.Date;
            sccmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = Rate.Price;
            sccmd.Parameters.Add("@UserId", SqlDbType.Int).Value = UserId;
            if (Rate.Id == 0)
            {
                Rate.Id = Convert.ToInt32(sccmd.ExecuteScalar());
            }
            else
                sccmd.ExecuteScalar();
        }
        sqlcon.Close();
        return true;
    }

Repeated clicking on add button, this is giving me this error

Additional information: ExecuteScalar requires an open and available Connection. The connection's current state is closed.

What is causing this error and how can I fix it?

Upvotes: 2

Views: 48

Answers (1)

FM Plus
FM Plus

Reputation: 568

Try Like this:

protected bool SaveItems(ObservableCollection<Model> Items, Int32 UserId)
{
    if (sqlcon.State == ConnectionState.Closed)
{
        sqlcon.Open();
    foreach (Model Rate in Items)
    {
        SqlCommand sccmd = new SqlCommand("ItemsAdd", sqlcon);
        sccmd.CommandType = CommandType.StoredProcedure;
        sccmd.Parameters.Add("@ItemsId", SqlDbType.Int).Value = Rate.Id;
        sccmd.Parameters.Add("@RMId", SqlDbType.Int).Value = Rate.HeadId;
        sccmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = Rate.Date;
        sccmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = Rate.Price;
        sccmd.Parameters.Add("@UserId", SqlDbType.Int).Value = UserId;
        if (Rate.Id == 0)
        {
            Rate.Id = Convert.ToInt32(sccmd.ExecuteScalar());
        }
        else
            sccmd.ExecuteScalar();
    }
    sqlcon.Close();
    }
    return true;
}

Upvotes: 2

Related Questions