I am using the below code to search for the inputted textBox ID in an accessdb and returning the row of data to a dataGridView. When I search for a second ID, the first row in the GridView is replaced, How can I make it save multiple rows?

 private void searchButton_Click(object sender, EventArgs e)
    {
        conn1.Open();

        
        OleDbCommand cmd1 = new OleDbCommand("Select ID, IMEI, TekNum, BatchNum, ICCID, IMSI from TBLTest1 where ID=@param1", conn1);
        cmd1.Parameters.AddWithValue("@param1", txtScannedValue.Text);
        OleDbDataReader reader1;
        reader1 = cmd1.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader1);

        dataGridView1.DataSource = dt;
        
        txtScannedValue.Text = "";

        conn1.Close();
    }

CodePudding user response：

What you are doing is - each time you press the searchButton_Click button you are running a query that returns 1 row only(ID=@param1) and then you are inserting ALL(which is always 1 in your case due to query) your searched rows to your dataGridView via .DataSource() bind. To solve this I would recommend you to re-implement your data bindings:

I am assuming that DataTable is from ADO.NET so without changing a query you could bind your data something like this:

// this is pseudocode
private void searchButton_Click(object sender, EventArgs e) {

  var row = (DataGridViewRow) dataGridView1.Rows[0].Clone();

  var retrievedRow = getRowById(txtScannedValue.Text);
  if (retrievedRow is null) return;

  row.Cells[0].Value = retrievedRow.value1; // bind here you model fields
  row.Cells[1].Value = retrievedRow.value2;
  // ...

  dataGridView1.Rows.Add(row);
}

private void getRowById(string id) {
  conn1.Open();

  OleDbCommand cmd1 = new OleDbCommand("Select ID, IMEI, TekNum, BatchNum, ICCID, IMSI from TBLTest1 where ID=@param1", conn1);
  cmd1.Parameters.AddWithValue("@param1", id);
  OleDbDataReader reader1;
  reader1 = cmd1.ExecuteReader();
  DataTable dt = new DataTable();
  dt.Load(reader1);

  DataRow row;
  if (dt.Rows.Count > 0) {
    row = dt.Rows[0];
  }

  conn1.Close();
  return row;
}

CodePudding user response：

I'd use a ListBox or ComboBox to present data they can recognize with the id available. To append rows, use DataTable.ImportRow.

Here a ComboBox is used to present, in this case employees and the DataGridView is initially populated with an empty DataTable.

Click a button to get a row to append to the underlying DataTable for the DataGridView.

Model and data operations

using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace AccessApplication.Classes
{
    public class EmployeesOperations
    {
        public static string ConnectionString =>
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb";

        public static List<Employee> EmployeesList()
        {
            List<Employee> list = new List<Employee>();
            using var cn = new OleDbConnection { ConnectionString = ConnectionString };
            using var cmd = new OleDbCommand() { Connection = cn };

            cmd.CommandText = "SELECT EmployeeID, FirstName, LastName  FROM Employees";

            cn.Open();
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new Employee()
                {
                    Id = reader.GetInt32(0), 
                    FirstName = reader.GetString(1), 
                    LastName = reader.GetString(2)
                });
            }

            return list;
        }

        public static DataTable EmptyDataTable()
        {
            using var cn = new OleDbConnection { ConnectionString = ConnectionString };
            using var cmd = new OleDbCommand() { Connection = cn };

            cmd.CommandText = 
                "SELECT TOP 1 EmployeeID, FirstName, LastName FROM Employees";

            cn.Open();

            DataTable table = new DataTable();
            table.Load(cmd.ExecuteReader());
            table.Rows.Clear();

            return table;
        }
        public static DataTable SingleRow(int identifier)
        {
            using var cn = new OleDbConnection { ConnectionString = ConnectionString };
            using var cmd = new OleDbCommand() { Connection = cn };

            cmd.CommandText = 
                "SELECT TOP 1 EmployeeID, FirstName, LastName "  
                "FROM Employees WHERE EmployeeID = @Id";

            cmd.Parameters.Add("@Id", OleDbType.Integer).Value = identifier;
            cn.Open();

            DataTable table = new DataTable();
            table.Load(cmd.ExecuteReader());

            return table;
        }
    }

    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public override string ToString() => $"{FirstName} {LastName}";
    }
}

Form code

public partial class EmployeeForm : Form
{
    private readonly BindingSource _employeesBindingSource = 
        new BindingSource();

    public EmployeeForm()
    {
        InitializeComponent();

        _employeesBindingSource.DataSource = EmployeesOperations.EmployeesList();
        EmployeesComboBox.DataSource = _employeesBindingSource;

        dataGridView1.DataSource = EmployeesOperations.EmptyDataTable();
    }

    private void GetSingleEmployeeButton_Click(object sender, EventArgs e)
    {
        int id = ((Employee)EmployeesComboBox.SelectedItem).Id;
        DataTable table = ((DataTable)dataGridView1.DataSource);
        
        DataRow result = table.AsEnumerable()
            .FirstOrDefault(row => row.Field<int>("EmployeeID") == id);

        // only add if not already in the data grid view
        if (result == null)
        {
            table.ImportRow(EmployeesOperations.SingleRow(id).Rows[0]);
        }
    }
}