CODEWITHSUNDEEP
c#sqlsql-server
Matthew
Matthew

Reputation: 67

SQL Command Parameter cannot covert string to int

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?

Upvotes: 0

Views: 702

Answers (2)

gmiley
gmiley

Reputation: 6604

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;

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1063338

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
    }));

Upvotes: 1

Related Questions