none
Insert DataTimePicker to database sql server RRS feed

  • Question

  • which is the correct statement to unpdate a Sql table with a datatimepicker field

    I'm using this code to pass parameters

    	CMD.CommandText = QueryI
    		CMD.Parameters.AddWithValue("@Codice", row.Field(Of String)("ClienteCodice"))
    		CMD.Parameters.AddWithValue("@RagSoc", row.Field(Of String)("ClienteRagSoc"))
    	        CMD.Parameters.AddWithValue("@Citta", row.Field(Of String)("ClienteCitta"))
    		CMD.Parameters.AddWithValue("@Provincia", row.Field(Of String)("ClienteProvincia"))
    	       CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of DatetimePicker)("ClienteDataCreazione"))
      	CMD.Parameters.AddWithValue("@Importo", row.Field(Of String)("ClienteImporto"))
    

    Wednesday, May 23, 2018 7:59 PM

All replies

  • Well the code look okay, the query should look like this with an ExecuteScalar to get the new primary key

    Dim insertStatement As String =
        <SQL>
        INSERT INTO dbo.Orders 
        (
    	    CustomerIdentifier, 
    	    EmployeeID, 
    	    OrderDate
        ) VALUES 
        (
    	    @CustomerIdentifier,  
    	    @EmployeeID, 
    	    @OrderDate
        );
        SELECT CAST(scope_identity() AS int); 
        </SQL>.Value

    Insert only via ExecuteNonQuery

    Dim insertStatement As String =
        <SQL>
        INSERT INTO dbo.Orders 
        (
    	    CustomerIdentifier, 
    	    EmployeeID, 
    	    OrderDate
        ) VALUES 
        (
    	    @CustomerIdentifier,  
    	    @EmployeeID, 
    	    @OrderDate
        );
        </SQL>.Value


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, May 23, 2018 9:23 PM
    Moderator
  • I would think that you need to access the Value property of the DateTimePicker, not the control itself:

    row.Field(Of DateTimePicker)("ClienteDataCreazione").Value


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, May 23, 2018 10:39 PM
    Moderator
  • I would think that you need to access the Value property of the DateTimePicker, not the control itself:

    row.Field(Of DateTimePicker)("ClienteDataCreazione").Value


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Exactly :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, May 23, 2018 11:15 PM
    Moderator
  • Hi Claudio111,

    If you want to insert datetimepicker value to sql database, you need to check date column type in sql database, it is Date or datetime type? If it is date, you can use

     cmd.Parameters.AddWithValue("@Birthday", DateTimePicker1.Value.Date)

    If it is datetime type, you can use

      cmd.Parameters.AddWithValue("@Birthday1", DateTimePicker2.Value)

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 24, 2018 7:39 AM
    Moderator
  • I'm using a 3Layer application so in the DataAccessLayer I have not access to DataTimePicker.

    I have to use the DataTable column name like this

    CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of DatetimePicker)("ClienteDataCreazione"))

    So how to do. ?

    Thursday, May 24, 2018 9:43 AM
  • I'm using a 3Layer application so in the DataAccessLayer I have not access to DataTimePicker.

    I have to use the DataTable column name like this

    CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of DatetimePicker)("ClienteDataCreazione"))

    So how to do. ?

    That code DOES access the DateTimePicker.  When you use Row.Field(Of DateTimePicker) that is getting a DateTimePicker control.  You simply need to access the Value property of that  control.

    CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of DatetimePicker)("ClienteDataCreazione").Value)


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, May 24, 2018 11:06 AM
    Moderator
  • Reed,

    You had already written that. Helping Claudio is for me impossible, therefore to you. Without knowing what type "row" is, it is in my perception impossible to help. 

    In fact gives Cherry in my perception the correct answer. But then the datetime picker has to be passed to the method in  the datalayer. 



    Success
    Cor

    Thursday, May 24, 2018 11:21 AM
  • Reed,

    You had already written that. Helping Claudio is for me impossible, therefore to you. Without knowing what type "row" is, it is in my perception impossible to help. 

    In fact gives Cherry in my perception the correct answer. But then the datetime picker has to be passed to the method in  the datalayer. 



    Success
    Cor


    I agree it is tough, however, it is a pretty safe assumption that "row" is a DataRow.  The variable name is a loose-clue, but the Field(Of T) is a strong-clue since that is a DataRow method.  The code would indicate that they are using an existing row to populate the parameters for a new row.  Presumably, this is the "copy one table to another table" issue.  Not the best solution to that problem, since it could likely be done in one shot with the correct SQL statement, but as you say, we aren't given enough information to provide the best solution to the problem, just enough info to try to resolve the error in question.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, May 24, 2018 12:13 PM
    Moderator
  • Perhaps this simple example will help (and this goes with what Reed recommended)

    Public Class DataOperations
        Public Sub Demo(ByVal pRow As DataRow)
            Dim id = pRow.Field(Of Integer)("id")
            Dim dataCreazine As Date = pRow.Field(Of DateTimePicker)("ClienteDataCreazione").Value
    
    
            MessageBox.Show($"id: {id} date: {dataCreazine}")
        End Sub
    End Class
    
    

    Hard coded usage

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dt As New DataTable
        dt.Columns.Add(New DataColumn() With {.ColumnName = "id", .DataType = GetType(Integer),
                                                .AutoIncrement = True, .AutoIncrementSeed = 1})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "ClienteDataCreazione", .DataType = GetType(DateTimePicker)})
    
        dt.Rows.Add(Nothing, DateTimePicker1)
        Dim ops As New DataOperations
        ops.Demo(dt.Rows(0))
    End Sub

    Expanded to perform an insert and set the primary key of the row passed in

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dt As New DataTable
        dt.Columns.Add(New DataColumn() With {.ColumnName = "id", .DataType = GetType(Integer),
                                            .AutoIncrement = True, .AutoIncrementSeed = 1})
        dt.Columns.Add(New DataColumn() With {.ColumnName = "ClienteDataCreazione", .DataType = GetType(DateTimePicker)})
    
        dt.Rows.Add(Nothing, DateTimePicker1)
        Dim ops As New DataOperations
        If ops.Demo(dt.Rows(0)) Then
            MessageBox.Show($"new key: {dt.Rows(0).Field(Of Integer)("id")}")
        Else
            MessageBox.Show("Insert failed")
        End If
    End Sub

    .

    Public Class DataOperations
        Public Function Demo(ByVal pRow As DataRow) As Boolean
            Dim id = pRow.Field(Of Integer)("id")
            Dim dataCreazine As Date = pRow.Field(Of DateTimePicker)("ClienteDataCreazione").Value
    
            Using cn As New SqlClient.SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlClient.SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO SomeTable (DataCreazione) VALUES (@DataCreazione);" &
                                      "SELECT CAST(scope_identity() AS int); "
    
                    cmd.Parameters.AddWithValue("@DataCreazione", dataCreazine)
    
                    Try
                        cn.Open()
                        '
                        ' insert record, set primary key for DataRow passed in so
                        ' if this method returns true the DataRow now has a primary key
                        '
                        pRow.SetField(Of Integer)("id", CInt(cmd.ExecuteScalar()))
    
                        Return True
                    Catch ex As Exception
                        Return False
                    End Try
                End Using
            End Using
    
        End Function
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, May 24, 2018 12:47 PM
    Moderator
  • I beg all pardon, i give you very few informations

    My application i very simple (i'm still studing )

    Just a Customer Sql Table, a DataGridview to list all customer and a form to Insert/Modify/Delete a single Customer selected in Dgv (DGv read only for all cells)

    3Layer application

    This is the DAL with the solution to solve my problem ( ClienteDataCrreazione is a "Date" columns in Sql Table)

    Imports Libreria
    
    Public Class DALClienti
    	Inherits DALExceptionsHandler
    	Private Property DAL_DS_Clienti As DataSet
    	Private Property DAL_DT_Clienti As DataTable
    	Private Property DAL_DA_Clienti As SqlDataAdapter
    
    	Public Function GetDataset() As DataSet
    		mHasException = False
    		mHasSqlException = False
    		mHasCurrException = False
    		DAL_DS_Clienti = New DataSet
    		Dim QueryS As String = <sql>
    					SELECT 
    						 [ClienteID]
    						,[ClienteCodice]
    						,[ClienteRagSoc]
    						,[ClienteCitta]
    						,[ClienteProvincia]
    						,[ClienteDataCreazione]
    						,[ClienteImporto]
    					FROM Anagrafiche.[Clienti]
    					ORDER BY [ClienteID] ASC
    					</sql>.Value
    		Try
    			Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS}
    				Using CMD = New SqlCommand() With {.Connection = CONN,
    					.CommandType = CommandType.Text}
    
    					Using DAL_DA_Clienti As New SqlDataAdapter With
    					{.SelectCommand = New SqlCommand With {.Connection = CONN}}
    						DAL_DA_Clienti.SelectCommand.CommandText = QueryS
    						DAL_DA_Clienti.MissingSchemaAction = MissingSchemaAction.AddWithKey
    						DAL_DA_Clienti.TableMappings.Add("Table", "Clienti")
    
    						CONN.Open()
    
    						DAL_DA_Clienti.Fill(DAL_DS_Clienti, "Clienti")
    
    						CONN.Close()
    
    						DAL_DT_Clienti = DAL_DS_Clienti.Tables("Clienti")
    
    						Return DAL_DS_Clienti
    					End Using
    				End Using
    			End Using
    		Catch sqlex As SqlException
    			mHasSqlException = True
    			mLastSqlException = sqlex
    		Catch ex As Exception
    			mHasException = True
    			mLastException = ex
    
    		End Try
    
    		Return Nothing
    	End Function
    
    	Public Sub SaveRowDB(ByVal ds As DataSet, ByVal Nrow As Integer, ByVal op As String)
    		DAL_DS_Clienti = ds
    		DAL_DT_Clienti = ds.Tables("Clienti")
    		mHasException = False
    		mHasSqlException = False
    		mHasCurrException = False
    		Dim row As DataRow = DAL_DT_Clienti.Rows(Nrow)
    		Dim QueryI = <sql>
    					INSERT INTO  Anagrafiche.[Clienti]
    						([ClienteCodice]
    						,[ClienteRagSoc]
    						,[ClienteCitta]
    						,[ClienteProvincia]
    						,[ClienteDataCreazione]
    						,[ClienteImporto])
    					VALUES ( @Codice, @RagSoc ,@Citta, @Provincia, @DataCreazione, @Importo );
    					SELECT ClienteID FROM Anagrafiche.[Clienti] 
    					WHERE (ClienteID = (SCOPE_IDENTITY()))
    					</sql>.Value
    
    		Dim QueryU = <sql>
    					UPDATE Anagrafiche.[Clienti]
    					SET	[ClienteCodice]=@Codice
    						,[ClienteRagSoc]=@RagSoc
    						,[ClienteCitta]=@Citta
    						,[ClienteProvincia]=@Provincia
    						,[ClienteDataCreazione]=@DataCreazione
    						,[ClienteImporto]=@Importo
    						WHERE [ClienteID]= @ID
    							</sql>.Value
    		Dim QueryD = <sql>
    					DELETE FROM Anagrafiche.[Clienti]
    					WHERE [ClienteID]= @ID
    					</sql>.Value
    		Try
    			Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS}
    				Using CMD As New SqlCommand With {.Connection = CONN, .CommandType =
    				CommandType.Text}
    					' INSERT COMMAND ---------------------------------	
    					If op = "I" Then
    
    						CMD.CommandText = QueryI
    						CMD.Parameters.AddWithValue("@Codice", row.Field(Of String)("ClienteCodice"))
    						CMD.Parameters.AddWithValue("@RagSoc", row.Field(Of String)("ClienteRagSoc"))
    						CMD.Parameters.AddWithValue("@Citta", row.Field(Of String)("ClienteCitta"))
    						CMD.Parameters.AddWithValue("@Provincia", row.Field(Of String)("ClienteProvincia"))
    						CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of Date)("ClienteDataCreazione"))
    						CMD.Parameters.AddWithValue("@Importo", row.Field(Of Double)("ClienteImporto"))
    
    
    					End If
    					If op = "U" Then
    						CMD.CommandText = QueryU
    						CMD.Parameters.AddWithValue("@ID", row.Field(Of Integer)("ClienteID"))
    
    
    						CMD.Parameters.AddWithValue("@Codice", row.Field(Of String)("ClienteCodice"))
    						CMD.Parameters.AddWithValue("@RagSoc", row.Field(Of String)("ClienteRagSoc"))
    						CMD.Parameters.AddWithValue("@Citta", row.Field(Of String)("ClienteCitta"))
    						CMD.Parameters.AddWithValue("@Provincia", row.Field(Of String)("ClienteProvincia"))
    						CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of Date)("ClienteDataCreazione"))
    						CMD.Parameters.AddWithValue("@Importo", row.Field(Of Double)("ClienteImporto"))
    
    					End If
    					If op = "D" Then
    						CMD.CommandText = QueryD
    						CMD.Parameters.AddWithValue("@ID", row.Field(Of Integer)("ClienteID"))
    					End If
    					CONN.Open()
    					CMD.ExecuteNonQuery()
    					CONN.Close()
    				End Using
    			End Using
    		Catch sqlex As SqlException
    			mHasSqlException = True
    			mLastSqlException = sqlex
    
    		Catch ex As Exception
    			mHasException = True
    			mLastException = ex
    		End Try
    	End Sub
    
    End Class
    
    
    

    And this is hte solution 

    CMD.Parameters.AddWithValue("@DataCreazione", row.Field(Of Date)("ClienteDataCreazione"))

    row.field(Of Date) not row.field(Of DataTimePicker)

    A stupid error for a student ;-)

    Thank you to all for let me thinking and thinking.

    p.s. Dear Core ..... try to be positive ! ! ! ! ! ! ! ! not polemical every time. 


    Thursday, May 24, 2018 7:38 PM

  • A stupid error for a student ;-)


    p.s. Dear Core ..... try to be positive ! ! ! ! ! ! ! ! not polemical every time. 


    Why do I get always this kind of reactions? You see telling that you go in the wrong direction as a polemical response.

    Your reactions are as from somebody who created .Net himself al alone. 

    I constantly try to warn you (Or in fact those who find your messages on Internet when they have a problem), that you use far to much code. .Net is created to let us not endless times again and again code the same things. They are predefined and situated in the .Net framework. 

    You try to use VB as if it is a kind of assembler code. 

    You use only one datatable. So the complete code you use can be done with the commandbuilder.

    http://www.vb-tips.com/CommandBuilderDataGridview.ASPX (for SQL server change OleDB everywhere in SQLClient)

    That picture of me on that site is taken in my favorite city in Italy.


    Success
    Cor




    Friday, May 25, 2018 9:45 AM