none
How to save converted datetime variable RRS feed

  • Question

  • Hello
    how can i save this converted time
     If m_DataSet.HasChanges() Then
    
                Dim data_adapter As SqlDataAdapter
    
                Dim Command_Builder As SqlCommandBuilder
    
    
    
                Conn = New SqlConnection(ConnectionString)
    
                

    strSQL =

    "SELECT id,comment,CONVERT(varchar(35), tb1.Time1, 108) As Time1,description FROM dbo.tb1"

    data_adapter = New SqlDataAdapter(strSQL, Conn) data_adapter.TableMappings.Add("Table", "tb1") Command_Builder = New SqlCommandBuilder(data_adapter) MsgBox(Command_Builder.GetInsertCommand.CommandText) MsgBox(Command_Builder.GetUpdateCommand.CommandText) MsgBox(Command_Builder.GetDeleteCommand.CommandText) ' Save the changes. data_adapter.Update(m_DataSet) MsgBox("sucesfully saved")

    • Moved by Arnie RowlandMVP Sunday, November 29, 2009 10:21 PM (From:Transact-SQL)
    • Moved by VMazurModerator Wednesday, December 2, 2009 11:23 AM (From:ADO.NET DataSet)
    Sunday, November 29, 2009 8:29 PM

Answers

  • Hi Lasha,

    If you want to use generated command for updating, you will do some more coding on displaying
    When you display the data in DataGridView, you can use cell formatting event before you show the date column.

    If you insist on selecting the formatted date, you'll have to write the update command by your self.

    If you have any further problem, please update the thread.


    Best Regards
    Yichun Feng
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Lasha34 Thursday, December 3, 2009 3:54 PM
    Thursday, December 3, 2009 3:16 AM

