CODEWITHSUNDEEP
c#web-servicesstored-procedures
The Muffin Man
The Muffin Man

Reputation: 20004

why is my web service/stored procedure truncating my insert values to one char?

I'm using jquery ajax to send values to my .net web service which in turn sends those values to a stored procedure. The problem i'm having is that no matter what values I send to the web service, they get inserted into my DB as one char. e.g. "test" becomes "t".

None of my receiving tables columns have a max value set of 1. Using firebug for Firefox I have confirmed that my ajax method is indeed posting the intended values to the web service.

Web Service:

 [WebMethod]
    public void InsertAllVehicles(string Make, string Model, string SubModel, decimal Liter, string Cylinder,string Fuel, string FuelDel, string Asp, string EngDesg)
    {

        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString))
        {
            using (SqlCommand comm = new SqlCommand())
            {
                conn.Open();
                comm.Connection = conn;
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = "Vehicle_InsertAll";
                if (Make.Length >=1)
                    comm.Parameters.AddWithValue("@Make", Make);
                if (Model.Length >=1)
                comm.Parameters.AddWithValue("@Model", Model);
                if (SubModel.Length >=1)
                comm.Parameters.AddWithValue("@SubModel", SubModel);
                if (Liter != 91)
                    comm.Parameters.AddWithValue("@Liter", Liter);
                if (Cylinder.Length >= 1)
                comm.Parameters.AddWithValue("@Cylinder", Cylinder);
                if (Fuel.Length >= 1)
                comm.Parameters.AddWithValue("@Fuel", Fuel);
                if (FuelDel.Length >= 1)
                comm.Parameters.AddWithValue("@FuelDel", FuelDel);
                if (Asp.Length >= 1)
                comm.Parameters.AddWithValue("@Asp", Asp);
                if (EngDesg.Length != 0)
                    comm.Parameters.AddWithValue("@EngDesg", EngDesg);
                comm.ExecuteNonQuery();
                conn.Close();

            }
        }

    }

Stored Procedure

ALTER PROCEDURE dbo.Vehicle_InsertAll
    @Make  varchar = null,
    @Model  varchar = null,
    @SubModel varchar = null,
    @Liter decimal = null,
    @Cylinder varchar = null,
    @Fuel varchar = null,
    @FuelDel varchar = null,
    @Asp varchar = null,
    @EngDesg varchar = null
AS
IF @Make IS NOT NULL
BEGIN
    INSERT INTO VehicleMakes (VehicleMake) VALUES(@Make)
    END
    IF @Model IS NOT NULL
    BEGIN
    INSERT INTO VehicleModels (Name) VALUES(@Model)
    END
    IF @SubModel IS NOT NULL
    BEGIN
    INSERT INTO VehicleSubModels (Name) VALUES(@SubModel)
    END
    IF @Liter IS NOT NULL
    BEGIN
    INSERT INTO VehicleLiters (Liters) VALUES(@Liter)
    END
    IF @Cylinder IS NOT NULL
    BEGIN
    INSERT INTO VehicleCylinders (Cylinders) VALUES(@Cylinder)
    END
    IF @Fuel IS NOT NULL
    BEGIN
    INSERT INTO VehicleFuel (FuelType) VALUES (@Fuel)
    END
    IF @FuelDel IS NOT NULL
    BEGIN
    INSERT INTO VehicleFuelDelivery (Delivery) VALUES (@FuelDel)
    END
    IF @Asp IS NOT NULL
    BEGIN
    INSERT INTO VehicleAspiration (Asp) VALUES (@Asp)
    END
    IF @EngDesg IS NOT NULL
    BEGIN
    INSERT INTO VehicleEngDesg (Designation) VALUES (@EngDesg)
    END

    RETURN

Upvotes: 0

Views: 710

Answers (3)

Eric K Yung
Eric K Yung

Reputation: 1784

The parameters of your stored procedure, particularly those with type varchar should be declared with lengths that match their columns defined in the table. For example: @Make varchar(50)=null.

Upvotes: 2

SteveCav
SteveCav

Reputation: 6729

specify the varchar length of each field in your stored proc, eg @Make varchar(50) = null

Upvotes: 1

jcolebrand
jcolebrand

Reputation: 16025

Because VARCHAR is one character. Set it to VARCHAR(MAX) till you know what size you really want, replacing MAX with the size you need.

Upvotes: 1

Related Questions