Problem inserting value from Windows Form to database, Stored Proc, C#

Visual Studio 2017, Windows Form Application, C#, SQL Database, Stored Procedure.

I have have an issue trying to insert data from a windows form into a sql database using a stored procedure.  The form has textboxes and dropdown boxes.  The only error I get is thrown as an exception when testing the application.  No errors during compilation.  

The error is  System.Exception: 'Execption adding account. Failed to convert parameter value from a String to a Int32.'

The stored proc works fine.  I think the problem may be with the dropdown boxes.  The dropdown boxes are databound with the display name visible in the form, but the datavalue (an int, as it is the foreign key) is what needs to be saved to the database.  Can you suggest how I get the parameter to pick up the data value and not the display value from the form?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;


namespace _2017DispatchDesktop
{
    public partial class frm_FeedMultipleRecordsFutureDisp : Form
    {
        string str = ConfigurationManager.ConnectionStrings["DevTare2"].ConnectionString;





        public frm_FeedMultipleRecordsFutureDisp()
        {
            InitializeComponent();
        }

        private void frm_FeedMultipleRecordsFutureDisp_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'ds_FeedCommodity.tbl_Commodity' table. You can move, or remove it, as needed.
            this.tbl_CommodityTableAdapter.Fill(this.ds_FeedCommodity.tbl_Commodity);
            // TODO: This line of code loads data into the 'ds_FeedDelivery.tbl_GenDairy' table. You can move, or remove it, as needed.
            this.tbl_GenDairyTableAdapter.Fill(this.ds_FeedDelivery.tbl_GenDairy);
            // TODO: This line of code loads data into the 'ds_FeedShip.tbl_GenShip' table. You can move, or remove it, as needed.
            this.tbl_GenShipTableAdapter.Fill(this.ds_FeedShip.tbl_GenShip);
            // TODO: This line of code loads data into the 'ds_FeedCustomer.tbl_GenFrtCustomer' table. You can move, or remove it, as needed.
            this.tbl_GenFrtCustomerTableAdapter.Fill(this.ds_FeedCustomer.tbl_GenFrtCustomer);
            // TODO: This line of code loads data into the 'ds_Driver.Tbl_Driver' table. You can move, or remove it, as needed.
            this.tbl_DriverTableAdapter.Fill(this.ds_Driver.Tbl_Driver);


            txtInsertDate.Text = DateTime.Now.ToString("MM/dd/yyyy");
            txtSchDate.Text = DateTime.Now.ToString("MM/dd/yyyy");


        }

        protected void button1_Click(object sender, EventArgs e)
        {

            int it = 0;
            int notimes = Convert.ToInt32(multiply.Text);

            string connstring = ConfigurationManager.ConnectionStrings["DevTare2"].ConnectionString;
            SqlConnection con = new SqlConnection(connstring);
            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_InsertNewGenFreight";
            cmd.Parameters.Add("@SchDate", SqlDbType.Date).Value = txtSchDate.Text.Trim();
            cmd.Parameters.Add("@TruckNo", SqlDbType.NVarChar).Value = txtTruckNo.Text.Trim();
            cmd.Parameters.Add("@DriverID", SqlDbType.Int).Value = DropDownList1.Text.Trim();
            cmd.Parameters.Add("@Driver2ID", SqlDbType.Int).Value = ddlDriver2No.Text.Trim();
            cmd.Parameters.Add("@SubHaulCk", SqlDbType.Bit).Value = checkBox1.Checked;
            cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = ddlCustomer.Text.Trim();
            cmd.Parameters.Add("@ShipID", SqlDbType.Int).Value = ddlShip.Text.Trim();
            cmd.Parameters.Add("@DeliveryID", SqlDbType.Int).Value = ddlDelivery.Text.Trim();
            cmd.Parameters.Add("@CommodityID", SqlDbType.Int).Value = ddlCommodity.Text.Trim();
            cmd.Parameters.Add("@ReleaseNo", SqlDbType.NVarChar).Value = txtReleaseNo.Text.Trim();
            cmd.Parameters.Add("@Loads", SqlDbType.NVarChar).Value = txtLoads.Text.Trim();
            cmd.Parameters.Add("@InsertDate", SqlDbType.Date).Value = txtInsertDate.Text.Trim();
            cmd.Parameters.Add("@GoDispatch", SqlDbType.Bit).Value = ChkDispatch.Checked;
            cmd.Parameters.Add("@Complete", SqlDbType.Bit).Value = ChkComplete.Checked;
            cmd.Connection = con;


            try

            {
                con.Open();
                for (it = 1; it <= notimes; it++)

                    cmd.ExecuteNonQuery();
                //con.Close();

            }

            catch (Exception ex)

            {
              throw new Exception("Execption adding account. " + ex.Message);
            }
           

            finally

            {
                con.Close();

            }

           
        }

        //protected void btnAlelrt_Click(object sender, EventArgs e)
        //{

        //    //write your code which you wish to execute after the confirmation from the user....
        //    Console.WriteLine("Done");




