Datatable always retrieving first record of the table in c#

Question

I am performing search operation based on id which is auto incremented when I enter id as 0 it is retrieving data which is having id 1.

This is the search method

public DataTable Search(int code=0)
    {
        SqlConnection con = sqlConnection;
        String Query = "Select * from person" + (code > 0 ? " where code =" + code : "");
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(Query, con);
        {
            DataTable dataTable = new DataTable("Temp");
            sqlDataAdapter.Fill(dataTable);
            con.Close();
            return dataTable;
        }
    }

This is search button code

private void btnSearch_Click(object sender, EventArgs e)
    {
        if(txtSearchCode.Text.Length>0)
        {
            DataTable dataTable = new SqlHelper().Search(int.Parse(txtSearchCode.Text));
            if(dataTable.Rows.Count>0)
            {
                try
                {
                    txtCode.Text = dataTable.Rows[0]["code"].ToString();
                    txtFirstname.Text = dataTable.Rows[0]["firstname"].ToString();
                    txtLastname.Text = dataTable.Rows[0]["lastname"].ToString();
                    var a = dataTable.Rows[0]["gender"].ToString() == "Male" ? rbnMale.Checked = true : rbnFemale.Checked = true;
                    txtMobile.Text = dataTable.Rows[0]["mobile"].ToString();
                    dtpDOB.Text = dataTable.Rows[0]["dob"].ToString();
                    txtAge.Text = dataTable.Rows[0]["age"].ToString();
                    chkStatus.Checked = dataTable.Rows[0]["status"].ToString() == "active"? true :false;
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show("No Data Found");
            }
        }
        else
        {
            MessageBox.Show("Please Enter Code");
        }
    }

When you use Code = 0; you actually are doing select * from table. But in the btnSearch_Click handler you are always lprocessing 0th Rows from the resultset — Prateek Shrivastava, Feb 10 '20 at 06:13
even if i removed code=0 also it is retrieving the record which is first record of the database @PrateekShrivastava — Ramu Eppakayala, Feb 10 '20 at 06:16
Coz you have hardcoded "Rows[0]" --- no matter how many records your query returns you will always read first record. — Prateek Shrivastava, Feb 10 '20 at 06:17

score 2 · Accepted Answer · answered Feb 10 '20 at 07:15

You are not sending a where class if your conditional expression code > 0 is not satisfied, which is why sql-server returns all rows from the table person. Then when you pick the first row from the datatable using dataTable.Rows[0] it picks the one with id 1. A better and secure way is to use a parameterized query instead.

String Query = "Select * from person where code = @code";
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(Query, con);
sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@code",code);

don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) — SMor, Feb 10 '20 at 14:08

Prateek Shrivastava · Answer 2 · 2020-02-10T06:20:01.860

-1

When you use Code = 0; you actually are doing select * from table.

But in the btnSearch_Click handler you are always processing 0th Rows from the resultset.

txtCode.Text = dataTable.Rows[0]["code"].ToString(); <------ See this line you have [0] hardcoded

Possibly the record with id 1 is first record in your query Result Set.

Edit:

You need to Loop through dataTable.Rows[] array and capture/process each result.

edited Feb 10 '20 at 06:20

answered Feb 10 '20 at 06:14

Prateek Shrivastava

1,877
1
10
17

Datatable always retrieving first record of the table in c#

2 Answers2