0

I have 3 tables:

1) House:

| ID_house | House_names | num_region |
---------------------------------------
|   int    |    names    |    int     |

num_region - a foreign key, which equals to a primary key(Region.ID_region) in Region table.

2) Region:

| ID_region | Region_names | num_area |
---------------------------------------
|   int     |    names     |   int    |

num_area - foreign key, equals to primary key(Areas_InRegion.ID_area) in Areas_InRegion table.

3) Areas_InRegion:

| ID_area | Area_names |
------------------------
|   int   |    names   |

In the form I have 3 comboboxes:

1) cmbHouse - for showing names of houses from House table.

2) cmbRegion - for showing names of regions from Region table.

3) cmbArea - for showing names of areas in regions from Areas_InRegion table.

I populate these combos like:

        //cmbHouse
        string cmbHouse_query = "SELECT * FROM House";
        OleDbDataAdapter dahouse = new OleDbDataAdapter(cmbHouse_query, connection);
        DataTable tablehouse = new DataTable();
        dahouse.Fill(tablehouse);
        cmbHouse.DataSource = tablehouse;
        cmbHouse.DisplayMember = "House_names"; 
        cmbHouse.ValueMember = "House.num_region";
        cmbHouse.SelectedIndex = -1;

        //cmbRegion
        string cmbRegion_query = "SELECT * FROM Region";
        OleDbDataAdapter daregion = new OleDbDataAdapter(cmbRegion_query, connection);
        DataTable tableregion = new DataTable();
        daregion.Fill(tableregion);
        cmbRegion.DataSource = tableregion;
        cmbRegion.DisplayMember = "Nameofregions";
        cmbRegion.ValueMember = "Region.ID_region";
        cmbRegion.SelectedIndex = -1;

        //cmbArea
        string cmbArea_query = "SELECT * FROM Areas_InRegion";
        OleDbDataAdapter daArea = new OleDbDataAdapter(cmbArea_query, connection);
        DataTable tablearea = new DataTable();
        daArea.Fill(tablearea);
        cmbArea.DataSource = tablearea;
        cmbArea.DisplayMember = "Names_OfAreas";
        cmbArea.ValueMember = "Areas_InRegion.ID_areas";
        cmbArea.SelectedIndex = -1;

I can filter cmbRegion combo by the selection in cmbHouse combo:

    private void cmbHouse_SelectionChangeCommitted(object sender, EventArgs e)
    {
        if (cmbHouse.SelectedIndex != null)
        {
            int num_region = Convert.ToInt32(cmbHouse.SelectedValue);
            OleDbCommand com = new OleDbCommand();
            com.CommandText = "SELECT * FROM Region WHERE Region.ID_region=" + num_region.ToString() + "";
            com.Parameters.AddWithValue("House.num_region", typeof(int));
            OleDbDataAdapter danum_region = new OleDbDataAdapter(com.CommandText, connection);
            DataTable tablenum_region = new DataTable();
            danum_region.Fill(tablenum_region);
            cmbRegion.DataSource = tablenum_region;
            cmbRegion.DisplayMember = "Nameofregions";
            cmbRegion.ValueMember = "Region.ID_region";
            cmbRegion.SelectedIndex = -1;    
        }  
    }

But when I try filtering cmbRegion code in filtering cmbArea combo, I get an error 'No value given for one or more required parameters.' in here:

    private void cmbRegion_SelectionChangeCommitted(object sender, EventArgs e)
    {            
        int num_area = Convert.ToInt32(cmbRegion.SelectedValue);
        OleDbCommand com2 = new OleDbCommand();
        com2.CommandText = "SELECT * FROM Areas_InRegion WHERE Region.num_arearegion=" + num_area.ToString() + "";
        com2.Parameters.AddWithValue("Region.num_arearegion", typeof(int));
        OleDbDataAdapter danum_area = new OleDbDataAdapter(com2.CommandText, connection);
        DataTable tablenum_area = new DataTable();
        danum_area.Fill(tablenum_area); //Error 'No value given for one or more required parameters.' appears here
        cmbArea.DataSource = tablenum_area;
        cmbArea.DisplayMember = "Names_OfAreas";
        cmbArea.ValueMember = "Areas_InRegion.ID_areas";
        cmbArea.SelectedIndex = -1;
    }

Help me to solve this problem. Thank you for helping.

Saint Man
  • 5
  • 4
  • `com2.Parameters.AddWithValue("Region.num_arearegion", typeof(int));` => `AddWithValue` expects an object as second parameter. not a type. – Sebastian Siemens May 23 '19 at 08:33
  • Also, your statements are concatenated but should contain the parameter... Look here for a working example: https://stackoverflow.com/questions/38738490/exception-no-value-given-for-one-or-more-required-parameters-in-vb-net-ms-acc It should be `Region.num_arearegion=@numarea"` and `AddWithValue("@numarea", num_area);` – Sebastian Siemens May 23 '19 at 08:38
  • What does '@' sign mean in query? Do I have problem only with parameters? – Saint Man May 23 '19 at 08:54
  • I think SQL expects an `@` at the beginning of a variable. Like when you write a SQL Statement where you declare a variable. `DECLARE @myvalue varchar(5)` – Sebastian Siemens May 23 '19 at 09:06
  • `"SELECT * FROM Areas_InRegion WHERE Region.num_arearegion=" + num_area.ToString()` => If you concatenate the SQL Statement like this, you dont need the parameter as the statement already contains the value. But it is always better to use parameter as they are more safe. – Sebastian Siemens May 23 '19 at 09:18

0 Answers0