        //   // Response.Redirect(Request.RawUrl);

Open in new window

LVL 5
rtayIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please replace these lines..

change done -->Convert.ToInt32(DropDownList1.Text.Trim());
---------------

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_InsertNewGenFreight";
cmd.Parameters.Add("@SchDate", SqlDbType.Date).Value = Convert.Date(txtSchDate.Text.Trim());
cmd.Parameters.Add("@TruckNo", SqlDbType.NVarChar).Value = txtTruckNo.Text.Trim();
cmd.Parameters.Add("@DriverID", SqlDbType.Int).Value = Convert.ToInt32(DropDownList1.Text.Trim());
cmd.Parameters.Add("@Driver2ID", SqlDbType.Int).Value = Convert.ToInt32(ddlDriver2No.Text.Trim());
cmd.Parameters.Add("@SubHaulCk", SqlDbType.Bit).Value = checkBox1.Checked;
cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = Convert.ToInt32(ddlCustomer.Text.Trim());
cmd.Parameters.Add("@ShipID", SqlDbType.Int).Value = Convert.ToInt32(ddlShip.Text.Trim());
cmd.Parameters.Add("@DeliveryID", SqlDbType.Int).Value = Convert.ToInt32(ddlDelivery.Text.Trim());
cmd.Parameters.Add("@CommodityID", SqlDbType.Int).Value = Convert.ToInt32(ddlCommodity.Text.Trim());
cmd.Parameters.Add("@ReleaseNo", SqlDbType.NVarChar).Value = txtReleaseNo.Text.Trim();
cmd.Parameters.Add("@Loads", SqlDbType.NVarChar).Value = txtLoads.Text.Trim();
cmd.Parameters.Add("@InsertDate", SqlDbType.Date).Value = Convert.Date(txtInsertDate.Text.Trim());
cmd.Parameters.Add("@GoDispatch", SqlDbType.Bit).Value = ChkDispatch.Checked;
cmd.Parameters.Add("@Complete", SqlDbType.Bit).Value = ChkComplete.Checked;
cmd.Connection = con;
rtayIT DirectorAuthor Commented:
image of new error attached.
it_saigeDeveloperCommented:
Unless your DropDownList is displaying a number, you will want the SelectedValue and not the displayed portion.

Proof of concept -

Form1.cs -
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace EE_Q29070983
{
    public partial class Form1 : Form
    {
        bool isLoading = true;

        public Form1()
        {
            InitializeComponent();
        }

        private void OnLoad(object sender, EventArgs e)
        {
            comboBox1.DataSource = (from i in Enumerable.Range(0, 20) select new { DisplayMember = i, ValueMember = i }).ToList();
            comboBox1.DisplayMember = "DisplayMember";
            comboBox1.ValueMember = "ValueMember";
            comboBox2.DataSource = (from i in Enumerable.Range(0, 20) select new { DisplayMember = $"Displayed_{i}", ValueMember = i }).ToList();
            comboBox2.DisplayMember = "DisplayMember";
            comboBox2.ValueMember = "ValueMember";
            isLoading = false;
        }

        private void OnSelectedValueChanged(object sender, EventArgs e)
        {
            if (sender is ComboBox && !isLoading)
            {
                var ddl = sender as ComboBox;
                textBox1.AppendText($"Display Member - {ddl.DisplayMember}; Value Member - {ddl.ValueMember}; ");
                if (ddl.Equals(comboBox1))
                {
                    textBox1.AppendText($"Converting Display Member To Int32 - {Convert.ToInt32(ddl.Text)}\r\n");
                }
                else if (ddl.Equals(comboBox2))
                {
                    textBox1.AppendText($"Converting Value Member To Int32 - {Convert.ToInt32(ddl.SelectedValue)}\r\n");
                }
            }
        }
    }
}

Open in new window

Form1.Designer.cs -
namespace EE_Q29070983
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.label1 = new System.Windows.Forms.Label();
            this.comboBox1 = new System.Windows.Forms.ComboBox();
            this.comboBox2 = new System.Windows.Forms.ComboBox();
            this.label2 = new System.Windows.Forms.Label();
            this.textBox1 = new System.Windows.Forms.TextBox();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(12, 13);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(152, 13);
            this.label1.TabIndex = 0;
            this.label1.Text = "DropDown displaying Numbers";
            // 
            // comboBox1
            // 
            this.comboBox1.FormattingEnabled = true;
            this.comboBox1.Location = new System.Drawing.Point(12, 29);
            this.comboBox1.Name = "comboBox1";
            this.comboBox1.Size = new System.Drawing.Size(260, 21);
            this.comboBox1.TabIndex = 1;
            this.comboBox1.SelectedValueChanged += new System.EventHandler(this.OnSelectedValueChanged);
            // 
            // comboBox2
            // 
            this.comboBox2.FormattingEnabled = true;
            this.comboBox2.Location = new System.Drawing.Point(12, 69);
            this.comboBox2.Name = "comboBox2";
            this.comboBox2.Size = new System.Drawing.Size(260, 21);
            this.comboBox2.TabIndex = 3;
            this.comboBox2.SelectedValueChanged += new System.EventHandler(this.OnSelectedValueChanged);
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Location = new System.Drawing.Point(12, 53);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(131, 13);
            this.label2.TabIndex = 2;
            this.label2.Text = "DropDown displaying Text";
            // 
            // textBox1
            // 
            this.textBox1.Location = new System.Drawing.Point(12, 97);
            this.textBox1.Multiline = true;
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new System.Drawing.Size(260, 152);
            this.textBox1.TabIndex = 4;
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(284, 261);
            this.Controls.Add(this.textBox1);
            this.Controls.Add(this.comboBox2);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.comboBox1);
            this.Controls.Add(this.label1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.OnLoad);
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.ComboBox comboBox1;
        private System.Windows.Forms.ComboBox comboBox2;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.TextBox textBox1;
    }
}

Open in new window

Produces the following results -Capture.PNG
-saige-

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.