Custom Select update and edit commands

See more:

MS-Access

database

Hi, my frustration levels are so high at the moment I could Yell, so please ignore any errors.

We have a legacy Access system that has forms and database initialization. The Access system is live and we are trying to port it across form by web form to C#. net.

We have not been able to migrate to SQL due to the large number of errors that occur during the MS migration.

The one great thing about Access is how easy it is to build forms, doing things in C# I have found illogical and frustrating.

At the moment this is what I am stuck on. We have a table that is very badly normallized. It is a table of orders to be delivered. If a client has a more than one order for a day the orderdate, preparation (or roast) date, required date and other fields are duplicated.

So I am keen to create a master detail form. I have created an object with header and details:

<blockquote class="FQ"><div class="FQA">Quote:</div>namespace TrackerDotNet.classes
{
  /// <summary>
  /// based on the order Query the classes stores the data
  /// SQL: SELECT OrdersTbl.CustomerID, CustomersTbl.CompanyName,  OrdersTbl.OrderDate, " +
  //           " OrdersTbl.RoastDate, OrdersTbl.RequiredByDate, OrdersTbl.Confirmed, OrdersTbl.Notes, " + 
  //           "PersonsTbl.Abreviation
  /// </summary>
  public class OrderHeaderDetails
  {

    //  CustomersTbl.CompanyName, OrdersTbl.CustomerId, OrdersTbl.OrderDate, OrdersTbl.RoastDate, 
    //  OrdersTbl.RequiredByDate, PersonsTbl.Abreviation, OrdersTbl.Confirmed, OrdersTbl.Done, OrdersTblNotes
    public OrderHeaderDetails()
    {
      _otCustomerID = 0;
      _ctCompanyName = _ptAbreviation = _otNotes = "";
      _otOrderDate = _otRoastDate = _otRequiredByDate  = DateTime.Now;
      _otConfirmed = true;
      _otDone = false;
    }

    private long _otCustomerID;
    private string _ctCompanyName;
    private DateTime _otOrderDate;
    private DateTime _otRoastDate;
    private DateTime _otRequiredByDate;
    private bool _otConfirmed;
    private bool _otDone;
    private string _otNotes;
    private string _ptAbreviation;

    public long CustomerID
    {
       get { return _otCustomerID; }
       set { _otCustomerID = value; }
    }
    public string CompanyName
    {
       get { return _ctCompanyName; }
       set { _ctCompanyName = value; }
    }
    public DateTime OrderDate
    {
       get { return _otOrderDate; }
       set { _otOrderDate = value; }
    }
    public DateTime RoastDate
    {
       get { return _otRoastDate; }
       set { _otRoastDate = value; }
    }
    public DateTime RequiredByDate
    {
       get { return _otRequiredByDate; }
       set { _otRequiredByDate = value; }
    }
    public bool Confirmed
    {
      get { return _otConfirmed; }
      set { _otConfirmed = value; }
    }
    public bool Done
    {
      get { return _otDone; }
      set { _otDone = value; }
    }
    public string Notes
    {
       get { return _otNotes; }
       set { _otNotes = value; }
    }
    public string Abreviation
    {
       get { return _ptAbreviation; }
       set { _ptAbreviation = value; }
    }
  }

  public class OrderLinesDetails
  {
    // ItemTypeID, QuantityOrdered, PrepTypeID 
    public OrderLinesDetails()
    {
      _otItemTypeID = _otPrepTypeID = 0;
      _otQuantityOrdered = 0.00;
    }

    private long _otItemTypeID, _otPrepTypeID;
    private double _otQuantityOrdered;

    public long otItemTypeID { get { return _otItemTypeID; } set { _otItemTypeID = value; } }
    public long otPrepTypeID { get { return _otPrepTypeID; } set { _otPrepTypeID = value; } }
    public double otQuantityOrdered { get { return _otQuantityOrdered; } set { _otQuantityOrdered = value; } }
  }
  
}
</blockquote>

I have then also created an object which I was hoping to link as an object datasource to the detailview (header / master) and gridview (lines / details)

