0

I Searched a lot about how to Get The Time online and i found a solution You will found it in my Codes.

My Aim is to make a MemberShip DateTime , First Step to make the program get the Time from internet startdate then add the Time which is the membership time as enddate,Second Step when the program run again it will replace the start time with the actual time "Got from internet too" and substract it with the enddate

Here is My Database Structure (Note i tried to make the enddate and startdate As DateTime but it failed):

Database Structure

Here is my Program :

[Program Photo]

here is my code :

Imports System.Globalization
Imports System.Net
Imports MySql.Data.MySqlClient


Public Class Form1
    Dim date1 As DateTime
    Dim date2 As DateTime
    Dim date3 As DateTime
    Dim x As DateTime
    Public Shared Function GetNistTime() As DateTime
        Dim myHttpWebRequest = CType(WebRequest.Create("http://www.microsoft.com"), HttpWebRequest)

        Dim response = myHttpWebRequest.GetResponse()
        Dim todaysDates As String = response.Headers("date")
        Return DateTime.ParseExact(todaysDates, "ddd, dd MMM yyyy HH:mm:ss 'GMT'", CultureInfo.InvariantCulture.DateTimeFormat, DateTimeStyles.AssumeUniversal)
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        x = GetNistTime()
        TextBox1.Text = x

        date1 = CStr(x.AddMonths(1))
        Dim MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString = "server=localhost;userid=root;password=123456789;database=test"
        Dim Reader As MySqlDataReader
        Dim Query As String
        Dim sqlcommand As MySqlCommand
        Try
            MySqlConn.Open()
            Query = "UPDATE test.time SET startdate='" & x & "',enddate='" & date1 & "' WHERE username='program'"
            sqlcommand = New MySqlCommand(Query, MySqlConn)
            Reader = sqlcommand.ExecuteReader
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString = "server=localhost;userid=root;password=123456789;database=test"
        Dim Reader As MySqlDataReader
        Dim Query As String
        Dim sqlcommand As MySqlCommand
        Try
            MySqlConn.Open()
            Query = "Select * from test.time  WHERE username='program'"
            sqlcommand = New MySqlCommand(Query, MySqlConn)
            Reader = sqlcommand.ExecuteReader
            While Reader.Read
                date2 = Reader.GetDateTime("startdate")
                date3 = Reader.GetDateTime("enddate")
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try
        TextBox2.Text = date2
        TextBox3.Text = date3
    End Sub
End Class

When i Run the Program and Press button2 I got this Error:

[Program Error Photo]

then when i press ok on ex.message this writen in TextBox2 & TextBox3:

[Program Error Photo]

This is the out put which stored in the Database:

[The Out Put in Database]

Please Help Me And Edit anything you Want in code or here, if you have another Idea Let me know it, sorry if i put All the Details and seem boring,thanks

i Know a way to replace "ص" or "م" to "AM" and "PM" If you have Any way to get the Date with "Am" and "PM" directly i would be Grateful

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
  • 1
    "i tried to make the enddate and startdate As DateTime but it failed" <- you should resolve that first. When that's fixed, change your update sql to use parameters. Also worth using `Using` for any objects that need to be disposed. – Andrew Mortimer May 07 '20 at 05:27
  • This is not a Big problem Because The startdate or enddate don`t accept the AM or PM that shows me An Error So i make it Varchar ! so is there any Way to Fix the Format of the date and Send it ?@AndrewMortimer – Eslam Alawy May 07 '20 at 05:57
  • 1
    Dates are dates, and strings are strings. If you want to store a date, use a date column. – Andrew Mortimer May 07 '20 at 06:42
  • **I get the Date Like That :** 20/05/2020 07:31:19 ص **And SQL accept it Like That:** 2020-05-15 07:31:19 **What i can do** @AndrewMortimer – Eslam Alawy May 07 '20 at 06:47
  • @AndrewMortimer and i want to Substract Two Dates From Time To Time i want to have a Substractable Date , What Should I do – Eslam Alawy May 07 '20 at 06:49
  • 1
    @EslamAlawy The `&` will convert the datetimes to strings, which is not what you want. To prevent that, use SQL parameters to pass the data. Also, I suspect you meant `DateTimeStyles.AdjustToUniversal` so that it gets the UTC date (I tested it). – Andrew Morton May 07 '20 at 08:27
  • so please if you tested it what is the selution you wrote in your code ,can you give me it ! – Eslam Alawy May 07 '20 at 08:52
  • @EslamAlawy Sorry, maybe I wasn't clear there: I only tested the DateTimeStyles.AdjustToUniversal part. – Andrew Morton May 07 '20 at 08:55
  • so do you mean prametars like this in selution ?: `MyCommand = New SqlCommand("UPDATE SeansMessage SET Message = @TicBoxText WHERE Number = 1", dbConn) MyCommand.Parameters.AddWithValue("@TicBoxText", TicBoxText.Text)` – Eslam Alawy May 07 '20 at 08:57
  • @EslamAlawy You can see how to use SQL parameters in [this answer by me](https://stackoverflow.com/a/60914324/1115360). You will need to change the names from "OleDbConnection" to "MySqlConnection" and so on. – Andrew Morton May 07 '20 at 08:59
  • 1
    @EslamAlawy Yes, that is going in the right direction, but don't use AddWithValue as that can lead to other problems: [AddWithValue is Evil](http://www.dbdelta.com/addwithvalue-is-evil/), [AddWithValue is evil!](http://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html), and [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). – Andrew Morton May 07 '20 at 09:00
  • ok but is this will prevent "ص" from appear after the Hour Time !@AndrewMorton – Eslam Alawy May 07 '20 at 09:01
  • @AndrewMorton brother you seem kind , do you know any way else to achive my AIm ! you can know my aim if you read the first lines of this problem – Eslam Alawy May 07 '20 at 09:13
  • 1
    @EslamAlawy You can control exactly how the datetime appears as text by using ToString as shown in [Custom date and time format strings](https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings). Also, you need to use [`Option Strict On`](https://stackoverflow.com/a/29985039/1115360) so that Visual Studio can tell you where variable types do not match. – Andrew Morton May 07 '20 at 09:16
  • @AndrewMorton i used this but i got Error my Code : ` sqlcommand.Parameters.Add(New MySqlParameter With {.ParameterName = "@addStartDate", .MySqlDbType = MySqlDbType.DateTime, .Value = x}) sqlcommand.Parameters.Add(New MySqlParameter With {.ParameterName = "@addEndDate", .MySqlDbType = MySqlDbType.DateTime, .Value = date1}) ` Error ** System.NullReferenceException: 'Object reference not set to an instance of an object. sqlcommand was Nothing.** – Eslam Alawy May 07 '20 at 09:37
  • i solved it by putting `As New` – Eslam Alawy May 07 '20 at 09:41
  • @AndrewMorton i have another problem : the program Successfully reached to the database and updated the value of Start Date and End Date but with false values : like this `0000-00-00 00:00:00` for start date and `0000-00-00 00:00:00` for end date – Eslam Alawy May 07 '20 at 09:47
  • @EslamAlawy If you could [edit] your question to add the code as it is at the moment, it would be easier for us. – Andrew Morton May 07 '20 at 11:04

0 Answers0