using vb.net with mysql

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.

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 :)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

romanvo22y ago
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 :)
0