dcsimg
Results 1 to 6 of 6

Thread: Update cells in datagrid after filtered

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    3

    Exclamation Update cells in datagrid after filtered

    Hello everyone,

    I am working on an application and I have a problem with editing the cells in the datagrid.
    I have a pTemp table, the data in the table are displayed in the datagrid after applying some filters (project, year, month, period). I need to edit the cells and then save the changes to the database in the same table.Name:  Capture1.jpg
Views: 224
Size:  29.8 KB


    below you will find the code...
    Code:
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Windows.Forms
    Imports System.Data.DataRow
    Imports System.Data.DataTable
    Imports System.Data.DataSet
    Imports System.IO.Directory
    Imports System.Data.OleDb
    'Imports Microsoft.Office.Interop
    'Imports Microsoft.Office.Interop.Excel
    
    
    Public Class frmTimesheet
    
        Dim sCommand As SqlCommand
        Dim sAdapter As SqlDataAdapter
        Dim sTable As DataTable
        Dim sDataSet As DataSet
        Dim sBuilder As SqlCommandBuilder
        Dim connection As New SqlConnection("Data Source=10.xx.xx.xx;Initial Catalog=Daily_P_and_L;Persist Security Info=True;User ID=xxx;password=xxxxxx")
        'Dim constr As String = "Data Source=10.xx.xx.xx;Initial Catalog=Daily_P_and_L;Persist Security Info=True;User ID=xx;password=xxxxxx"
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            Me.PTempTableAdapter.Fill(Me.Daily_P_and_LDataSet.pTemp)
            Me.PopulateComboBox(cbox_year, "select distinct year from pTemp where projectname = '" & txtProject.Text & "'", "year", "year")
            Me.PopulateComboBox(cbox_month, "select distinct month from pTemp where projectname = '" & txtProject.Text & "'", "month", "month")
            Me.PopulateComboBox(cbox_period, "select distinct period from pTemp where projectname = '" & txtProject.Text & "'", "period", "period")
            Me.PopulateComboBox(cbox_language, "select distinct language from pTemp where projectname = '" & txtProject.Text & "'", "language", "language")
        End Sub
    
        Private Sub PopulateComboBox(ByVal comboBox As ComboBox, ByVal query As String, ByVal displayMember As String, ByVal valueMember As String)
            Dim constr As String = "Data Source=10.xx.xx.xx;Initial Catalog=Daily_P_and_L;Persist Security Info=True;User ID=xxxx;password=xxxxxx"
            Using con As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand(query, con)
                    Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        Dim row As DataRow = dt.NewRow()
                        dt.Rows.InsertAt(row, 0)
                        comboBox.DataSource = dt
                        comboBox.DisplayMember = displayMember
                        comboBox.ValueMember = valueMember
                    End Using
                End Using
            End Using
        End Sub
    
        Private Sub PopulateDataGridView()
            Dim query As String = "SELECT * FROM pTemp"
            query += " WHERE year = @year AND month = @month and period = @period and language = @language"
            'query += " OR ISNULL(@year, '') = ''"
            'query += " OR ISNULL(@month, '') = ''"
            'query += " OR ISNULL(@period, '') = ''"
            query += " OR ISNULL(@language, '') = ''"
            Dim constr As String = "Data Source=10.xx.xx.xx;Initial Catalog=Daily_P_and_L;Persist Security Info=True;User ID=xxx;password=xxxxxx"
    
            Using con As SqlConnection = New SqlConnection(constr)
    
                Using cmd As SqlCommand = New SqlCommand(query, con)
                    cmd.Parameters.AddWithValue("@year", cbox_year.SelectedValue)
                    cmd.Parameters.AddWithValue("@month", cbox_month.SelectedValue)
                    cmd.Parameters.AddWithValue("@period", cbox_period.SelectedValue)
                    cmd.Parameters.AddWithValue("@language", cbox_language.SelectedValue)
                    Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        dgvTimesheet.DataSource = dt
                    End Using
                End Using
            End Using
        End Sub
    
        Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
            Me.PopulateDataGridView()
        End Sub
    
      Private Sub btnsave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click
    
      Try
                Dim dr As New DialogResult
                dr = MessageBox.Show("Are you sure you want to Update?", "Transfer", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
                If dr = Windows.Forms.DialogResult.OK Then
                    If connection.State = ConnectionState.Closed Then
                     connection.Open()
                    End If
                    Dim st As String = "update pTemp set [1] = '" & dgvTimesheet.CurrentRow.Cells(9).Value & "'"
                    Dim cmd As New SqlCommand(st, connection)
                    cmd.ExecuteNonQuery()
                    MessageBox.Show("Process Successful!", "Transfer", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    connection.Close()
                End If
            Catch err As Exception
                MessageBox.Show(err.ToString)
            End Try
        End Sub
    
    End Class
    At this moment it saves me the same value on the whole column [1]. I need to modify the columns from 1 -31 and then save them to the base. I practically overwrite them.
    Can you help me please
    Last edited by si_the_geek; Sep 6th, 2019 at 06:46 AM. Reason: added Code tags

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,362

    Re: Update cells in datagrid after filtered

    Welcome to VBForums

    By default an Update statement will set the value(s) for every record in the table, in order to set the value(s) for just one record you need to add an appropriate Where clause, eg:
    Code:
    Dim st As String = "update pTemp set [1] = 'value1' WHERE period = @period"
    (and of course then add parameter values as apt)

    Note however that all records that match the Where clause will be altered, so you need to make sure you have appropriate conditions to limit the records appropriately. If your table has a Primary Key then it is easy to ensure only one record gets updated (if you don't have a Primary Key, it may be a good idea to add an Identity field to use as one).

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    3

    Re: Update cells in datagrid after filtered

    Hi,
    I understood what you mean, but I didn't need to put the same value on the whole column. My piece of code is wrong.
    I need to enter different values ​​on column [1] for example. each cells in column [1] will have a different value.
    I forgot to mention that I have a Primary Key and I can also use the IndividualNO field, it is unique
    Last edited by mihaigrg; Sep 6th, 2019 at 07:31 AM.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,362

    Re: Update cells in datagrid after filtered

    In that case you should use a Where clause like this: WHERE IndividualNO = @IndividualNO
    ...and add the parameter value (from the appropriate row of the grid).

    If you want to update the database for all of the rows in the grid, use a loop to do each row (updating the parameter values).


    Alternatively you could use a DataAdapter to do the work, there is a good example of that (and various other things) here:
    http://www.vbforums.com/showthread.p...a-in-Databases (see "Retrieving multiple records for display and editing" about half way down the first post)

  5. #5

    Thread Starter
    New Member
    Join Date
    Sep 2019
    Posts
    3

    Re: Update cells in datagrid after filtered

    Hi,

    I tried to do what you told me. I changed the cod like this (just for one column, for the momment)...and received an error
    The datatype for IndividualNO field is nvarchar. I also tried with the Primary Key ID, but I get that error message.
    You can show me how to modify it, for a single column. I also do not understand how to do that loop for each row.
    Thank you for your support!


    Try

    Dim dr As New DialogResult

    dr = MessageBox.Show("Are you sure you want to Update?", "Transfer", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
    If dr = Windows.Forms.DialogResult.OK Then
    If connection.State = ConnectionState.Closed Then
    'connection.ConnectionString = "Data Source=10.226.105.222;Initial Catalog=Daily_P_and_L;Persist Security Info=True;User ID=sa;password=GN6/8KuNbrEce22s"
    connection.Open()
    End If
    'Dim st As String = "update pTemp set [1] = '" & dgvTimesheet.CurrentRow.Cells.Item(9).Value & "'"
    Dim st As String = "update pTemp set [1] = @1 WHERE IndividualNO = @IndividualNO"

    'MessageBox.Show((DataGridView1.CurrentRow.Cells(1).Value) & (DataGridView1.CurrentRow.Cells(2).Value) & (DataGridView1.CurrentRow.Cells(3).Value) & (DataGridView1.CurrentRow.Cells(4).Value) & (DataGridView1.CurrentRow.Cells(5).Value))
    Dim cmd As New SqlCommand(st, connection)
    cmd.Parameters.Add("@IndividualNO", SqlDbType.NVarChar, "IndividualNO")
    cmd.Parameters.Add("@1", SqlDbType.NVarChar, "1")

    cmd.ExecuteNonQuery()
    MessageBox.Show("Process Successful!", "Transfer", MessageBoxButtons.OK, MessageBoxIcon.Information)
    connection.Close()
    End If
    Catch err As Exception
    MessageBox.Show(err.ToString)
    End Try

    Name:  error.PNG
Views: 52
Size:  17.9 KB

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,362

    Re: Update cells in datagrid after filtered

    When you provide 3 arguments to cmd.Parameters.Add(), they are: parameterName, sqlDbType, size (of the data type)
    More info (and examples) for .Add() can be seen in the documentation: https://docs.microsoft.com/en-us/dot...collection.add
    I assume you intended to use AddWithValue instead, or perhaps don't know how to set the value (the link above shows how).

    You also need to use the values from the grid (as you did in the MessageBox), rather than using hard-coded values.

    To make it a loop, instead of using .CurrentRow use a For (or ForEach) loop to go thru the .Rows() collection

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width