Problem with search database C#

Question

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 26-Jan-22 6:34am

ZaYeD1

Updated 26-Jan-22 9:23am

Sean Ewington

v3

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2022-01-26T08:05:00

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[^]

RobertSF · Answer 2 · 2022-01-26T08:18:00

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[^]

Problem with search database C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0