MsSql server:running stored procedure

Question

I have a table

CREATE TABLE [dbo].[DealerInfo](
    [DealerName] [nvarchar](100) NULL,
    [Address] [nvarchar](100) NULL,
    [City] [nvarchar](100) NULL,
    [County] [nvarchar](100) NULL,
    [Fax] [nvarchar](50) NULL,
    [CompanyWebSite] [nvarchar](max) NULL,
    [EmailAddress] [nvarchar](100) NULL,
    [Currency] [nvarchar](20) NULL,
    [LicenceID] [int] NULL,
    [TaxRegistration] [int] NULL,
    [Phone] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And a problem, when i try to insert a currency value it reports that it need an integer value

i insert values with a stored procedure

ALTER PROCEDURE [dbo].[DealerInformation]
    -- Add the parameters for the stored procedure here
    @DealerName nvarchar(100),
    @Address nvarchar(100),
    @City nvarchar (100),
    @County nvarchar (100), 
    @Phone nvarchar (100),
    @Fax nvarchar(50),
    @CompanyWebSite nvarchar (100),
    @EmailAddress nvarchar(Max),
    @Currency nvarchar(20),
    @LicenceID int,
    @TaxRegistration int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    Insert into DealerInfo values (@DealerName, @Address, @City, @County, @Phone, @Fax, @CompanyWebSite,@EmailAddress,@Currency,@LicenceID,@TaxRegistration)

The Currency is supposed to be a string value like USD, CAD, EUR etc

When i execute the stored procedure this is the error i get

Msg 245, Level 16, State 1, Procedure DealerInformation, Line 26 [Batch Start Line 2]
Conversion failed when converting the nvarchar value 'USD' to data type int.


using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=CarDealership;Integrated Security=True") )
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("DealerInformation", conn);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("DealerName", dealership.DealershipName);
                cmd.Parameters.AddWithValue("Address", dealership.Address);
                cmd.Parameters.AddWithValue("City", dealership.City);
                cmd.Parameters.AddWithValue("County", dealership.Country);
                cmd.Parameters.AddWithValue("Phone", dealership.Telephone);
                cmd.Parameters.AddWithValue("Fax", dealership.Fax);
                cmd.Parameters.AddWithValue("CompanyWebSite", dealership.CompanyWebSite);
                cmd.Parameters.AddWithValue("EmailAddress", dealership.Email);
                cmd.Parameters.AddWithValue("Currency", dealership.Currency); //The currency is string type
                cmd.Parameters.AddWithValue("LicenceID", dealership.LicenceID);
                cmd.Parameters.AddWithValue("TaxRegistration", dealership.TaxRegistration);

                int RowsAffected = cmd.ExecuteNonQuery();
                return RowsAffected;

            }

Or you're passing a parameter value 'USD' to a column that is of type int. Can you take a look at the columns of the table and see if the insert statement has the same (number of) columns in the same order as you're using in the insert statement? — rickvdbosch, Jun 26 '17 at 12:01

Steve · Accepted Answer · 2017-06-26T12:19:22.257

You are using an INSERT INTO statement without specifying the fields names.
Thus your parameters should be listed exactly in the same order of your fields names inside the table

As it is now, the database engine is setting the fields with these parameters

[DealerName]  = @DealerName, 
[Address] = @Address
[City] = @City
[County] = @County
[Fax] = @Phone 
[CompanyWebSite] = @Fax 
[EmailAddress] = @CompanyWebSite
[Currency] = @EmailAddress,
[LicenceID] = @Currency,
[TaxRegistration]  = @LicenceID
[Phone] = @TaxRegistration

As you can see, your LicenceID field (an int) receives the value of the @Currency parameter (a nvarchar).
This is the cause of the error and the fix is to always specify the field names in the INSERT INTO statement and list the parameters in the correct order to set the associated fields.

Insert into DealerInfo 
   (DealerName, Address, City, County, Fax, CompanyWebSite,
   EmailAddress,Currency,LicenceID,TaxRegistration,Phone)
values (@DealerName, @Address, @City, @County, @Fax, @CompanyWebSite,
   @EmailAddress,@Currency,@LicenceID,@TaxRegistration, @Phone)

Yes, it looks like i added Phone at the end of the table definition. I'll fix it that and see if it works — Michael27, Jun 26 '17 at 12:06
Yes it did work, i've recreated the table definition and now it works. Now phone is after fax Thanks Steve Dumb mistake really :) — Michael27, Jun 26 '17 at 12:12

score 0 · Answer 2 · answered Jun 26 '17 at 12:13

0

To me, it seems like your table definition and stored procedure code is not in sync. That is your table definition column data type for currency is actually int, but somehow you have changed it in the editor to nvarchar.

To resolve the issue, right click on your table and check the definition of you table (not the code you have in editor window), You will get your answer.

I hope, it will help you. Thanks

answered Jun 26 '17 at 12:13

vivek

1,493
2
18
32

Yes, Steve's solution helped me :) – Michael27 Jun 26 '17 at 12:13
it's ok. Happy coding – vivek Jun 26 '17 at 12:14

MsSql server:running stored procedure

2 Answers2