none
How to search for records without using primary key RRS feed

  • Question

  • I would like to know how to search for a certain record by a value in a non keyed column, then being able to update that record to the values in another table. Here is what I have used in the past to locate a record by a primary key, is there a way to do the same with out a keyed column as a search value.

     

    DataSet1.HorseJoinTableDataTable dth = new DataSet1.HorseJoinTableDataTable();

    DataSet1TableAdapters.HorseJoinTableTableAdapter tah = new    FBWinMobileSync.DataSet1TableAdapters.HorseJoinTableTableAdapter();

    dataSet1.EnforceConstraints = false;

    tah.FillByMod(dth);

    fB7MobileDBDataSet.EnforceConstraints = false;

    foreach (DataSet1.HorseJoinTableRow dr in dth.Rows)

    try

    { How can I accomplish this below on a non keyed column search

    FB7MobileDBDataSet.HorseRow newMobileHorse = this.fB7MobileDBDataSet.Horse.FindByCODEHorseName(dr.CODE, dr.HORSENAME);

    newMobileHorse.CODE = dr.CODE;

    newMobileHorse.Stable = dr.STABLE;

    newMobileHorse.HorseName = dr.HORSENAME;

    newMobileHorse.Active = dr.ACTIVE;

    newMobileHorse.AngleLF = dr.ANGLELF;

    newMobileHorse.AngleLH = dr.ANGLELH;

    newMobileHorse.AngleRF = dr.ANGLERF;

    newMobileHorse.AngleRH = dr.ANGLERH;

    dataSet1.EnforceConstraints = false;

    //if(dr.DUEDATE.ToString().Trim()=="")

    if (dr.IsNull("DUEDATE"))

    dr.DUEDATE = DateTime.Today;

    newMobileHorse.DueDate = dr.DUEDATE;

    if (dr.IsNull("LASTCOST"))

    dr.LASTCOST = "0";

    double D = Convert.ToDouble(dr.LASTCOST);

    newMobileHorse.LastCost = D; //Convert.ToDouble(dr.LASTCOST);

    newMobileHorse.Totals = 3;

    //Equals no change to record

    if (dr.IsNull("LASTDATE"))

    dr.LASTDATE = DateTime.Today;

    newMobileHorse.LastDate = dr.LASTDATE;

    newMobileHorse.LengthLF = dr.LENGTHLF;

    newMobileHorse.LengthLH = dr.LENGTHLH;

    newMobileHorse.LengthRF = dr.LENGTHRF;

    newMobileHorse.LengthRH = dr.LENGTHRH;

    newMobileHorse.MainWork = dr.MAINWORK;

    newMobileHorse.NickName = dr.NICKNAME;

    newMobileHorse.SizeLF = dr.SIZELF;

    newMobileHorse.SizeLH = dr.SIZELH;

    newMobileHorse.SizeRF = dr.SIZERF;

    newMobileHorse.SizeRH = dr.SIZERH;

    newMobileHorse.Weeks = Convert.ToInt16(dr.WEEKS);

    newMobileHorse.Delete = dr.DELETE;

    newMobileHorse.LengthList = dr.LENGTHLIST;

    newMobileHorse.NeedsNewFr = dr.NEEDS_NEW_FR;

    newMobileHorse.NeedsNewHd = dr.NEEDS_NEW_HD;

    newMobileHorse.StartAngleLF = dr.START_ANGLE_LF;

    newMobileHorse.StartAngleLH = dr.START_ANGLE_LH;

    newMobileHorse.StartAngleRF = dr.START_ANGLE_RF;

    newMobileHorse.StartAngleRH = dr.START_ANGLE_RH;

    newMobileHorse.StartLengthLF = dr.START_LENGHT_LF;

    newMobileHorse.StartLengthLH = dr.START_LENGTH_LH;

    newMobileHorse.StartLengthRF = dr.START_LENGHT_RF;

    newMobileHorse.StartLengthRH = dr.START_LENGTH_RH;

    newMobileHorse.Front = dr.FRONT;

    newMobileHorse.Hind = dr.HIND;

    Thanks Jon Stroh
    Saturday, June 16, 2007 10:05 PM

Answers

  • No worries Jon glad to help,

     

    If you have a search for DataTable.Select() method on MSDN you will find additional information on the method. Smile

     

    Monday, June 18, 2007 1:16 PM

All replies

  • Hi Jon,

     

    If you search without a primary key then your result can end up with more than one record. You cannot guarantee that you'll select one record. The only way to do it is to include all columns of the table and check if each column matches a value. You can do this with the select() method of a DataTable.

     

    Here a fairly lame example based on a table with two columns [Name] and [Postcode]....

     

    Code Snippet
     Dim foundRows() As DataRow
     foundRows = table.Select("[Name] ='Fred' AND [Postcode] = 'PA0YUI'")

     

    You will need to include all the column in the select statement to be more accurate with the search. The select statement returns an array of DataRows that match the select expression so you can determine how many rows where returned with the Length property of the array.

     

    That I feel is your best approach.

     

     

    Sunday, June 17, 2007 3:24 PM
  • Also, you're writing an awful lot more code there than you need to.  To copy data between tables with the same schema you can just use:

    Code Snippet

    for (int i=0; i<oldDataTable.Columns.Count; i++)
    {
       newRow[i] = oldRow[i];
    }



    And even in cases where you're only copying columns conditionally, you're better off putting the names of the columns that in some simple data structure like a string[] or a List<string>, e.g.

    Code Snippet

    string[] alwaysCopy = { "CODE", "STABLE" ... };
    string[] copyIfNew = { "LASTDATE", "LENGTHHLF" ... };

    foreach (string col in alwaysCopy)
    {
       newRow[col] = oldRow[col];
    }
    if (oldRow.IsNull("LASTDATE"))
    {
       foreach (string col in copyIfNew)
       {
          newRow[col] = oldRow[col];
       }
    }




    ...which accomplishes in 7 lines of code what you're doing in about 30, and eliminates the risk that you'll type something like

       newRow.START_ANGLE_LF = oldRow.START_ANGLE_HF

    and not notice it.
    Monday, June 18, 2007 1:37 AM
  • Thank you Robert and Derek very much, I am still learning C# and welcome every bit of help I can get. I will try to implement your suggestion Robert, it makes very good sense. I wish i could learn how to search the vs2005 help file better because i still can't find any help on table.Select. Oh well probably found it and didn't understand it as well as what Derek typed. Smile

     

    Thanks again!!

    Monday, June 18, 2007 12:43 PM
  • No worries Jon glad to help,

     

    If you have a search for DataTable.Select() method on MSDN you will find additional information on the method. Smile

     

    Monday, June 18, 2007 1:16 PM