Insert a Combobox ValueMember to database

[Woodenhousen]

I need to insert a record to another table with the FK ID taken from another table. For example, the end user needs to create a unique product which is made up from various components from other tables.

Product Table Component1 Table Component2Table Component3Table

ProductID Comp1ID Comp2ID Comp3ID

Name Name Name Name

Comp1ID

Comp2ID

Comp3ID

I need to insert a row in to the Product table with the IDs taken from each component table. I am using a stored procedure which I know works OK when I run it through SQL Server Management Studio and I add the values manually but how do I get each combobox to pass the IDs back to the stored procedure.

Each combobox code loads data




private void NewProduct_Load(object sender, EventArgs e)
{

connection.ConnectionString = "connection string";

command.CommandText = "SELECT * FROM Product;" + SELECT * FROM Component1; " +
"SELECT * FROM Component2; " +
"SELECT * FROM Component3;";

SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.SelectCommand.Connection = connection;
adapter.Fill(dSet);

cbxComp1.DisplayMember = "Name";
cbxComp1.ValueMember = "Comp1ID";
cbxComp1.DataSource = dSet.Tables[0];
cbxComp1.Text = "";

cbxComp2.DisplayMember = "Name";
cbxComp2.ValueMember = "Comp2ID";
cbxComp2.DataSource = dSet.Tables[2];
cbxComp2.Text = "";


Each Parameter is set




insertCommand.Connection = connection;
insertCommand.CommandText = "proc_insertNewProduct";
insertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter modParameter = new SqlParameter();
modParameter.ParameterName = "@Comp1ID";
modParameter.SqlDbType = SqlDbType.Int;
modParameter.Direction = ParameterDirection.Input;
modParameter.Value = cbxComp1.ValueMember;

SqlParameter tranParameter = new SqlParameter();
tranParameter.ParameterName = "@Comp2ID";
tranParameter.SqlDbType = SqlDbType.Int;
tranParameter.Direction = ParameterDirection.Input;
tranParameter.Value = cbxComp2.ValueMember;

I get an error "Failed to convert parameter value from a String to a Int32" so it looks to me like I am passing the incorrect datatype back to the database????? My executeNonQuery statement is


if (connection.State == ConnectionState.Open)
{
int rows = command.ExecuteNonQuery();
if (rows > 0)
MessageBox.Show("blah blah");

I am new to C# and very much experimenting with code, please help!

Continue reading...