SQL Command Parameter cannot covert string to int

Question

I have a SQL command to add data from a collection to my SQL database. Everything seems to be working fine except when I try to pass non number characters into my Description field on the DB table. Seems like it's not taking string values.

My Collection class

    public class LotData
    {
        public string Lot;
        public string Description { get; set; }
        public int PO { get; set; }
        public string MfgPart { get; set; }
    }

My SQL Command

          private const string strConn = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\Matthew\\QCast.mdf;Integrated Security=True;Connect Timeout=30";


    private void Button_Click_1(object sender, RoutedEventArgs e)
    //send data from DataGrid to database
    {
        using (var conn = new SqlConnection(strConn))
        {
            conn.Open();

            try
            {
                foreach (var lotData in lot)
                {
                    using (var command = new SqlCommand("INSERT into LotData Values (@LOTnum, @WOnum, @POnum, @Description, @MFGpartNO)", conn))
                    {

                        command.Parameters.AddWithValue("LOTnum", lotData.Lot);
                        command.Parameters.AddWithValue("WOnum", "50-50-100");
                        command.Parameters.AddWithValue("POnum", lotData.PO);
                        command.Parameters.Add("Description",SqlDbType.Text , lotData.Description);
                        command.Parameters.AddWithValue("MFGpartNO", lotData.MfgPart);

                        command.ExecuteNonQuery();
                    }
                }
            }

            finally
            {
                conn.Close();
            }
        }

I have an issue with this line -

command.Parameters.Add("Description",SqlDbType.Text , lotData.Description);

The error I get 'cannot covert from stirng to int'. I don't want an int I want a string

I have also tried:

   command.Parameters.AddWithValue("Description",lotData.Description);

Then I get the runtime exception - Operand type clash: int is incompatible with text'

My SQL table setup is this:

CREATE TABLE [dbo].[LotData] (
    [TransID]     INT           IDENTITY (1, 1) NOT NULL,
    [WOnum]       NCHAR (10)    NULL,
    [LOTnum]      NVARCHAR (50) NULL,
    [Description] TEXT          NULL,
    [POnum]       INT           NULL,
    [MFGpartNO]   NCHAR (10)    NULL,
    CONSTRAINT [PK_LotData] PRIMARY KEY CLUSTERED ([TransID] ASC)

How do I resolve this?

If you insert then always use the column names: insert into t (col1, col2) values ('x1', 'x2') — juergen d
– juergen d, Commented Oct 17, 2019 at 11:26
Specifying your column names in your INSERT statement would be a good start. i.e. INSERT into LotData ([LOTnum], [WOnum], [POnum], [Description], [MFGpartNO]) Values (@LOTnum, @WOnum, @POnum, @Description, @MFGpartNO) — Diado
– Diado, Commented Oct 17, 2019 at 11:26
Would that solve my problem? The data does post to the DB if I use numbers only — Matthew
– Matthew, Commented Oct 17, 2019 at 11:27
The error doesnt entirely tie up with your code, however the order of your fields in the code is lotnum, wonum, ponum, description, mfgparno, in your db you have id, wonum, lotnum, description, ponum mfgpartno therefore it seems things are in a different order in your sql than they are, so you possibly have incorrect data in fields too. — BugFinder
– BugFinder, Commented Oct 17, 2019 at 11:58

gmiley · Accepted Answer · 2019-10-17 11:41:12Z

2

Your issue is not the contents of the Description property, but rather you are trying to pass a String type to an Int32 parameter: see the MSDN page for SqlParameterCollection.Add. You will see that the way you are calling it only works when you provide a number because a String representation of an Int32 will implicitly convert from String to Int32. You should be specifying the column width in that parameter, then add the value by setting the property after the parameter is initialized.

SqlParameter parm = command.Parameters.Add("Description", SqlDbType.Text, 500);
parm.Value = lotData.Description;

edited Oct 17, 2019 at 11:41

answered Oct 17, 2019 at 11:36

gmiley

6,6041 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2019-10-17 11:27:47Z

There's a few things to unpack here. Firstly, you should always specify the columns when using INSERT so that it knows which parameter goes where, i.e.

INSERT into LotData (LOTnum, WOnum, POnum, Description, MFGpartNO)
Values (@LOTnum, @WOnum, @POnum, @Description, @MFGpartNO);

If that doesn't fix it, then yes, if a parameter is meant to be an int, but is a string: you need to use int.Parse yourself. I suspect the above will work, though.

Finally; you might find it easier to use a tool like "Dapper" here:

conn.Execute(@"INSERT into LotData (LOTnum, WOnum, POnum, Description, MFGpartNO)
Values (@Lot, @WOnum, @PO, @Description, @MfgPart)",
    lot.Select(lotData => new { // args, invoked per item
        lotData.Lot,
        WOnum = "50-50-100",
        lotData.PO,
        lotData.Description,
        lotData.MfgPart
    }));

Collectives™ on Stack Overflow

SQL Command Parameter cannot covert string to int

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related