SQL datetime & VB DateTimePicker 1

[LobsterSte]

Hallo

I need help to get values of a DateTimePicker Into the correct format for a SQL field with the datatype set to datetime, because I have to compare two dates later on.

I was told that it is possible, but can't find an answer on the net by searching.

Any help would be hugely appreaciated!

 

[aviles1973]

I currenty am moving my knowledge from Access to SQLServer and already I am stuck at the PrimaryKey Autonumber Identity issue when adding new rows.

I am replying to this post because I havent used date and time values yet in SQLServer but I am too interested in the answer.

I would guess it would easy being SQLServer is actually built for .net apps primarily. Why would Microsoft make this so hard?

 

[jbenson001]

try something like this:

 Private Sub DateTimePicker1_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged
   MessageBox.Show(DateTimePicker1.Value)
End Sub

You will see when you pick a value in the picker, the messabebox will show the date picked with the current time. You can change the time portion to be what ever you want. When you go to insert/update the sql table, convert the value into a string and pass that to your stored procedure or sql you are running.


Jim

 

[LobsterSte]

That Does Not work. For the reason that sql cannot accept datetime in a string form. I have even tried to create a custome format for the dateTimePicker:

DateTimePicker1.Format = DateTimePickerFormat.Custom
DateTimePicker1.CustomFormat = "mm/dd/yyyy"
cmd.CommandText = "INSERT INTO tblDate Values (" & "'" & Date2 & "','" & Date1 & "')"

Because The datetime Format of sql is mm/dd/yyyy, but no time.

 

[jbenson001]

That Does Not work. For the reason that sql cannot accept datetime in a string form

If you are using sql server, the insert/update will work with a string, for Access or something else, I am not sure.

 

[PGoRule]

I am able to insert the DateTimePicker into SQL server.
The columns(d1, d2) in table(tmpDate) are defined as DateTime type. Here giving the code for your reference:

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        d1.CustomFormat = "mm/dd/yyyy"
        d2.CustomFormat = "mm/dd/yyyy"
    End Sub

    Private Sub b1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles b1.Click
        Dim strd1 = d1.Value
        Dim strd2 = d2.Value
        Dim r As Integer
        Dim strCon As String = "Data Source=(local);Initial Catalog=pubs;Integrated Security=True;"
        Dim con As New System.Data.SqlClient.SqlConnection(strCon)
        Dim com As New System.Data.SqlClient.SqlCommand()

        Try
            con.Open()
            com.CommandText = "Insert into tmpDate values ('" & strd1 & "','" & strd2 & "')"
            com.Connection = con
            r = com.ExecuteNonQuery()
            MessageBox.Show(r)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

Sharing the best from my side...

--Prashant--

 

[chrissie1]

Well if everybody would use parameters then this problem would be a non issue.

Try looking up sql-injection and you will get the picture.

Christiaan Baes
Belgium

"My new site" - Me

 

[LobsterSte]

Ah! Ok, Cool!

Worked, Thanx people!

Now I can finish my project!