How to Pass TimeSpan Variable to Data base

Question

I have the code Like this:

Dim starttime As TimeSpan = fromtimepicker.Value.TimeOfDay
Dim endtime As TimeSpan = TotimePicker.Value.TimeOfDay

and i try to update my table like this:

Dim sqlcmd As String = "update Location_tbl set StartTime='" & starttime & "' ,EndTime='" & endtime & "'

but error coming like this:

operator & is not defined for types 'string' and system.timespan

how i can resolve this issue

What is the data type of `StartTime` column in your table? Also it is much better to use [SqlParameter](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx) — huMpty duMpty, Jul 16 '13 at 10:38
@huMptyduMpty The exact datatype is irrelevant to the error. — Zev Spitz, Jul 16 '13 at 10:43
Apart from the OP's issue about the & operator, there are real issues about mapping TimeSpan client-side CLR object to a counterpart in SQL Server (or other database) that the OP might not know about yet. Until SQL Server implements a TimeSpan datatype, Time is the recommended kludge when choosing the SQL Server type, or sometimes integer with some conversions client-side. — Tim, Jul 16 '13 at 10:44
@huMptyduMpty If he's using a different database, he'll have to use the corresponding parameter class -- [OdbcParameter](http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparameter.aspx), [OleDbParameter](http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx) etc. — Zev Spitz, Jul 16 '13 at 10:45
@ZevSpitz: Yes data type is irrelevant to the error. But it helps to give a proper answer (to do the [TimeSpan.ToString](http://msdn.microsoft.com/en-us/library/dd992632.aspx)) — huMpty duMpty, Jul 16 '13 at 11:04

score 0 · Answer 1 · answered Jul 16 '13 at 10:35

0

Use

 starttime.ToString(...)

where ... is the format string (http://msdn.microsoft.com/en-us/library/ee372287.aspx) - it depends on which database you are using. Sometimes you need to use the database function to convert.

answered Jul 16 '13 at 10:35

eMko

1,147
9
22

score 0 · Answer 2 · answered Jul 16 '13 at 10:36

This error has nothing to do with a database. You're trying to combine a TimeSpan variable with a string variable. You have to tell .NET how the timespan should be represented as a string, either using ToString or String.Format.

Once you have your SQL statement as a string, then you can execute the statement.

You might want to consider using parameters instead of concatenating the string into a statement.

score 0 · Answer 3 · edited May 23 '17 at 11:49

Firstly you should use a parameterised query - this is good practice because it will make your SQL statement easier to read and you don't have to convert every value to a string. It will also help to avoid a possible SQL injection attack on any string inputs you may have.

Secondly how to find a solution depends on what data type these columns are in your database. If they are DateTime or Time then you could potentially use the value as is.

Dim sql As String = "update Location_tbl set StartTime= @starttime, EndTime= @endtime"

Using cn As New SqlConnection("Your connection string here"), _
    cmd As New SqlCommand(sql, cn)

    cmd.Parameters.Add("@starttime", SqlDbTypes.DateTime).Value = fromtimepicker.Value.TimeOfDay
    cmd.Parameters.Add("@endtime", SqlDbTypes.DateTime).Value = TotimePicker.Value.TimeOfDay
    Return cmd.ExecuteScalar()
End Using

If they are string types then just change the data type to something such as SqlDbTypes.VarChar and pass the values with .ToString on the end

Side Note; I think you are missing a WHERE clause from the end of your sql statement (unless there is only every going to be one row in this table?)

SQL injection is not the issue here. The OP is starting off from a `TimeSpan`, for which no string formatting is going to contain SQL statements (unless he does `startTime.ToString("{0};DROP DATABASE dbname;--")`. There are still valid reasons to use parameters: 1) to avoid formatting conflicts between the local machine and the server -- mm/dd vs. dd/mm for one, and 2) efficiency, as the server doesn't have to recalculate the execution plan each time. — Zev Spitz, Jul 16 '13 at 11:11
@ZevSpitz - agreed although this is good practice. I have updated my answer — Matt Wilko, Jul 16 '13 at 11:15

How to Pass TimeSpan Variable to Data base

3 Answers3