After inserting, only one character is inserted of "string" in database

Question

Below is my table screenshot:

Data after insertion

Code in C#

SqlConnection con = new SqlConnection(connectionsession.Con);
con.Open();
SqlCommand cmd = new SqlCommand("finalinsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@fid", SqlDbType.Int)).Value = flightsession.Sflightid;
cmd.Parameters.Add(new SqlParameter("@fcid", SqlDbType.Int)).Value = flightsession.Sflightcostid;
cmd.Parameters.Add(new SqlParameter("@ftid", SqlDbType.Int)).Value = flightsession.Sflighttimingid;
cmd.Parameters.Add(new SqlParameter("@frdn", SqlDbType.Date)).Value = DateTime.Now.ToString("dd-MM-yyyy");
cmd.Parameters.Add(new SqlParameter("@ford", SqlDbType.Date)).Value = flightsession.Regdate;
cmd.Parameters.Add(new SqlParameter("@uid", SqlDbType.Int)).Value = loginsession.Auser;
cmd.Parameters.Add(new SqlParameter("@paid", SqlDbType.Int)).Value = "1";
cmd.Parameters.Add(new SqlParameter("@source", SqlDbType.VarChar)).Value = flightsession.Sscource;
cmd.Parameters.Add(new SqlParameter("@destination", SqlDbType.VarChar)).Value = flightsession.Sdestination;
cmd.Parameters.Add(new SqlParameter("@fn", SqlDbType.VarChar)).Value = flightsession.Sflightname;
cmd.Parameters.Add(new SqlParameter("@fc", SqlDbType.VarChar)).Value = flightsession.Total;
cmd.Parameters.Add(new SqlParameter("@fty", SqlDbType.VarChar)).Value = flightsession.Stype;
cmd.Parameters.Add(new SqlParameter("@fcl", SqlDbType.VarChar)).Value = flightsession.Sflightclass;
cmd.Parameters.Add(new SqlParameter("@ft", SqlDbType.Time)).Value =flightsession.Sflighttime;
cmd.ExecuteNonQuery();
MessageBox.Show("Succesful");
con.Close();
flightreceipt ob18 = new flightreceipt();
ob18.ShowDialog();
this.Hide();

Code for creating procedure in SQL server 2008

ALTER procedure [dbo].[finalinsert] @fid int,@fcid int,@ftid int,@frdn date,@ford date,@uid int,
@paid int,@source varchar,@destination varchar,@fc varchar,@fty varchar,@fcl varchar,@ft time(7) ,@fn varchar
AS
INSERT INTO [shangrila].[dbo].[flight_reg_table]
           ([flight_id]
           ,[flight_cost_id]
           ,[flight_time_id]
           ,[flight_reg_date_now]
           ,[flight_on_reg_date]
           ,[user_id]
           ,[paid]
           ,[source]
           ,[destination]
           ,[flight_name]
           ,[flight_cost]
           ,[flight_type]
           ,[flight_class]
           ,[flight_time])
     VALUES
           (@fid,
           @fcid,
           @ftid,
           @frdn,
           @ford,
           @uid,
           @paid,
           @source,
           @destination,
           @fn,
           @fc,
           @fty,
           @fcl,
           @ft)

I tried to figure out why! But i am stuck. Remember all format which I am inserting is string. Thanks.

Answer 1

In your sproc your VARCHAR needs to have a length associated with it, such as VARCHAR(50). The default length is 1, so SQL is only sending one character to your sproc.

ALTER procedure [dbo].[finalinsert]
     @fid int
    ,@fcid int
    ,@ftid int
    ,@frdn date 
    ,@ford date
    ,@uid int
    ,@paid int
    ,@source varchar(50)
    ,@destination varchar(50)
    ,@fc varchar(50)
    ,@fty varchar(50)
    ,@fcl varchar(50)
    ,@ft time(7)
    ,@fn varchar(50)
AS

You may also need to change the datatype in your call as well to include the length, but ADO.NET might handle it correctly.

Answer 2

The root cause is mentioned by @Talljoe, as well as by @Jethro.

Anyway, your C# code will be rather better if will look like this:

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand cmd = connection.CreateCommand())
{
    cmd.CommandText = commandName;
    cmd.CommandType = CommandType.StoredProcedure;

    ...
    cmd.Parameters.Add("@source", SqlDbType.VarChar, 50).Value = flightsession.Sscource;
    cmd.Parameters.Add("@destination", SqlDbType.VarChar, 50).Value = flightsession.Sdestination;
    cmd.Parameters.Add("@fn", SqlDbType.VarChar, 40).Value = flightsession.Sflightname;
    cmd.Parameters.Add("@fc", SqlDbType.VarChar, 50).Value = flightsession.Total;
    cmd.Parameters.Add("@fty", SqlDbType.VarChar, 50).Value = flightsession.Stype;
    cmd.Parameters.Add("@fcl", SqlDbType.VarChar, 50).Value = flightsession.Sflightclass;

    connection.Open(); // open just now!
    cmd.ExecuteNonQuery();
} // using block call Dispose() for each object locker to guarantee close of the connection

Read more about SqlConnection.Dispose() and using(){} block.