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

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.

Answers


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.


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.


Your stored procedure varchar needs to change to show the length of the field. Ie @fn varchar(50).


Along with the data type of the variables, you should mention the size of the data item. Ex: varchar(50)


Need Your Help

What IO activity does the GHC IO manager support?

haskell ghc

I've been reading about the new IO manager in GHC, which uses asynchronous event notifications and eschews blocking I/O to achieve high throughput.

CarrierWave and resize to a specific width

ruby-on-rails carrierwave

Using CarrierWave in Rails 3.0 how would you go about making the code