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;

            }

Answer 1

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)