Results 1 to 9 of 9

Thread: [RESOLVED] Writing to database Current Date.Time in format

  1. #1

    Thread Starter
    Addicted Member t3cho's Avatar
    Join Date
    Mar 2014
    Posts
    231

    Resolved [RESOLVED] Writing to database Current Date.Time in format

    I need to add the current date.time in database in format dd/MM/yyyy HH:mm .

    In Sql Server Database i set the DataType for column as datetime. So my insert query goes like this now

    Code:
                "Insert into table (Product, DateAdd) values ('" & Product & "', '" & Date.Now & "')"
    It works great. But when i want to format the Date.Now to custom string like this

    Code:
           Dim time As DateTime = DateTime.Now
                Dim format As String = "dd.MM.yyyy HH:mm"
                "Insert into table (Product, DateAdd) values ('" & Product & "', '" & time.ToString(format) & "')"
    It won't add the record so i suppose something is wrong either of my format or datatype in database

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: Writing to database Current Date.Time in format

    Parameters Parameters Parameters !!!

    Using them will not only fix any date problems you have, but will take care of things like special characters like quotes. It also guards against SQL Injection.

    In short you should always be using them!!!

    take a look at this
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  3. #3

    Thread Starter
    Addicted Member t3cho's Avatar
    Join Date
    Mar 2014
    Posts
    231

    Re: Writing to database Current Date.Time in format

    Code:
    Public Sub Town(Name As String)
            Try
                Dim time As DateTime = DateTime.Now
                Dim format As String = "dd.MM.yyyy HH:mm"
                Dim MyCommand As SqlCommand
                MyCommand = New SqlCommand("Insert into test (Name, DateAdd) values (@name, @test)", sqlcon)
                MyCommand.Parameters.AddWithValue("@name", form2.txtName.Text)
                MyCommand.Parameters.AddWithValue("@test", time.ToString(format))
                sqlcon.Open()
                MyCommand.ExecuteNonQuery()
                sqlcon.Close()
            Catch ex As Exception
            End Try
        End Sub
    It shows me an error message "The connection was not closed ... "
    Name:  cb0808e0fb18843ee67926ff0f4a5141.jpg
Views: 43
Size:  7.6 KB


    Previous code

    Code:
     Public Sub Town(Name As String)
            Try
                Dim addString As String = "Insert into table (Name, DateAdd) values ('" & Name & "', '" & Date.Now & "')"
                sqlcon.Open()
                SqlCom = New SqlCommand(addString, sqlcon)
                SqlCom.ExecuteNonQuery()
                sqlcon.Close()
            Catch ex As Exception
            End Try
        End Sub
    Last edited by t3cho; Apr 16th, 2015 at 04:37 AM.

  4. #4
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: Writing to database Current Date.Time in format

    Have you noticed that you pass the connection into your previous command -

    Code:
    SqlCom = New SqlCommand(addString, sqlcon)
    but not your new one?

    Code:
    MyCommand = New SqlCommand("Insert into test (Name, DateAdd) values (@name, @test)"
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  5. #5

    Thread Starter
    Addicted Member t3cho's Avatar
    Join Date
    Mar 2014
    Posts
    231

    Re: Writing to database Current Date.Time in format

    I don't quite understand what are you trying to say. I passed the connection to mycommand aswell

    Code:
     MyCommand = New SqlCommand("Insert into test (Name, DateAdd) values (@name, @test)", sqlcon)

  6. #6
    Junior Member valleteclark12's Avatar
    Join Date
    Mar 2015
    Location
    Best Cordless Drill & Tool Kit Buying Guide
    Posts
    16

    Re: Writing to database Current Date.Time in format

    Insert current datetime you can read here: https://msdn.microsoft.com/en-us/library/bb630353.aspx

  7. #7
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: Writing to database Current Date.Time in format

    I don't quite understand what are you trying to say. I passed the connection to mycommand aswell
    Ah sorry i misread your code as it went across to a newline on my screen.

    Well your error is telling you when you are trying your sqlcon.Open() command you still have your connection open probably because you forgot to close it somewhere else in your code
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  8. #8

    Thread Starter
    Addicted Member t3cho's Avatar
    Join Date
    Mar 2014
    Posts
    231

    Re: Writing to database Current Date.Time in format

    Well i did it in different way like this .

    Code:
     dgv.Columns(2).DefaultCellStyle.Format = "dd/MM/yyyy HH:mm"
    It works great and i dont need to touch anything

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,953

    Re: Writing to database Current Date.Time in format

    Quote Originally Posted by t3cho View Post
    Code:
    Public Sub Town(Name As String)
            Try
                Dim time As DateTime = DateTime.Now
                Dim format As String = "dd.MM.yyyy HH:mm"
                Dim MyCommand As SqlCommand
                MyCommand = New SqlCommand("Insert into test (Name, DateAdd) values (@name, @test)", sqlcon)
                MyCommand.Parameters.AddWithValue("@name", form2.txtName.Text)
                MyCommand.Parameters.AddWithValue("@test", time.ToString(format))
                sqlcon.Open()
                MyCommand.ExecuteNonQuery()
                sqlcon.Close()
            Catch ex As Exception
            End Try
        End Sub
    It shows me an error message "The connection was not closed ... "
    Name:  cb0808e0fb18843ee67926ff0f4a5141.jpg
Views: 43
Size:  7.6 KB


    Previous code

    Code:
     Public Sub Town(Name As String)
            Try
                Dim addString As String = "Insert into table (Name, DateAdd) values ('" & Name & "', '" & Date.Now & "')"
                sqlcon.Open()
                SqlCom = New SqlCommand(addString, sqlcon)
                SqlCom.ExecuteNonQuery()
                sqlcon.Close()
            Catch ex As Exception
            End Try
        End Sub
    I don't see how that code could ever display an error... you shut off all error handling with an empty catch. That's almost like doing On Error Resume Next. I can only assume an error IS happening but you've stuffed cotton, a couple of bananas and your fingers into your ears and are refusing to listen.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width