Issue in adding the Selected Value in Access DataBase

[GagaCode]

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

[cgeier]

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.

[cgeier]

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.

[cgeier]

Does your database use constraints? Do you have a database diagram?

[GagaCode]

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