Hey All i really have an issue that it is really stopping me here
so basicly i have a form that i use to add some Address Book data so i have combobox's that is synchronized with each other
so @ first i'm populating the combobox with the following function :

    public static void FillDropDownList(string Query, System.Windows.Forms.ComboBox DropDownName, string AValue, string Adisplay)
    {
        string Value = AValue;
        string display = Adisplay;

        using (var CONN = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\Vendors.accdb;"))
        {
            CONN.Open();
            DataTable dt = new DataTable();
            try
            {
                OleDbCommand cmd = new OleDbCommand(Query, CONN);
                OleDbDataReader myReader = cmd.ExecuteReader();
                dt.Load(myReader);
                /// MessageBox.Show (dt.Rows.Count.ToString());
                // Console.ReadLine();


            }
            catch (OleDbException e)
            {
                MessageBox.Show(e.ToString());
                // Console.WriteLine(e.ToString());
                // Console.ReadLine();

                return;
            }
            DropDownName.DataSource = dt;
            DropDownName.ValueMember = Value;
            DropDownName.DisplayMember = display;
        }
    }

after that i'm calling the function in the load like this :

FillDropDownList("select COUNTRYCODE,COUNTERYNAME from COUNTRIES", Country_CB, "COUNTRYCODE", "COUNTERYNAME");

and the above code i use to populate a combobox with the countries around the globe

after that to sync each country with it's state i used the following event

private void Country_CB_SelectionChangeCommitted(object sender, EventArgs e)
    {
        ///this is to fill the Gov combo box                     
            FillDropDownList("select STATECODE,STATENAME from STATES Where COUNTERYCODE =  '" + Country_CB.SelectedValue   + "' ORDER BY STATENAME ASC;", Gov_CB, "STATECODE", "STATENAME");               
       }

after that i add only the value member in to the table in database
however i mistakenly forget to add some states for som country so when i tried to add to the database using the following code

 private void button2_Click(object sender, EventArgs e)
    {
using (OleDbConnection con = new OleDbConnection(StrConn))
            {
                con.Open();
                string Sql = "INSERT INTO AddresBookMain (Country, State)" +
                             "VALUES (@Branch, @State)";
                using (OleDbCommand cmd = new OleDbCommand(Sql, con))
                {
                    cmd.Parameters.AddWithValue("@Country", Country_CB.SelectedValue);
                    cmd.Parameters.AddWithValue("@State", StateCB.SelectedValue);

                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Successfully Done ", "Done  ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }

When i tried to add i found the following error cuz simply there where nothing populated into the state combobox and the error is :

Parameter @State has no default value.

so what should i do to resovle this kind of issue ??

i tried something like this

   if (State.DataSource == null || State.ValueMember == null)
        {
            State.Items.Add("no");
            State.ValueMember.Contains("0");
            State.SelectedValue = 0;
            MessageBox.Show("the Zero");
        }   

but it still gives me the same error so is there any way to overcome this issue

If you have the ability to modify the database, you could change "Required" to "false" for the column, or assign a default value.

Alternatively, you could programmatically assign a default value. See below for an example:

*Note: Need using System.Data.OleDb; statement.

private static OleDbConnection dbConn = null;
static string connectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|Vendors.accdb;";




    public static void InsertAddressBookMainTbl(ref OleDbConnection dbConn, string Country, string State)
    {
        OleDbCommand sqlCmd = null;
        string sqlText = string.Empty;
        string msg = string.Empty;

        try 
        {
            if (dbConn == null)
            {
                dbConn = new OleDbConnection(connectStr);
            } //if 

            if (dbConn.State != System.Data.ConnectionState.Open)
            {
                dbConn.Open();
            } //if 

            sqlCmd = new OleDbCommand();
            sqlText = @"INSERT INTO AddressBookMain(Country, State)
                            VALUES(@Country, @State)";

            sqlCmd.CommandText = sqlText;
            sqlCmd.Connection = dbConn; 

            OleDbParameter paramName0 = new OleDbParameter();
            paramName0.ParameterName = "@Country"; 
            paramName0.OleDbType = OleDbType.VarChar; 
            paramName0.Direction = ParameterDirection.Input; 
            //paramName0.Value = (object)Country ?? DBNull.Value; 

            //if the value is null or empty
            //the value will be "unavailable"
            paramName0.Value = (object)Country ?? "unavailable"; 
            sqlCmd.Parameters.Add(paramName0); 

            OleDbParameter paramName1 = new OleDbParameter();
            paramName1.ParameterName = "@State"; 
            paramName1.OleDbType = OleDbType.VarChar; 
            paramName1.Direction = ParameterDirection.Input; 
            //paramName1.Value = (object)State ?? DBNull.Value; 

            //if the value is null or empty
            //the value will be "unavailable"
            paramName1.Value = (object)State ?? "unavailable"; 
            sqlCmd.Parameters.Add(paramName1); 

            sqlCmd.ExecuteNonQuery();
        } //try
        catch(System.Data.OleDb.OleDbException ex)
        {
            msg = "ERROR: InsertAddressBookMainTbl: " + ex.Message + System.Environment.NewLine + System.Environment.NewLine;
            Console.WriteLine(msg);
            throw ex;
        } //catch
        catch(Exception ex)
        {
            msg = "ERROR: InsertAddressBookMainTbl: " + ex.Message + System.Environment.NewLine + "SQL:" + sqlText + System.Environment.NewLine;
            Console.WriteLine(msg);
            throw ex;
        } //catch
        finally
        {
            if (sqlCmd != null)
            {
                sqlCmd.Dispose();
            } //if
        } //finally
    } //InsertAddressBookMainTbl

To use:

  • InsertCountriesTbl(ref dbConn, "USA", null);
  • InsertCountriesTbl(ref dbConn, "USA", "Alaska");

However you need to ask yourself if your table really should contain null values or invalid/unusable data.

Edited 2 Years Ago by cgeier

Comments
a great answer i did not use the same function but it really helped me alot to find the answer thank you for the second time

Also, you may Click Here to see about checking if the column allows nulls ("AllowDBNull"). It didn't seem to work for an Access db when I tried it though--AllowDBNull always seemed to be true.

Edited 2 Years Ago by cgeier

@cgeier thank you for the help the your code supplied a solution for me

This question has already been answered. Start a new discussion instead.