C# TimeSpan passing to SQL 2008 Time?

  • Thread starter Thread starter nomad
  • Start date Start date
N

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();

}
 
nomad said:
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.
Click to expand...
Stored Proc
@ID int,
@Title varchar(100),
@Length Time,
@IMDB varchar(200),
@Genre varchar(50)
Click to expand...
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();
Click to expand...

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.
 
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.
Click to expand...

Hi,

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