<blockquote class="FQ"><div class="FQA">Quote:</div>namespace TrackerDotNet.App_Code
{
  public class OrdersData
  {

    const string CONST_CONSTRING = "Tracker08ConnectionString";
    const string CONST_ORDERSHEADER_SELECT = "SELECT CustomersTbl.CompanyName, OrdersTbl.CustomerId As CustomerId, OrdersTbl.OrderDate, OrdersTbl.RoastDate, " +
                                             " OrdersTbl.RequiredByDate, PersonsTbl.Abreviation, OrdersTbl.Confirmed, OrdersTbl.Done, OrdersTbl.Notes " +
                                             " FROM ((OrdersTbl LEFT OUTER JOIN PersonsTbl ON OrdersTbl.ToBeDeliveredBy = PersonsTbl.PersonID)" +
                                             " LEFT OUTER JOIN CustomersTbl ON OrdersTbl.CustomerId = CustomersTbl.CustomerID)" +
                                             " WHERE ([CustomerId] = ?) AND ([RoastDate] = ?)";
    const string CONST_ORDERSLINES_SELECT = "SELECT ItemTypeID, QuantityOrdered, PrepTypeID FROM OrdersTbl WHERE ([CustomerId] = ?) AND ([RoastDate] = ?)";

    // connection Strinbg
    private string _connectionString;

    public OrdersData()
    {
      Initialize();
    }
    public void Initialize()
    {
      // Initialize data source. Use "Tracker08" connection string from configuration.

      if (ConfigurationManager.ConnectionStrings[CONST_CONSTRING] == null ||
          ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString.Trim() == "")
      {
        throw new Exception("A connection string named " + CONST_CONSTRING + " with a valid connection string " +
                            "must exist in the <connectionStrings> configuration section for the application.");
      }
      _connectionString =
        ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
    }

    public List<OrderHeaderDetails> LoadOrderHeader(Int32 pCustomerId, DateTime pPrepDate)
    {
      List<OrderHeaderDetails> ohDetails = new List<OrderHeaderDetails>;

      string _sqlCmd = CONST_ORDERSHEADER_SELECT;
      OleDbConnection _conn = new OleDbConnection(_connectionString);

      OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
      _cmd.Parameters.Add(new OleDbParameter { Value = pCustomerDone } );
      _cmd.Parameters.Add(new OleDbParameter { Value = pPrepDate } );

      _conn.Open();
      objReader = objCmd.ExecuteReader();
      
      while (objReader.Read()) 
      {
        OrderHeaderDetails ohDetail = new OrderHeaderDetails();
 
        ohDetail.CustomerID = (Int32)objReader["CustomerID"];
        ohDetail.CompanyName = (string)objReader["CompanyName"];
        ohDetail.Abreviation = (string)objReader["Abreviation "];
        ohDetail.Notes = (string)objReader["Notes"];
        ohDetail.OrderDate = (string)objReader["OrderDate"];
        ohDetail.RoastDate = (string)objReader["RoastDate"];
        ohDetail.RequiredByDate = (string)objReader["RequiredByDate"];
        ohDetail.Confirmed = (bool)objReader["Confirmed"];
        ohDetail.Done = (bool)objReader["Done"];
        
        ohDetails.Add(ohDetail);
      }
      objReader.Close();  
      objConn.Close();

      return ohDetails;
    }


  }
}</blockquote>

I had previously used

da.Fill(ds, "Orders");

but this seems to only return the data from the OrdersTbl not the customers table.

All I want to do is be able to create my own SELECT, UPDATE, INSERT and DELETE or CRED commands, and attach them to the grid / detail view. Is this really such a mission!


I have used many articles on CodeProject and ASP.NET to get this far and now I too frustrated. Is there not an easier way to do this?

I had tried to attach the class to the Object data source as below, but this is also problematic, since placing the class in the app_code folder seems to make to compiling not work and the auto fillful programming too.

<asp:ObjectDataSource ID="odsOrderHeader" runat="server" TypeName="TrackerDotNet.App_Code.OrdersData" 
    EnablePaging="true" SelectMethod="LoadOrderHeader" >
        <SelectParameters>
          <asp:QueryStringParameter DefaultValue="1" Name="CustomerId" QueryStringField="CustomerID" Type="Int32" />
          <asp:QueryStringParameter Name="RoastDate" QueryStringField="PrepDate" Type="DateTime" />
        </SelectParameters>

      </asp:ObjectDataSource>