Updating data pulled into the form

Mehmet Fatih

I have a table where my constant information is stored. I want to replace the city and town names in this fixed table with city and dsitrict names that I pulled from other tables named city and district. But it is sending blank data. I could never understand why.

private void getcity()
{
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }    
    OleDbCommand cmd = new OleDbCommand("select * from city ORDER BY id ASC", conn);
    OleDbDataReader dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        comboBox1.Items.Add(dr["sehir"].ToString());
        comboBox1.ValueMember = (dr["id"].ToString());
     }
}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    if (comboBox1.SelectedIndex != -1)
    {
        DataTable dt = new DataTable();
        OleDbDataAdapter da = new OleDbDataAdapter("select * from ilceler where city= " + comboBox1.SelectedValue, conn);
        da.Fill(dt);
        comboBox2.ValueMember = "id";
        comboBox2.DisplayMember = "town";
        comboBox2.DataSource = dt;
    }
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago

Accepted answer

See below my observation:

1. Connection Management
- Consider using the `using` statement to ensure proper disposal of the connection object.
- Instead of explicitly checking and opening the connection, you can rely on the connection's automatic state management.

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    // Rest of the code here
}

2. Use `using` statement for disposable objects
- Wrap the `OleDbCommand`, `OleDbDataReader`, and `OleDbDataAdapter` objects in `using` statements to ensure they are properly disposed of after use.

using (OleDbCommand sorgu = new OleDbCommand("SELECT * FROM city ORDER BY id ASC", conn))
{
    using (OleDbDataAdapter adp = new OleDbDataAdapter(sorgu))
    {
        DataTable dt = new DataTable();
        adp.Fill(dt);
        comboBox1.ValueMember = "id";
        comboBox1.DisplayMember = "city";
        comboBox1.DataSource = dt;
    }
}

using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM kurumbilgisi2541 WHERE kid = 1", conn))
{
    using (OleDbDataReader dr = cmd.ExecuteReader())
    {
        if (dr.Read())
        {
            comboBox1.Text = dr["city"].ToString();
            comboBox2.Text = dr["district"].ToString();
        }
    }
}

3. Proper exception handling
- Wrap the database operations in appropriate `try-catch` blocks to handle any exceptions that may occur during the execution.

try
{
    // Code snippet here
}
catch (OleDbException ex)
{
    // Handle the exception or log the error
    // Example: MessageBox.Show("An error occurred: " + ex.Message);
}

4. Optimize database queries
- Instead of using `sorgu.ExecuteNonQuery()` before filling the `DataTable`, you can directly fill the `DataTable` using the `OleDbDataAdapter.Fill` method.

DataTable dt = new DataTable();
OleDbDataAdapter adp = new OleDbDataAdapter(sorgu);
adp.Fill(dt);

Mohamed Azarudeen Z3y ago
Great ??
Mehmet Fatih3y ago
Thanks for your intrest Mohamed. I solved this problem by myself. I changed the codes like that.

if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
comboBox1.Items.Clear();
OleDbCommand sorgu = new OleDbCommand("select * from cityORDER BY id ASC", conn);
sorgu.ExecuteNonQuery();

DataTable dt = new DataTable();
OleDbDataAdapter adp = new OleDbDataAdapter(sorgu);
adp.Fill(dt);
comboBox1.ValueMember = "id";
comboBox1.DisplayMember = "city";
comboBox1.DataSource = dt;

OleDbCommand cmd = new OleDbCommand("Select * from kurumbilgisi2541 where kid=1", conn);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Text = dr["city"].ToString();
comboBox2.Text = dr["district"].ToString();
}
Mohamed Azarudeen Z3y ago
The issue you mentioned, where it is sending blank data, could be due to a couple of reasons. Here are some suggestions to help troubleshoot the problem:
1. Verify Database Connectivity: Make sure you have a successful connection to the database before executing any queries. You can check if the connection is open by adding a debug statement or using breakpoints to verify that the conn.State is set to ConnectionState.Open.
2. Check Data Availability: Verify that the "city" and "ilceler" tables contain the expected data. You can examine the tables in your Access database to ensure they have the necessary columns and that the data is populated correctly.
3. Check ComboBox Binding: Ensure that the comboBox1 and comboBox2 properties are correctly set up for data binding. Confirm that the ValueMember and DisplayMember properties are set to the correct column names in the data tables.
4. Validate Query Parameters: In the comboBox1_SelectedIndexChanged method, you are constructing a SQL query by concatenating the comboBox1.SelectedValue without proper parameterization. This can lead to SQL injection vulnerabilities and incorrect query execution. Consider using parameterized queries instead to avoid such issues.
Here's an example of how you can modify your code to use parameterized queries:
```
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    if (comboBox1.SelectedIndex != -1)
    {
        DataTable dt = new DataTable();
        string query = "SELECT * FROM ilceler WHERE city = ?";
        OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
        da.SelectCommand.Parameters.AddWithValue("@city", comboBox1.SelectedValue);
        da.Fill(dt);
        comboBox2.ValueMember = "id";
        comboBox2.DisplayMember = "town";
        comboBox2.DataSource = dt;
    }
}
```
By using parameterized queries, you can ensure that the query is executed correctly, even if the selected value contains special characters.

Make sure to check for any error messages or exceptions that might provide additional insights into the issue. Logging or displaying error messages can help identify the root cause of the problem.