How to edit data in search result in C#

Question

0.00/5 (No votes)

See more:

, +

I managed to create a code for my edit button and search button. My edit button is functional. It can edit my data in database, my search code is also functional. My problem is to edit data in search results. Like if i search "naruto", my datagridview will filter all the names that has "naruto" on the database. After i choose and doubleclick on the result, all data will reappear to my textbox. But when i edit that data, it doesn't work. Instead, the first data on my database is the one that is changing not the one that i choose. My edit code is working if i don't search any names and don't choose on the search result. I think the problem is in my Edit codes. Please share your answers. Much better if you have a code examples. THANKS IN ADVANCE!

here's my codes. Please HELP...

What I have tried:

C#

private void JOGridView_DoubleClick(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("Select * from JobOrder", con);

        if (JOGridView.CurrentCell != null && JOGridView.CurrentCell.Value != null)
        {
            TBName.Text = JOGridView.SelectedRows[0].Cells[0].Value.ToString();
            TBContact.Text = JOGridView.SelectedRows[0].Cells[1].Value.ToString();
            CBStatus.Text = JOGridView.SelectedRows[0].Cells[2].Value.ToString();
            TBModel.Text = JOGridView.SelectedRows[0].Cells[3].Value.ToString();
            TBSerial.Text = JOGridView.SelectedRows[0].Cells[4].Value.ToString();
            TBAccess.Text = JOGridView.SelectedRows[0].Cells[5].Value.ToString();
            TBRB.Text = JOGridView.SelectedRows[0].Cells[6].Value.ToString();
            TBRP.Text = JOGridView.SelectedRows[0].Cells[8].Value.ToString();
            TBIT.Text = JOGridView.SelectedRows[0].Cells[10].Value.ToString();
            CBRamarks.Text = JOGridView.SelectedRows[0].Cells[11].Value.ToString();
            TBCharge.Text = JOGridView.SelectedRows[0].Cells[12].Value.ToString();
            TBRELB.Text = JOGridView.SelectedRows[0].Cells[13].Value.ToString();
        }
    }

 private void BTNEdit_Click(object sender, EventArgs e)
    {
        {
            DialogResult dr;
            dr = MessageBox.Show("Are you sure you want to Edit this record?", "Update Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (dr == DialogResult.Yes)
            {

                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM JobOrder", con);
                da.Fill(dt);

                dt.Rows[JOGridView.CurrentRow.Index].BeginEdit();


                dt.Rows[JOGridView.CurrentRow.Index][1] = TBName.Text;
                dt.Rows[JOGridView.CurrentRow.Index][2] = TBContact.Text;
                dt.Rows[JOGridView.CurrentRow.Index][3] = CBStatus.Text;
                dt.Rows[JOGridView.CurrentRow.Index][4] = TBModel.Text;
                dt.Rows[JOGridView.CurrentRow.Index][5] = TBSerial.Text;
                dt.Rows[JOGridView.CurrentRow.Index][6] = TBAccess.Text;
                dt.Rows[JOGridView.CurrentRow.Index][7] = TBRB.Text;
                dt.Rows[JOGridView.CurrentRow.Index][8] = DTDR.Text;
                dt.Rows[JOGridView.CurrentRow.Index][9] = TBRP.Text;
                dt.Rows[JOGridView.CurrentRow.Index][10] = DTDF.Text;
                dt.Rows[JOGridView.CurrentRow.Index][11] = TBIT.Text;
                dt.Rows[JOGridView.CurrentRow.Index][12] = CBRamarks.Text;
                dt.Rows[JOGridView.CurrentRow.Index][13] = TBCharge.Text;
                dt.Rows[JOGridView.CurrentRow.Index][14] = TBRELB.Text;
                dt.Rows[JOGridView.CurrentRow.Index][15] = DTDR.Text;

                dt.Rows[JOGridView.CurrentRow.Index].EndEdit();

                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.Update(dt);


                displayrecords();

                MessageBox.Show("Selected record has been Updated!", "Done Updating ", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            clearrecords();
        }
    }

    private void TBSearch_KeyUp(object sender, KeyEventArgs e)
    {
        if (TBSearch.Text == " ")
        {

        }
        else
            con.Open();
        SqlCommand cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Select * from JobOrder where Name  like ('" + TBSearch.Text + "%')";
        cmd.ExecuteNonQuery();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        JOGridView.DataSource = dt;

        con.Close();
    }

Posted 25-Oct-17 3:28am

Azzyloth

Updated 25-Oct-17 8:06am

v2

Add a Solution

Comments

Richard Deeming 25-Oct-17 11:14am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

private void TBSearch_KeyUp(object sender, KeyEventArgs e)
{
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM JobOrder WHERE Name Like @Search + '%'";
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@Search", TBSearch.Text);
        
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        
        JOGridView.DataSource = dt;
    }
}

Azzyloth 25-Oct-17 12:42pm

Same result with my codes. instead of editing the one that i choose in search result, the first data from my database is the one that is changing :(

Richard Deeming 25-Oct-17 12:47pm

Because your "edit" function is loading all the data, not the data displayed in the grid.

You'll probably need to use the table's primary key to update just the record you're editing.

Azzyloth 25-Oct-17 13:04pm

i have no problem editing when all the data is in my datagridview. only in search result. can you give me some code sample for my edit button? or maybe just revise it if it is OK with you and if you're not busy.

Richard Deeming 25-Oct-17 13:27pm

What's the primary key for the JobOrder table?

Azzyloth 25-Oct-17 13:41pm

ID. I didn't include that ID on my dataset but when i add new data, the ID in my database is updating. Increamenting 1

Azzyloth 25-Oct-17 20:56pm

Did you create a class on your codes? i got an error saying "The class name '?' is not a valid identifier for this language. "

Richard Deeming 26-Oct-17 7:32am

The methods in my answer go in the same class as the methods in your question.

Richard Deeming 26-Oct-17 7:33am

What problem?

Azzyloth 27-Oct-17 8:43am

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near the keyword 'WHERE'.

That is my problem
My Primary key name is "ID" and its in the first column of my database. when it try to put dataset in my datagridview, i always delete the ID. thats why the first column in my datagridview is Name not the ID.

Azzyloth 28-Oct-17 7:25am

Its now working. It's just that your code here

WHERE
ID = @ID
;";

there has a ";" on it. HAHAHA

THANKS A LOT MATE!

1 solution

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)

Richard Deeming · Accepted Answer · 2017-10-25T08:06:00

Try something like this:

C#

private DataRowView GetCurrentRowData()
{
    if (JOGridView.CurrentCell == null) return null;
    
    int rowIndex = JOGridView.CurrentCell.RowIndex;
    if (rowIndex == -1) return null;
    
    return JOGridView.Rows[rowIndex].DataBoundItem as DataRowView;
}

private void JOGridView_DoubleClick(object sender, EventArgs e)
{
    DataRowView row = GetCurrentRowData();
    if (row != null)
    {
        TBName.Text = Convert.ToString(row["Name"]);
        TBContact.Text = Convert.ToString(row["Contact"]);
        CBStatus.Text = Convert.ToString(row["Status"]);
        TBModel.Text = Convert.ToString(row["Model"]);
        TBSerial.Text = Convert.ToString(row["Serial"]);
        TBAccess.Text = Convert.ToString(row["Access"]);
        TBRB.Text = Convert.ToString(row["RB"]);
        TBRP.Text = Convert.ToString(row["RP"]);
        TBIT.Text = Convert.ToString(row["IT"]);
        CBRamarks.Text = Convert.ToString(row["Remarks"]);
        TBCharge.Text = Convert.ToString(row["Charge"]);
        TBRELB.Text = Convert.ToString(row["RELB"]);
    }
}

private void BTNEdit_Click(object sender, EventArgs e)
{
    DataRowView row = GetCurrentRowData();
    if (row != null)
    (
        DialogResult dr = MessageBox.Show("Are you sure you want to edit this record?", "Update Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
        if (dr == DialogResult.Yes)
        {
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = @"UPDATE 
                    JobOrder 
                SET 
                    Name = @Name, 
                    Contact = @Contact, 
                    Status = @Status, 
                    Model = @Model, 
                    Serial = @Serial, 
                    Access = @Access, 
                    RB = @RB, 
                    RP = @RP, 
                    IT = @IT, 
                    Remarks = @Remarks, 
                    Charge = @Charge, 
                    RELB = @RELB 
                WHERE 
                    ID = @ID
                ;";
                
                cmd.CommandType = CommandType.Text;
                
                cmd.Parameters.AddWithValue("@Name", TBName.Text);
                cmd.Parameters.AddWithValue("@Contact", TBContact.Text);
                cmd.Parameters.AddWithValue("@Status", CBStatus.Text);
                cmd.Parameters.AddWithValue("@Model", TBModel.Text);
                cmd.Parameters.AddWithValue("@Serial", TBSerial.Text);
                cmd.Parameters.AddWithValue("@Access", TBAccess.Text);
                cmd.Parameters.AddWithValue("@RB", TBRB.Text);
                cmd.Parameters.AddWithValue("@RP", TBRP.Text);
                cmd.Parameters.AddWithValue("@IT", TBIT.Text);
                cmd.Parameters.AddWithValue("@Remarks", CBRamarks.Text);
                cmd.Parameters.AddWithValue("@Charge", TBCharge.Text);
                cmd.Parameters.AddWithValue("@RELB", TBRELB.Text);
                cmd.Parameters.AddWithValue("@ID", row["ID"]);
                
                con.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    con.Close();
                }
            }
            
            displayrecords();
            
            MessageBox.Show("Selected record has been updated!", "Done Updating", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
    
    clearrecords();
}

(You might need to fix the column names to match your table; I've just guessed based on your control names.)