C# TimeSpan passing to SQL 2008 Time?

[nomad]

Hi,

I have an update stored procedure which has a Time parameter. In C# I
am passing through a TimeSpan value but I keep getting "Failed to
convert parameter value from a String to a TimeSpan" error. If anyone
could help with this it would be greatly appreciated.

Stored Proc
@ID int,
@Title varchar(100),
@Length Time,
@IMDB varchar(200),
@Genre varchar(50)

AS
BEGIN

SET NOCOUNT ON;

UPDATE tb_collection
SET Title = @Title,
Length = @Length,
IMDB = @IMDB,
@Genre = @Genre
WHERE ID = @ID
END


C# Code
private void btnUpdate_Click(object sender, RoutedEventArgs e)
{
TimeSpan time;
time = TimeSpan.Parse(txtLength.Text);
conn.ConnectionString = conString;
command.CommandText = "sp_update_dvd";
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@ID",
SqlDbType.Int, 0, "ID"));
command.Parameters.Add(new SqlParameter("@Title",
SqlDbType.VarChar, 100, "Title"));
command.Parameters.Add(new SqlParameter("@Length",
SqlDbType.Time, 0, "Length"));
command.Parameters.Add(new SqlParameter("@IMDB",
SqlDbType.VarChar, 200, "IMDB"));
command.Parameters.Add(new SqlParameter("@Genre",
SqlDbType.VarChar, 50, "Genre"));
command.Parameters[0].Value = lblID.Content;
command.Parameters[1].Value = txtTitle.Text;
command.Parameters[2].Value = TimeSpan.Parse
(txtLength.Text);
command.Parameters[3].Value = txtIMDB.Text;
command.Parameters[4].Value = cmbGenre.Text;
command.ExecuteNonQuery();
conn.Close();

}

 

[Arne Vajhøj]

I have an update stored procedure which has a Time parameter. In C# I
am passing through a TimeSpan value but I keep getting "Failed to
convert parameter value from a String to a TimeSpan" error. If anyone
could help with this it would be greatly appreciated.

Stored Proc
@ID int,
@Title varchar(100),
@Length Time,
@IMDB varchar(200),
@Genre varchar(50)

command.CommandText = "sp_update_dvd";
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@ID",
SqlDbType.Int, 0, "ID"));
command.Parameters.Add(new SqlParameter("@Title",
SqlDbType.VarChar, 100, "Title"));
command.Parameters.Add(new SqlParameter("@Length",
SqlDbType.Time, 0, "Length"));
command.Parameters.Add(new SqlParameter("@IMDB",
SqlDbType.VarChar, 200, "IMDB"));
command.Parameters.Add(new SqlParameter("@Genre",
SqlDbType.VarChar, 50, "Genre"));
command.Parameters[0].Value = lblID.Content;
command.Parameters[1].Value = txtTitle.Text;
command.Parameters[2].Value = TimeSpan.Parse
(txtLength.Text);
command.Parameters[3].Value = txtIMDB.Text;
command.Parameters[4].Value = cmbGenre.Text;
command.ExecuteNonQuery();

I believe TIME is used for time part of DATETIME not for a
delta time.

I can see two solutions:

1) Change the C# type to DateTime.
2) Change the SQLServer type to INT and save in seonds.

Arne

PS: Setting parameters by name instead of by index may increase
readability.

 

[Ignacio Machin ( .NET/ C# MVP )]

Hi,

I have an update stored procedure which has a Time parameter. In C# I
am passing through a TimeSpan value but I keep getting "Failed to
convert parameter value from a String to a TimeSpan" error. If anyone
could help with this it would be greatly appreciated.

Hi,

A TimeSpan is not more than a numeric value (a long IIRC). You need
to change your DB.