All replies

  • This question seems better positioned in the ADO.NET.Dataset Forum.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Sunday, November 29, 2009 10:21 PM
  • Hi Lasha,

    your column field type in the database needs to be Time(7)
    CAST(GETDATE() AS time(7)) AS 'time'

    John
    Monday, November 30, 2009 12:14 AM
  • thanks and can you show me this code in my example?
    Monday, November 30, 2009 1:03 PM

  • Well once you create your Time(7) DataType Field in the Database all you need to do is to write a save method to send your changes back to your database table.
    Now I'm not sure if you are using Strongly Typed or Untyped Dataset and you had MsgBox in your Update, Delete and Insert Method (I didn't understand what that meant in your code)

    The code below sends your changes from the dataset back to the database (Insert, Update and Delete) assuming that you have a public datatable (dt1) in your Dataset (ds)

    #Region "Visual Studio Namespace"
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text
    Imports System.IO
    Imports System.String
    #End Region
    
    
     Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
    
            'After User change data you can send your data back to the database
            'Here is a sample
            Dim dtRows_Added As DataTable = dt1.GetChanges(DataRowState.Added) 'Added Records
            Dim dtRows_Modified As DataTable = dt1.GetChanges(DataRowState.Modified) 'Updated Records
            Dim dtRows_Deleted As DataTable = dt1.GetChanges(DataRowState.Deleted) 'Deleted Records
    
            If ds.HasChanges() Then
                Using Conn As New SqlConnection(My.Settings.MyConnection)
                    Using Adapter As New SqlDataAdapter()
                        'Insert Command 
                        If Not (dtRows_Added Is Nothing) Then
                            Dim SQLQuery As New StringWriter()
                            With SQLQuery
                                .WriteLine("INSERT INTO [Northwind].[dbo].[Orders]([CustomerID],[EmployeeID],[OrderDate],[OrderTime]) ")
                                .WriteLine("VALUES (@CustomerID ,@EmployeeID ,@OrderDate, @OrderTime) ")
                            End With
                            Adapter.InsertCommand = New SqlCommand(SQLQuery.ToString(), Conn)
                            Adapter.InsertCommand.CommandType = CommandType.Text
                            'Get the Parameter return value
                            Dim myParm As SqlParameter = Adapter.InsertCommand.Parameters.Add("@Rowcount", SqlDbType.Int, 5, "@@ROWCOUNT")
                            myParm.Direction = ParameterDirection.ReturnValue
                            With Adapter.InsertCommand.Parameters
                                .Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
                                .Add("@EmployeeID", SqlDbType.Int, 5, "EmployeeID")
                                .Add("@OrderDate", SqlDbType.DateTime, 22, "OrderDate")
                                .Add("@OrderTime", SqlDbType.Time, 16, "OrderTime")
                                'keep adding fields with their parameters etc....
                            End With
                        End If
    
                        'Update Command
                        If Not (dtRows_Modified Is Nothing) Then
                            Dim SQLQuery As New StringWriter()
                            With SQLQuery
                                .WriteLine("UPDATE [Northwind].[dbo].[Orders] ")
                                .WriteLine("SET [CustomerID] = @CustomerID, [EmployeeID] = @EmployeeID, ")
                                .WriteLine("[OrderDate] = @OrderDate, [OrderTime] = @OrderTime ")
                            End With
                            Adapter.UpdateCommand = New SqlCommand(SQLQuery.ToString(), Conn)
                            Adapter.UpdateCommand.CommandType = CommandType.Text
                            Dim myParm As SqlParameter = Adapter.UpdateCommand.Parameters.Add("@RowCount", SqlDbType.Int, 5, "@@ROWCOUNT")
                            myParm.Direction = ParameterDirection.ReturnValue
                            With Adapter.UpdateCommand.Parameters
                                'if one of your parameter is a primary key use sourceversion = datarowversion.Original 
                                .Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID").SourceVersion = DataRowVersion.Current
                                .Add("@EmployeeID", SqlDbType.Int, 5, "EmployeeID").SourceVersion = DataRowVersion.Current
                                .Add("@OrderDate", SqlDbType.DateTime, 22, "OrderDate").SourceVersion = DataRowVersion.Current
                                .Add("@OrderTime", SqlDbType.Time, 16, "OrderTime").SourceVersion = DataRowVersion.Current
                            End With
                            'Similar Code to the above insert command use UpdateCommand Instead
                        End If
    
                        'Delete Command
                        If Not (dtRows_Deleted Is Nothing) Then
                            Dim SQLQuery As New StringWriter()
                            With SQLQuery
                                .WriteLine("DELETE FROM [Northwind].[dbo].[Orders] ")
                                .WriteLine("WHERE [OrderID] = @OrderID")
                            End With
                            Adapter.DeleteCommand = New SqlCommand(SQLQuery.ToString(), Conn)
                            Adapter.DeleteCommand.CommandType = CommandType.Text
                            Dim myParm As SqlParameter = Adapter.DeleteCommand.Parameters.Add("@RowCount", SqlDbType.Int, 5, "@@ROWCOUNT")
                            myParm.Direction = ParameterDirection.ReturnValue
                            With Adapter.DeleteCommand.Parameters
                                .Add("@OrderID", SqlDbType.Int, 10, "OrderID")
                            End With
                        End If
                        'once you are done you need to update your datatable 
                        Adapter.Update(dt1)
                    End Using 'Adapter Using Block
                End Using 'Connection Using Block
            End If
    
        End Sub
    


    John
    Monday, November 30, 2009 3:47 PM
  • how this will be in my case?
    Monday, November 30, 2009 6:20 PM
  • This was an example of how to save your data back to the database. I had to supply another code cause the code that you posted was not clear. especially this part

                MsgBox(Command_Builder.GetInsertCommand.CommandText)

              

                MsgBox(Command_Builder.GetUpdateCommand.CommandText)

               

                MsgBox(Command_Builder.GetDeleteCommand.CommandText)


    John
    Monday, November 30, 2009 11:56 PM
  • Hi Lasha,

    For your issue, I think the most simple way is to select the original value from DB and do not modify it in select string.
    Then convert the date while displaying.

    Best Regards
    Yichun Feng
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 2, 2009 8:55 AM
  • hi
    but i want to display only time such this format "HH:mm:ss"  and not datetime for update
    Wednesday, December 2, 2009 3:47 PM
  • Hi Lasha,

    If you want to use generated command for updating, you will do some more coding on displaying
    When you display the data in DataGridView, you can use cell formatting event before you show the date column.

    If you insist on selecting the formatted date, you'll have to write the update command by your self.

    If you have any further problem, please update the thread.


    Best Regards
    Yichun Feng
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Lasha34 Thursday, December 3, 2009 3:54 PM
    Thursday, December 3, 2009 3:16 AM
  • hi
    but i want to display only time such this format "HH:mm:ss"  and not datetime for update

    Once your data ready to be saved to the database use the sql syntax below to send your data back in hh:mm:ss format
    Replace GETDATE() with the Column Name

    CAST(GETDATE() AS time(7)) AS 'time'

    John
    Thursday, December 3, 2009 2:36 PM
  • Hi Lasha,

    If you want to use generated command for updating, you will do some more coding on displaying
    When you display the data in DataGridView, you can use cell formatting event before you show the date column.

    If you insist on selecting the formatted date, you'll have to write the update command by your self.

    If you have any further problem, please update the thread.



    Hi and thanks, this helped

     Dim timeCellStyle As New DataGridViewCellStyle
            timeCellStyle.Format = "HH:mm:ss"
    
    
            With Me.DataGridView1
                .Columns("time1").DefaultCellStyle = timeCellStyle
            End With

    Thursday, December 3, 2009 3:54 PM
  • hi
    but i want to display only time such this format "HH:mm:ss"  and not datetime for update

    Once your data ready to be saved to the database use the sql syntax below to send your data back in hh:mm:ss format
    Replace GETDATE() with the Column Name

    CAST(GETDATE() AS time(7)) AS 'time'
    
    
    Hi

     

     

    strSQL = "SELECT id,comment,CAST(time1 AS time(7)) AS 'time',description FROM dbo.tb1"
    


    it shows error and writes (Type time is not a defined system type)

    Thursday, December 3, 2009 3:59 PM
  • what sql server version are you using?
    John
    Thursday, December 3, 2009 4:37 PM
  • sql server 2005

    Friday, December 4, 2009 5:54 AM