Update cells in datagrid after filtered

[mihaigrg]

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.


below you will find the 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

[si_the_geek]

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:

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).

[mihaigrg]

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

[si_the_geek]

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)

[mihaigrg]

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

[si_the_geek]

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