Member Avatar for poojavb

Hello Friends,

I need one urgent help on this topic.

I have a database in mysql.
The column in table is of datatype time(0)

Similarly I have a control datetimepicker in vb.net
I have assigned format property as custom to the control and the CustomFormat property as HH:mm:ss tt so that it can just provide the time.
I dont want the date to be in it.

Issue comes when I try to retrieve the value from database
I get an error as Converion of type 'Timespan' to type 'Date' is not valid

Can anyone help me in this?

Try
   Dim myCommand As SqlCommand
   myCommand = New SqlCommand("select * from [HMS].[dbo].[DoctorSchedule] where DoctorID='" & txtDoctorID.Text & "'", Connection)
   Dim reader As SqlDataReader = myCommand.ExecuteReader
       While reader.Read
           TextBox1.Text = reader("ScheduledID")
           dtpMonST.Value = reader("MonST")
       End While

Catch ex As Exception
   MessageBox.Show("Error Connecting to Database: " & ex.Message)
End Try
  • 3 Contributors
  • 5 Replies
  • 615 Views
  • 4 Weeks Discussion Span
  • Latest Post Latest Post by poojavb

Dani AI

Generated

The error happens because a DB TIME column comes back to .NET as a TimeSpan (a duration), while a DateTimePicker.Value is a DateTime. You cannot assign a TimeSpan directly to Value, so you need to convert the TimeSpan to a DateTime (commonly by adding it to a date like DateTime.Today). Also check whether you're actually using the correct ADO.NET provider: your post mentions MySQL but the code uses SqlClient — use the matching client (MySql.Data.MySqlClient for MySQL, SqlClient for SQL Server). was right to check the field type; a TIME field is the reason you get a TimeSpan back.

A minimal, safe read-and-assign pattern:

Dim ord As Integer = reader.GetOrdinal("MonST")
If Not reader.IsDBNull(ord) Then
    Dim ts As TimeSpan = CType(reader.GetValue(ord), TimeSpan)
    dtpMonST.Value = DateTime.Today.Add(ts)
Else
    dtpMonST.Value = DateTime.Today 'or leave unchanged / set a default
End If

If your provider returns the time as a string, parse it with TimeSpan.TryParse instead of casting.

's suggestion to use a BindingSource is fine; the sample posted had a few typos (use dt.Tables / Fill(dt) rather than dt.Table(...)). When databinding a DateTimePicker bind the Value property (not Text), for example:

Dim dt As New DataTable()
Using da As New SqlDataAdapter("SELECT * FROM [HMS].[dbo].[DoctorRegister]", Connection)
    da.Fill(dt)
End Using
BindingSource1.DataSource = dt
dtpBreakFrom.DataBindings.Add("Value", BindingSource1, "BreakFrom", True, DataSourceUpdateMode.OnPropertyChanged)

Notes: the DateTimePicker always stores a date component — CustomFormat only hides it visually. Handle DBNulls and provider differences, and be cautious if stored TIME values represent durations larger than 24 hours (those may need special handling).

Recommended Answers

All 5 Replies

Member Avatar for bhagawatshinde

Is your database field is datetime or varchar

Member Avatar for poojavb

Is your database field is datetime or varchar

In MySql the column datatype is time(0)
In vb.net the type is time
But still its showing the conversion error

Member Avatar for M.Waqas Aslam

hello Pooja !
m not sure that the solution i m providing u is helpful for u or not , but i regularly using this thing in my projects , y not u try databinding and bindingsource , like this
take a bindingsource1

dim dt as new datatable
dim mycon as new sqlconnection("ur conn string")
mycon.open()
dim da as new sqldataadapter("ur command",mycon)
dt.table("mytable").clear
da.fill(dt,"mytable")
bindigsource1.datasource= dt.table("mytable")
'here u have to bindg ur form controls 
datetimepicker1.databind.add("text",bindingsource1,"__ur field in db__",false)' i use here text insteadof time , u can use time also
'with this u can also bind the textboxes and other controls on form

hope this will help u , and yes keep ur custom settings for ur datetime piker to just show time ,

Regards
M.Waqas Aslam

Edited by M.Waqas Aslam because: to give some comments on code
Member Avatar for poojavb

The code did not work..

Its showing error as dt is not a member of DataTable and many more

Dim dt As New DataTable
Dim da As New SqlDataAdapter("SELECT * FROM [HMS].[dbo].[DoctorRegister]", Connection)
dt.Table("DoctorRegister").clear()
da.Fill(dt, "DoctorRegister")
BindingSource1.DataSource = dt.table("DoctorRegister")
dtpBreakFrom.databind.add("text", BindingSource1, "BreakFrom", False)
Member Avatar for poojavb

The code did not work..

Its showing error as dt is not a member of DataTable and many more

Dim dt As New DataTable
Dim da As New SqlDataAdapter("SELECT * FROM [HMS].[dbo].[DoctorRegister]", Connection)
dt.Table("DoctorRegister").clear()
da.Fill(dt, "DoctorRegister")
BindingSource1.DataSource = dt.table("DoctorRegister")
dtpBreakFrom.databind.add("text", BindingSource1, "BreakFrom", False)

Tried with something else....
thank u...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.