0

I keep getting an error when I select a value from my drop down.

**The SelectCommand property has not been initialized before calling 'Fill'.**

It looks like my dataset is returning as empty.

I want to stick to the 3 tier structure.

How do i return a valid dataset using my code?

DAL

public static DataTable GetCustomer(collection b)
{
    {
        DataTable table;
        try
        {
            string returnValue = string.Empty;
            DB = Connect();
            DBCommand = connection.Procedure("getCustomer");
            DB.AddInParameter(DBCommand, "@CustomerRef", DbType.String, b.CustomerRef1);

            DbDataReader reader = DBCommand.ExecuteReader();
            table = new DataTable();
            table.Load(reader);
            return table;
        }
        catch (Exception ex)
        {
            throw (ex);
        }

    }

}

BLL

Looks like I have some redundant code below. I would like to utilize my connection class below:

   public DataSet returnCustomer(collection b)
   {
       try
       {
           SqlDataAdapter adapt = new SqlDataAdapter();
           DataSet table = new DataSet();

           adapt.Fill(table, "table");
           return table;
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }

Connection Class

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.ObjectBuilder;
using System.Data.Common;
using System.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class connection
    {
        const string StrConnection = "CustomerHelperConnectionString";
        internal static Database DB;
        public static DbCommand DBCommand;
        public static Database Connect()
        {

            try
            {
                DB = DatabaseFactory.CreateDatabase(StrConnection);
                return DB;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DbCommand Procedure(string procedure)
        {

            try
            {
                DBCommand = DB.GetStoredProcCommand(procedure);
                return DBCommand;
            }
            catch (Exception ex)
            {
                throw (ex);            
            }
        }
    }
}

PL

protected void ddl_Customers_SelectedIndexChanged(object sender, EventArgs e)
{

    DAL.collection cobj = new collection();
    BLL.business bobj = new business();

    string selectedValue = ddl_Customers.SelectedValue.ToString();

        //populate the text boxes
        txtCustomerRef.Text = bobj.returnCustomer(cobj).Tables[0].Rows[0][0].ToString();
}
PriceCheaperton
  • 5,071
  • 17
  • 52
  • 94
  • In `returnCustomer` method, your dataadapter does not have any command in it. So, it is empty. There is nothing to fill from it. – Soner Gönül Apr 19 '15 at 10:50
  • Added connection class* – PriceCheaperton Apr 19 '15 at 10:53
  • I think, BLL code is missing something, not doing anything, I meant, you need to have some command object to fill that or query DAL , get some value, apply filter based on business logic, then return object and if you need `CustomerRef1` only in `GetCustomer` you can pass `string` directly. – Arindam Nayak Apr 19 '15 at 10:56
  • i need to eventually get customer name, customer address etc etc... – PriceCheaperton Apr 19 '15 at 11:06
  • If you can show me and answer that would be good.. I have struggled with this all day and night :( – PriceCheaperton Apr 19 '15 at 11:13
  • I am confused here, you get customer info by sending customer_ref_id and then you are trying to show customer ref in `txtCustomerRef` ? Is that intended? My assumption, `ddl_Customers` will get you selected `CustomerRef` and then you hit DB to get all details and display in individual `textbox` ? – Arindam Nayak Apr 19 '15 at 11:14
  • Yes your assumption is correct.. Select CustID from drop down and then textboxes get populated. I need to pass custID into SP as that will return results just for that customer. – PriceCheaperton Apr 19 '15 at 11:15

2 Answers2

1

As per my assumption mentioned in comments to OP's question, you need to following.

Change DAL to have this public static DataTable GetCustomer(string customer_ref) and use this DB.AddInParameter(DBCommand, "@CustomerRef", DbType.String, customer_ref);

I see there is no work done in BAL, so I am skipping usage of that.

Instead of using BLL object in bobj use one of DAL instance as this has GetCustomer and has some work to fetch info from DB.

So assuming bobj is an instance of DAL, next, change PL, like this - txtCustomerRef.Text = bobj.GetCustomer(selectedValue).Tables[0].Rows[0][0].ToString();

Arindam Nayak
  • 7,346
  • 4
  • 32
  • 48
1

Change your DAL code like this:

public static DataTable GetCustomer(collection b)
{
    {
        DataTable table = new DataTable();
        try
        {
            string returnValue = string.Empty;
            DB = Connect();
            DBCommand = connection.Procedure("getCustomer");
            DB.AddInParameter(DBCommand, "@CustomerRef", DbType.String, b.CustomerRef1);

            SqlDataAdapter adptr = new SqlDataAdapter(DBCommand);
            adptr.Fill(table);
            return table;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }
}

and now your BAL like this,

public DataSet returnCustomer(collection b)
   {
       try
       {
           DataSet _ds = new DataSet();
           _ds.Tables.Add(DAL.GetCustomer(b));
           return _ds;
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }
Pratik Galoria
  • 351
  • 2
  • 12