Click here to Skip to main content
15,302,644 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a datagridview to show data from access. So my problem is in search. How do I show my data to datagridview with name of columns?
C#
OleDbDataAdapter da = new OleDbDataAdapter("Select * from Invoice where Company_Name Like '%" + txtCompanyName.Text + "%'", connection);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGridView1.DataSource = dt;

This my code for search. I do not want use DataTable dt = new DataTable. I want use my datagridview to show my data in the same properties datagridview

I use this code for save data, but have no idea how to use use it for search.
C#
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
    string query = "insert into Invoice (Invoice_No,Company_Name,Item,Invoice_Date,Warranty_Perioed,Expir_Warranty,Note_) values(@Invoice_No,@Company_Name,@Item,@Invoice_Date,@Warranty_Perioed,@Expir_Warranty,@Note_)";

    OleDbCommand cmd2 = new OleDbCommand(query, connection);
    if (dr.IsNewRow) continue;
    {
        cmd2.Parameters.AddWithValue("@Invoice_No", dr.Cells["Invoice_No"].Value ?? DBNull.Value);
        cmd2.Parameters.AddWithValue("@Company_Name", dr.Cells["Company_Name"].Value ?? DBNull.Value);
        cmd2.Parameters.AddWithValue("@Item", dr.Cells["Item"].Value ?? DBNull.Value);
        cmd2.Parameters.AddWithValue("@Invoice_Date", dr.Cells["Invoice_Date"].Value ?? DBNull.Value);
        cmd2.Parameters.AddWithValue("@Warranty_Perioed", dr.Cells["Warranty_Perioed"].Value ?? DBNull.Value);
        cmd2.Parameters.AddWithValue("@Expir_Warranty", dr.Cells["Expir_Warranty"].Value ?? DBNull.Value);
        cmd2.Parameters.AddWithValue("@Note_", dr.Cells["Note_"].Value ?? DBNull.Value);
    }
    connection.Open();
    cmd2.ExecuteNonQuery();
    connection.Close();
}


What I have tried:

I want another way to use search without using

C#
DataTable dt = new DataTable();
Posted
Updated 26-Jan-22 9:23am
v3
Comments
PIEBALDconsult 26-Jan-22 12:44pm
   
You load a DataTable with data, and then use its DefauiltView to populate the grid.
And please use a parameterized SQL statement.
ZaYeD1 26-Jan-22 13:28pm
   
i use parameter for save sql but
no have idea for use parameter for search

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that through your whole app, and then worry about searching - but generally a better way to do what you want is to user the RowFilter property of the associated DataView instead of re-querying the DB: DataView.RowFilter Property (System.Data) | Microsoft Docs[^]
   
Hi. To search, you could do something similar to what you are doing now, except the string query would be "select" and not "insert." But I think I see the challenge -- the parameters could be in different combination.

There's an article here about making a Table Adapter that uses a dynamic WHERE clause. It may be what you need. Extending TableAdapters for Dynamic SQL[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900