Add to database datetime in custom format

Question

I need to add the current date.time in database in format

dd/MM/yyyy HH:mm

This way without formatting works great

 Public Sub AddIt(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

Tried with parameters to format

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 says connection was not closed. The connection state is open

open the connection before using it in new SQL Command

Tushar Gupta
– Tushar Gupta

2015-04-16 10:23:23 +00:00
Commented Apr 16, 2015 at 10:23 — Tushar Gupta
– Tushar Gupta, Commented Apr 16, 2015 at 10:23

Psychemaster · Accepted Answer · 2015-04-16 10:43:56Z

2

I question why you would need the date and time to be in the database in that format - it would be much easier to simply store the full Date object in the database as shown in the first code snippet, and then whenever you retrieve that data, convert it into the format you require using DateTime.toString("dd/MM/yyyy HH:mm")

I have a feeling that your current problem is being caused by a previous exception - specifically, as you've not included any code in your exception handling, if the exception occurs as a result of a failed SQL statement, the connection will remain open - causing the next open call to fail.

You can rectify this by checking the connection state before attempting to re-open it - SqlConnection exposes a ConnectionState property that you can check to see if the connection is already open.

edited Apr 16, 2015 at 10:43

answered Apr 16, 2015 at 10:24

Psychemaster

87611 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anel Hodzic Over a year ago

Yes didn't thought about it. Done on your way ;) P.S Is there any way to get selected in datagridview row that has been last inserted/updated ???

Psychemaster Over a year ago

Not that I know of. Worth a new question, I think.

Tushar Gupta · Accepted Answer · 2015-04-16 10:25:27Z

0

Open the connection before initializing your SQLcommand

Public Sub Town(Name As String)
    Try
        Dim time As DateTime = DateTime.Now
        Dim format As String = "dd.MM.yyyy HH:mm"
        sqlcon.Open()
        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))
        MyCommand.ExecuteNonQuery()
        sqlcon.Close()
    Catch ex As Exception
    End Try
End Sub

answered Apr 16, 2015 at 10:25

Tushar Gupta

16k1 gold badge33 silver badges53 bronze badges

1 Comment

Anel Hodzic Over a year ago

Still the same problem even with that.

Collectives™ on Stack Overflow

Add to database datetime in custom format

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related