using vb.net with mysql

Question

Hello. I am using VB.Net to connect to mysql database. Using input from textboxes to link with fields defined as type varchar work perfectly fine. However a constant problem arises when i try to link an input from datetimepicker with a MySQL field of data type time. Whatever i try the database time always shows 12:00:00. I had a similar problem initially with MySQL data type date. I managed to overcome the date problem using the code below: 'The function below was obtained from the following website 'http://www.vbcity.com/forums/faq.asp?fid=44&cat=SQL%20Server#TID31172 Public Shared Function DateToDB(ByVal TheDate As Date) As String Dim str As String With TheDate str = (.Year.ToString.PadLeft(2 Convert.ToChar(0)) & - & .Month.ToString.PadLeft(2 Convert.ToChar(0)) & - & .Day.ToString.PadLeft(2 Convert.ToChar(0))) End With Return str End Function However this does not work with data type time. Please advise and thanking you in advance.

romanvo · Answer

Hello! I'm not sure that i understood what you asked because i don't use to speak english  but i think i did hehehe I'll tell you what it happend to me and how i solved it maybe it's the same thing what you need... I had a table with a field DATETIME and i wanted to SELECT a row from a table and INSERT that row in the same table that belongs to another database. DATABASE1 DATABASE2 MY_TABLE =====> MY_TABLE The problem I had was the same as you said: the hour was always 00:00:00 What I did was to create a function to SELECT a field: ______________________________ Public Function SeleccionFecha(ByVal parametro As String) As String SeleccionFecha = CONCAT( & _ YEAR( & parametro & ) '-'  & _ MONTH( & parametro & ) '-'  & _ DAYOFMONTH( & parametro & ) ' '  & _ HOUR( & parametro & ) ':'  & _ MINUTE( & parametro & ) ':'  & _ SECOND( & parametro & ))  & _ AS  & parametro End Function ______________________________ and i used it so: ______________________________ orden_sql.CommandText = SELECT  & _ SeleccionFecha(MY_DATETIME) & _ FROM  & USUARIOS & _ WHERE & USUARIO=' & usuario.Text & ' resultado = orden_sql.Execute orden_sql.ActiveConnection = conexion_r orden_sql.CommandText = INSERT_INTO & USUARIOS & _ (USUARIO CONTRASENYA  & _ NOMBRE APELLIDO1  & _ APELLIDO2 TELEFONO  & _ EMAIL MY_DATETIME)  & _ VALUES & ( & _ ' & usuario.Text.Trim & '  & _ ........... ' & email.Text.Trim & '  campo = resultado(MY_DATETIME) orden_sql.CommandText &= ' & campo.Value & ') resultado = orden_sql.Execute ______________________________ You only have to watch the field MY_DATETIME. In the function the parameter parametro is supposed to be a DATETIME field. Then can you read it like '20-01-2005 20:30:00'. Mysql likes to insert datetime fields like that one. Ok!! I hope that was what you asked. Hasta la vista :)