Failed to convert parameter value from a String to a Int32 for DateTime

Question

I have a C# form linked to an SQL database, and for one of the columns I wish to insert the current date each time a new row is added. I am baffled because at one point this code was doing exactly what I wanted, and then after changing a few things it now keeps throwing the error "Failed to convert parameter value from a String to a Int32." and I don't know what happened. Here is my table definition. I know the value types are probably a bit arbitrary, but all I care about is that the date is stored as a string.

CREATE TABLE [dbo].[InvTable] (
[ID]       INT           IDENTITY (1, 1) NOT NULL,
[Item]     NVARCHAR (50) NULL,
[QuantRem] INT           NULL,
[Type]     NCHAR (10)    NULL,
[DateOrd]  NCHAR(10)    NULL,
[QuantOrd] INT           NULL,
[Received] BIT           NULL,
[DateRec]  NCHAR(10)    NULL,
PRIMARY KEY CLUSTERED ([ID] ASC)
);

And the code on the form is below. The second date will be defined differently later, but my primary concern is getting rid of the error in the title.

    private void Submitbutton1_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrWhiteSpace(textBox1.Text))
        {
            MessageBox.Show("Cannot Submit Empty Request");
            return;
        }
        if (comboBox1.SelectedItem == null)
        {
            MessageBox.Show("Please Use Drop Down Menu To Select Item Category");
            return;
        }
        SqlDataAdapter da = new SqlDataAdapter();
        da.InsertCommand = new SqlCommand("INSERT INTO InvTable VALUES(@Item, @QuantRem, @Type, @DateReq, @QuantOrd, @Received, @DateRec)", con);
        da.InsertCommand.Parameters.Add("@Item", SqlDbType.NChar).Value = textBox1.Text;
        da.InsertCommand.Parameters.Add("@QuantRem", SqlDbType.Int).Value = textBox2.Text;
        da.InsertCommand.Parameters.Add("@Type", SqlDbType.NChar).Value = comboBox1.Text;
        da.InsertCommand.Parameters.Add("@DateReq", SqlDbType.NChar).Value = DateTime.Today.ToString("d");
        da.InsertCommand.Parameters.Add("@QuantOrd", SqlDbType.Int).Value = 0;
        da.InsertCommand.Parameters.Add("@Received", SqlDbType.Bit).Value = 0;
        da.InsertCommand.Parameters.Add("@DateRec", SqlDbType.NChar).Value = DateTime.Today.ToString("d");
        con.Open();
        da.InsertCommand.ExecuteNonQuery();
        da.SelectCommand = new SqlCommand("SELECT * FROM InvTable WHERE Received=0", con);
        con.Close();
        ds.Clear();
        da.Fill(ds);
        invTableDataGridView.DataSource = ds.Tables[0];
        textBox1.Clear();
    }

So this code used to work at one point, giving me a string "07/20/2015", but now it just gives me an error.

Why are you using text to represent a date at all? You should use a `Date` field in the database if you *possibly* can. As for the problem in your question - I suggest you explicitly do the parsing in your code, e.g. `int.Parse(textBox2.Text)`. I *suspect* that's where the problem is, but you should be able to work that out. — Jon Skeet, Jul 20 '15 at 10:44
Are you sure it's not the QuantRem **int** value being converted from textBox2. **text** that's the issue? The date **string** isn't undergoing any conversion at any point — James, Jul 20 '15 at 10:47
It was just for the purpose of logging when it was done... no real data function. Would using the Date field fix the problem? — anesthetic, Jul 20 '15 at 10:47
i think field is wrong typed in your code [DateOrd] as ("@DateReq", — DeshDeep Singh, Jul 20 '15 at 10:52

score 3 · Accepted Answer · edited Jul 29 '20 at 18:28

You are a victim of choosing the wrong data type.

Change your DateOrd and DateRec column types to date type and pass your DateTime.Today directly to your parameterized query (since you keep only date part of a DateTime).

[DateOrd]  date    NULL,
...
...
[DateRec]  date   NULL,

and

da.InsertCommand.Parameters.Add("@DateReq", SqlDbType.Date).Value = DateTime.Today;
da.InsertCommand.Parameters.Add("@DateRec", SqlDbType.Date).Value = DateTime.Today;

Also for QuantRem column, looks like you need to parse textBox2.Text value to int before you pass it as a parameter.

da.InsertCommand.Parameters.Add("@QuantRem", SqlDbType.Int).Value = int.Parse(textBox2.Text);

Don't forget to use using statement to dispose your connections, commands and adapters automatically instead of calling Close methods manually.

QuantRem was definitely the problem. Thanks! +1 – anesthetic Jul 20 '15 at 10:53 — anesthetic, Jul 20 '15 at 10:53

score 1 · Answer 2 · answered Jul 20 '15 at 10:52

1

As others have said, don't use text when you could/should be using an actual date type ... it will save you a lot of hassle now and in the future.

But your actual error is more likely to be this line: da.InsertCommand.Parameters.Add("@QuantRem", SqlDbType.Int).Value = textBox2.Text;

Where you are taking text and trying to set an integer parameter. Convert that text to an integer, and then set your integer parameter.

answered Jul 20 '15 at 10:52

Mashton

6,037
2
25
35

Yup QuantRem was the problem. +1 – anesthetic Jul 20 '15 at 10:53

Failed to convert parameter value from a String to a Int32 for DateTime

2 Answers2