combined primary key search problem

[snakay]

Hi,

I'm trying to locate a certain row in my datagridview, using textbox _textchanged event. Grid is populated with table having 2 columns as primary keys. With one column I have no problem, but using 2 columns I'm having exception .

private void dfSponsorname_TextChanged(object sender, EventArgs e)
        {
            try
            {
                DataTable tb = ds.Tables["Sponsors"];
                int intRow;

                DataColumn[] dcolPk = new DataColumn[2];

  

                DataColumn column;
                column = new DataColumn();
                column = tb.Columns["SPONSOR_CODE"];
                dcolPk[0] = column;
                column = new DataColumn();
               column = tb.Columns["SUB_CODE"];
                dcolPk[1] = column;
               

              
                tb.PrimaryKey = dcolPk;
                tb.DefaultView.Sort = "SPONSOR_CODE,SUB_CODE";

 // This is where I get exc."  Expecting 2 keys but 1 received 1. "
//To search I have to use sponsor_name column not 
 //key columns      
        if (tb.Rows.Contains(dfSponsorname.Text.Trim()))
                {

                    // At least one row matches primary key
                    rowFound = tb.Rows.Find(dfSponsorname.Text);

I appreciate any help.

snky

[sknake]

Two or more fields used as a PRIMARY KEY are referred to as "composite", but that doesn't help you in solving your problem. You're also creating a new column and then losing the reference. You should be doing this:

private void button1_Click(object sender, EventArgs e)
    {
      DataTable dt = ds.Tables["Sponsors"];

      dt.PrimaryKey = new DataColumn[] { dt.Columns["SPONSOR_CODE"], dt.Columns["SUB_CODE"] };
      dt.DefaultView.Sort = "SPONSOR_CODE,SUB_CODE";
    }

Also what column are you trying to search? Using .Find() is only for primary key columns and to me it looks like you're trying to match a name that isn't included in your primary key. You should probably Select the data out:

private void button2_Click(object sender, EventArgs e)
    {
      const string name = "scott"; //you would pull this off of your text box
      DataTable dt = ds.Tables["Sponsors"];
      DataRow[] rows = dt.Select(string.Format("Name Like '%{0}%'", name.Replace("'", "''")));
      int[] rowNumbers = new int[rows.Length];
      for (int i1 = 0; i1 < rows.Length; i1++)
        rowNumbers[i1] = dt.Rows.IndexOf(rows[i1]);
      Console.WriteLine("Found value in the rows: " + string.Join(", ", Array.ConvertAll<int, string>(rowNumbers, Convert.ToString)));
    }

The full class to test this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmCompositePK : Form
  {
    private DataSet ds;

    public frmCompositePK()
    {
      InitializeComponent();
    }

    private void frmCompositePK_Load(object sender, EventArgs e)
    {
      //Since I don't have your data structure:
      ds = new DataSet();
      ds.EnforceConstraints = false;
      DataTable dtSponsors = new DataTable();
      dtSponsors.Columns.Add(new DataColumn("SPONSOR_CODE", typeof(string)));
      dtSponsors.Columns.Add(new DataColumn("SUB_CODE", typeof(string)));
      dtSponsors.Columns.Add(new DataColumn("Name", typeof(string)));
      dtSponsors.TableName = "Sponsors";
      ds.Tables.Add(dtSponsors);
      {
        DataRow row = dtSponsors.NewRow();
        row["SPONSOR_CODE"] = Guid.NewGuid().ToString();
        row["SUB_CODE"] = Guid.NewGuid().ToString();
        row["Name"] = "Scott Allan";
        dtSponsors.Rows.Add(row);
      }
      {
        DataRow row = dtSponsors.NewRow();
        row["SPONSOR_CODE"] = Guid.NewGuid().ToString();
        row["SUB_CODE"] = Guid.NewGuid().ToString();
        row["Name"] = "Scott Knake";
        dtSponsors.Rows.Add(row);
      }
      {
        DataRow row = dtSponsors.NewRow();
        row["SPONSOR_CODE"] = Guid.NewGuid().ToString();
        row["SUB_CODE"] = Guid.NewGuid().ToString();
        row["Name"] = "Snakay";
        dtSponsors.Rows.Add(row);
      }
      {
        DataRow row = dtSponsors.NewRow();
        row["SPONSOR_CODE"] = Guid.NewGuid().ToString();
        row["SUB_CODE"] = Guid.NewGuid().ToString();
        row["Name"] = "Knake, Scott";
        dtSponsors.Rows.Add(row);
      }
    }

    private void button1_Click(object sender, EventArgs e)
    {
      DataTable dt = ds.Tables["Sponsors"];
      dt.PrimaryKey = new DataColumn[] { dt.Columns["SPONSOR_CODE"], dt.Columns["SUB_CODE"] };
      dt.DefaultView.Sort = "SPONSOR_CODE,SUB_CODE";
    }

    private void button2_Click(object sender, EventArgs e)
    {
      const string name = "scott"; //you would pull this off of your text box
      DataTable dt = ds.Tables["Sponsors"];
      DataRow[] rows = dt.Select(string.Format("Name Like '%{0}%'", name.Replace("'", "''")));
      int[] rowNumbers = new int[rows.Length];
      for (int i1 = 0; i1 < rows.Length; i1++)
        rowNumbers[i1] = dt.Rows.IndexOf(rows[i1]);
      Console.WriteLine("Found value in the rows: " + string.Join(", ", Array.ConvertAll<int, string>(rowNumbers, Convert.ToString)));
    }


  }
}

[snakay]

Hi sknake,
2 things to ask

1-Can you explain to me what it does here with name.Replace(' '' ',' "" ')))

DataRow[] rows = dt.Select(string.Format("Name Like '%{0}%'", name.Replace("'", "''")));

2-How do I select the row on the grid that I look for which includes the textbox.text in the column "Name"

Thanks
snky

[sknake]

>> 1-Can you explain to me what it does here with name.Replace(' '' ',' "" ')))

Just double-quoting user input to escape it properly.

2-How do I select the row on the grid that I look for which includes the textbox.text in the column "Name"

By using the line you asked a question about in #1.

Try running the code....

[snakay]

I could do it with the previous approach, somehow couldn't manage to select row , so I did it as follows;

private void dfSponsorname_TextChanged(object sender, EventArgs e)
        {
           
            try
            {
                DataTable tb = ds.Tables["Sponsor"];
                tblSponsor.DataSource = ds.Tables["Sponsor"];
                tblSponsor.ClearSelection();
              
                foreach (DataGridViewRow row in tblSponsor.Rows)
                {

                    if (row.Cells["SPONSOR_NAME"].Value.ToString().StartsWith(this.dfSponsorname.Text))
                    {
                        // selecting the row
                        row.Selected = true;
                        // displaying the row
                        tblSponsor.FirstDisplayedScrollingRowIndex = row.Index;//bring it to the top
                       
                        break;

                        // focusing the row 
                      //  tblSponsor.CurrentCell = row.Cells[2];
                    
                    }
              
                }              
                
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.ToString());
            }
        }