How to add an item to combobox if no item found?

Question

Greetings experts,

We have a database called Types that has two fields, TypeID, and Type Name.

Then we have a textbox called newType.

The idea of this textbox is that when a user attempts to select an item from the combobox and that item does not exist in the combobox, then enter the new item into the newType textbox and submit to the database.

Once submitted, it now becomes available for selection in the combobox.

Here is how I have approached this problem:

//Populate Combobox1
SqlDataAdapter sda1 = new SqlDataAdapter("SELECT DISTINCT TypeId, TypeName FROM Types", constr);
//Fill the DataTable with records from Table.
DataTable dt = new DataTable();
sda1.Fill(dt);
//Insert the Default Item to DataTable.
DataRow row = dt.NewRow();
row[0] = 0;
row[1] = "Please select member";
dt.Rows.InsertAt(row, 0);
//Assign DataTable as DataSource.
txtType.DataSource = dt;
txtType.DisplayMember = "TypeName";
txtType.ValueMember = "TypeId";
txtType.SelectedIndex = 0;

Once a value has been entered into the newType textbox field, I process it:

//check if this is new type and insert into Types table
if (mID <= 0)
{
SqlCommand cmd = new SqlCommand("insert into Types(TypeName) VALUES(@typeName)SELECT SCOPE_IDENTITY()", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@typeName", txtTypeName.SelectedItem);
con.Open();
//get the newly inserted ID - to be used later
int typeid = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
if (typeid != 0)
{
MessageBox.Show("Record Saved Successfully");
}
//insert new typeid along with other fields into Transactions table
…
...

The problem is when I click submit to add the newType to the database, I am getting the following error message:

System.ArgumentException: No mapping exists from object type System.Data.DataRowView to a known managed provider native type.
at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)

Any ideas what I am doing wrong?

Thanks in advance

Sam Okeh · Answer

Can anyone here please helP?

Sam Okeh · Answer

Hi Rajanikant, Thanks for your response. I want to insert a new type into Types table if value for type does not exist in txtTypeNames combobox. If however, it exists, I want to select it from the combobox and insert it along with other fields into the Transactions table. Here is the entire code (sorry) that shows how I am using ExecuteScalar() private void btnSubmit_Click( object sender, EventArgs e) { var tID = txtTypeName.SelectedIndex; string constr = ConfigurationManager.ConnectionStrings[ "constring" ].ConnectionString; if (paymentType.SelectedIndex == 0) //Nothing selected { MessageBox.Show( "Please select a value from the dropdown" , "Error" ); } else { using (SqlConnection con = new SqlConnection(constr)) { if (tID { SqlCommand cmd = new SqlCommand( "insert into Types(typename,date_registered) VALUES(@typename,@date_registered)SELECT SCOPE_IDENTITY()" , con); //int typeid; cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue( "@typename" , newType.Text); cmd.Parameters.AddWithValue( "@date_registered" , registration_date.Text); //cmd.Parameters.AddWithValue("@email", email.Text); //cmd.Parameters.AddWithValue("@phone", registration_date.Text); //cmd.Parameters.AddWithValue("@address", registration_date.Text); con.Open(); //int i = cmd.ExecuteNonQuery(); int typeid = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); } else { SqlCommand cmd3 = new SqlCommand( "insert into Types(typename,date_registered) VALUES(@typename,@date_registered)SELECT SCOPE_IDENTITY()" , con); //int typeid; cmd3.CommandType = CommandType.Text; cmd3.Parameters.AddWithValue( "@Types" , txttypename.SelectedValue); cmd3.Parameters.AddWithValue( "@date_registered" , registration_date.Text); //cmd.Parameters.AddWithValue("@email", email.Text); //cmd.Parameters.AddWithValue("@phone", registration_date.Text); //cmd.Parameters.AddWithValue("@address", registration_date.Text); con.Open(); //int i = cmd.ExecuteNonQuery(); int typeid = Convert.ToInt32(cmd3.ExecuteScalar()); con.Close(); DateTime edate = DateTime.Today; SqlCommand cmd1 = new SqlCommand( "insert into Transactions(typeid,TypeID,Amount,AmountPaid,Balance,registrationFee, envelopeNumber,eventYear) VALUES(@typeid,@paymenttype,@startingamount,@amountpaid,@amountowed,@reg_fee,@envelopenum,@eventyear)" , con); cmd1.CommandType = CommandType.Text; cmd1.Parameters.AddWithValue( "@typeid" , typeid); cmd1.Parameters.AddWithValue( "@paymenttype" , paymentType.SelectedValue); cmd1.Parameters.AddWithValue( "@startingamount" , annual_dues.Text); cmd1.Parameters.AddWithValue( "@amountpaid" , Amt_Paid.Text); cmd1.Parameters.AddWithValue( "@amountowed" , Amt_Owed.Text); cmd1.Parameters.AddWithValue( "@reg_fee" , reg_fee.Text); cmd1.Parameters.AddWithValue( "@envelopenum" , envelope_number.Text); cmd1.Parameters.AddWithValue( "@eventyear" , edate); //cmd1.Parameters.AddWithValue("@date_registered", registration_date.Text); con.Open(); int i = cmd1.ExecuteNonQuery(); con.Close(); if (i != 0) { MessageBox.Show( "Record Saved Successfully" ); } } paymentType.Text = "" ; txttypename.Text = "" ; registration_date.Text = "" ; envelope_number.Text = "" ; //Phone.Text = ""; //Address.Text = ""; } } }

Rajanikant Hawaldar · Answer

HI Sam Okeh, Can you tell me When to use ExecuteScalar() ?

Rafnas T P · Answer

hi, you can try with txtTypeName.Text cmd.Parameters.AddWithValue("@typeName", txtTypeName.Text ); or cmd.Parameters.AddWithValue("@typeName", txtTypeName.SelectedValue);

Sam Okeh · Answer

Thank you so very much for your response. Inititially, I had this: cmd.Parameters.AddWithValue("@typeName", txtTypeName.SelectedIndex); This added a record to the Types table with no issues. Problem is that it added a zero (0). I wanted to add the name like Benefits instead of an integer value. Recall that Types table has two columns, TypeID and TypeName and TypeID is an integer with auto increment. So, I want to add the text value knowing that TypeID value will be added automatically. I tried txtTypeName.SelectedText and still got same error. I have done this before with asp.net webform; just don't know how to do this with windows form.

Amit Gupta · Answer

txtTypeName.SelectedItem contains the whole row( i.e 0 and 1 index), you need to fetch the index 1 from that row (a single value)

Amit Gupta · Answer

Issue is in this line cmd.Parameters.AddWithValue("@typeName", txtTypeName.SelectedItem);