How to Convert Web Form Values for Stored Procedure Parameters Using TableAdapters

Question

I am creating a Web Form using TableAdapters with stored procedures and I am having a very difficult time finding a reference for the proper way to convert values from form fields for stored procedure parameters. I have strings, dates, and integers. I think the strings and dates are formatted correctly, but I am unsure about the integers.

I keep getting an "Input string was not in a correct format." error when I run the application. What am I doing wrong?

Thank you in advance for your time and assistance!

Here is the TableAdapter Wizard-generated test stored procedure:

CREATE PROCEDURE [dbo].InsertQuery
(
@firmName nvarchar(100),
@address nvarchar(200),
@city nvarchar(50),
@state nvarchar(2),
@zipcode nvarchar(15),
@phoneNumber nvarchar(MAX),
@emailAddress nvarchar(MAX),
@website nvarchar(MAX),
@desiredEffDate datetime,
@desiredLimits nvarchar(50),
@desiredLimitsOther1 nvarchar(100),
@desiredLimitsOther2 nvarchar(100),
@desiredDeductible nvarchar(20),
@retroDate datetime,
@insurance nvarchar(10),
@currentCarrier nvarchar(100),
@currentPremium nvarchar(50),
@firmEstablished datetime,
@numParalegals int,
@numStaff int,
@engageLetters nvarchar(10),
@conflictSystem nvarchar(10),
@docketSystem nvarchar(10),
@sueFees nvarchar(10),
@numAttorneys int,
@numClaims int,
@complaint nvarchar(10),
@refusedAdmission nvarchar(10),
@signature nvarchar(200)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Firms] ([firmName], [address], [city], [state], [zipcode], [phoneNumber], [emailAddress], [website], [desiredEffDate], [desiredLimits], [desiredLimitsOther1], [desiredLimitsOther2], [desiredDeductible], [retroDate], [insurance], [currentCarrier], [currentPremium], [firmEstablished], [numParalegals], [numStaff], [engageLetters], [conflictSystem], [docketSystem], [sueFees], [numAttorneys], [numClaims], [complaint], [refusedAdmission], [signature]) VALUES (@firmName, @address, @city, @state, @zipcode, @phoneNumber, @emailAddress, @website, @desiredEffDate, @desiredLimits, @desiredLimitsOther1, @desiredLimitsOther2, @desiredDeductible, @retroDate, @insurance, @currentCarrier, @currentPremium, @firmEstablished, @numParalegals, @numStaff, @engageLetters, @conflictSystem, @docketSystem, @sueFees, @numAttorneys, @numClaims, @complaint, @refusedAdmission, @signature)

Here is the code behind:

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        FirmsTableAdapter InsertFirmAdapter = new FirmsTableAdapter();
        InsertFirmAdapter.InsertQuery(
            txtFirmName.Text, 
            txtAddress1.Text, 
            txtCity.Text, 
            ddStates.SelectedValue, 
            txtZip.Text, 
            txtPhone.Text, 
            txtEmail.Text, 
            txtWebsite.Text, 
            Convert.ToDateTime(txtEffDate.Text), 
            ddLimits.SelectedValue, 
            txtLimits1.Text, 
            txtLimits2.Text, 
            ddDeductible.SelectedValue, 
            Convert.ToDateTime(txtRetroDate.Text), 
            cblInsurance.Text, 
            txtCurrCarrier.Text, 
            txtCurrPrem.Text, 
            Convert.ToDateTime(txtEstDate.Text), 
            Convert.ToInt32(txtNumParalegals.Text), 
            Convert.ToInt32(txtNumClerical.Text), 
            cblEngageLetters.Text,
            cblConflictSystem.Text, 
            cblDocketSystem.Text, 
            cblSueFees.Text, 
            Convert.ToInt32(txtTotalAttys.Text), 
            Convert.ToInt32(txtClaimsQuantity.Text), 
            cblComplaint.Text, 
            cblRefusedAdmission.Text, 
            txtSignature.Text);
    }

Before I switched to using TableAdapters I was using SQL commands and this code worked, so I'm surprised the TableAdapter calls are not:

        string CS = ConfigurationManager.ConnectionStrings["EstimateDB"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("InsertQuery", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@firmName", txtFirmName.Text);
            cmd.Parameters.AddWithValue("@address", txtAddress1.Text);
            cmd.Parameters.AddWithValue("@city", txtCity.Text);
            cmd.Parameters.AddWithValue("@state", ddStates.SelectedValue);
            cmd.Parameters.AddWithValue("@zipcode", txtZip.Text);
            cmd.Parameters.AddWithValue("@phoneNumber", txtPhone.Text);
            cmd.Parameters.AddWithValue("@emailAddress", txtEmail.Text);
            cmd.Parameters.AddWithValue("@website", txtWebsite.Text);
            cmd.Parameters.Add("@desiredEffDate", SqlDbType.DateTime).Value = txtEffDate.Text;
            cmd.Parameters.AddWithValue("@desiredLimits", ddLimits.SelectedValue);
            cmd.Parameters.AddWithValue("@desiredLimitsOther1", txtLimits1.Text);
            cmd.Parameters.AddWithValue("@desiredLimitsOther2", txtLimits2.Text);
            cmd.Parameters.AddWithValue("@desiredDeductible", ddDeductible.SelectedValue);
            cmd.Parameters.Add("@retroDate", SqlDbType.DateTime).Value = txtRetroDate.Text;
            cmd.Parameters.AddWithValue("@insurance", cblInsurance.Text);
            cmd.Parameters.AddWithValue("@currentCarrier", txtCurrCarrier.Text);
            cmd.Parameters.AddWithValue("@currentPremium", txtCurrPrem.Text);
            cmd.Parameters.Add("@firmEstablished", SqlDbType.DateTime).Value = txtEstDate.Text;
            cmd.Parameters.AddWithValue("@numParalegals", txtNumParalegals.Text);
            cmd.Parameters.AddWithValue("@numStaff", txtNumClerical.Text);
            cmd.Parameters.AddWithValue("@engageLetters", cblEngageLetters.Text);
            cmd.Parameters.AddWithValue("@conflictSystem", cblConflictSystem.Text);
            cmd.Parameters.AddWithValue("@docketSystem", cblDocketSystem.Text);
            cmd.Parameters.AddWithValue("@sueFees", cblSueFees.Text);
            cmd.Parameters.AddWithValue("@numAttorneys", txtTotalAttys.Text);
            cmd.Parameters.AddWithValue("@numClaims", txtClaimsQuantity.Text);
            cmd.Parameters.AddWithValue("@complaint", cblComplaint.Text);
            cmd.Parameters.AddWithValue("@refusedAdmission", cblRefusedAdmission.Text);
            cmd.Parameters.AddWithValue("@signature", txtSignature.Text);

            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }

Answer 1

It's not about formatting strings before passing them to InsertFirmAdapter.InsertQuery. You actually need proper objects, as some of the params in stored procedure are declared as integers and datetime. So the error is thrown by one of the Convert.To calls. You can trace it by logging or debugging. What are the actual values before the call?

EDIT
You can write own methods to verify user input. Normally you would do that at a higher level than right be fore executing query, to be able to notify user about invalid data provided. This an example for converting string to int, you can infer string to DateTime conversion from it.

int ParseInt32WithDefault(string value, int defaultValue) {
    int number;
    if (Int32.TryParse(value, out number))
        return number;
    return defaultValue;
}

And call it instead of Convert.ToInt32 ex.: ParseInt32WithDefault(txtNumParalegals.Text, 0).
However, this doesn't solve the problem but rather masks it. You need to see a list of values of all variables that you do Convert.To...