Double Click Row in DGV to add information to Access 2010 table

Good Afternoon.

I am designing an application in VB.Net and am using a datagridview (DGV) within one of my windows forms, which is pulling the data from a table within an Access 2010 database.

What I’m trying to accomplish is this:

1. The user double clicks on one of the rows within the DGV and a password box appears asking them for the supervisor password.
2. If they input the correct password, a confirmation box appears asking if they are sure they want to proceed.
3. If the user clicks ‘Yes” then the current system date “DD-MMM-YYYY” is inserted and saved in to the “Voided” column for the row that the user selected.

How would I accomplish “3”?

Screenshots and code below:

DATAGRIDVIEW within Windows Form
dgvCode for the above form
Imports System.Data.OleDb
Imports System.IO

Public Class frmSBTHist

    Private Sub frmSBTHist_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim conn As New OleDbConnection
        Dim da As New OleDbDataAdapter
        Dim ds As DataSet
        Dim tables As DataTableCollection
        Dim SBTHistSrc As New BindingSource

        'Try catch block for when no history exists

        Try

            'get connection string declared in the modFunctions.vb and assing it to conn variable

            conn = New OleDbConnection(sConnString)
            conn.Open()
            ds = New DataSet
            tables = ds.Tables
            da = New OleDbDataAdapter("Select * from [tblSBTHist]", conn)
            da.Fill(ds, "conn") 'Database name
            Dim view As New DataView(tables(0))
            SBTHistSrc.DataSource = view
            dtgSBTHist.DataSource = view

            'Displays with leading 0's in DGV
            dtgSBTHist.Columns("ChqNum").DefaultCellStyle.Format = "000000"
            'Displays as a fixed number with 2 decimal places
            dtgSBTHist.Columns("Amount").DefaultCellStyle.Format = "N2"
            'Hides ID Autonumber Column
            dtgSBTHist.Columns("SBTChqID").Visible = False

            If dtgSBTHist.Rows.Count = 0 Then
                MsgBox("No records found!")
                Me.Close()
            End If

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try

    End Sub

    Private Sub btnSBTHistClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSBTHistClose.Click

        Me.Close()

    End Sub

    Private Sub dtgSBTHistVoid_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtgSBTHist.DoubleClick

        frmSuperPwdVoid.ShowDialog()

    End Sub

End Class

Open in new window


Table settings for Voided Column
table1
Code for frmSuperPwdVoid
Imports System.IO
Imports Microsoft.Win32

Public Class frmSuperPwdVoid

    Private Sub btnSPwdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSPwdCancel.Click

        txtSPwd.Text = ""
        Me.Close()

    End Sub

    Private Sub btnSPwdOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSPwdOK.Click

        Try

            With My.Computer.Registry

                Dim strGetValue As String

                strGetValue = .GetValue(gRegKeyName, "SupervisorPwd", "")
                If txtSPwd.Text = strGetValue Then

                    If MsgBox("Are you sure you want to void the selected cheque?" & vbNewLine & "This process cannot be undone.", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo, "WARNING") = MsgBoxResult.Yes Then

                        'Dont know what code to put here to accomplish

                    Else

                        Me.Close()

                    End If

                Else
                    MsgBox("Password incorrect.", MsgBoxStyle.Exclamation, "Supervisor Password")
                    txtSPwd.Text = ""
                End If

            End With

        Catch ex As Exception

        End Try

    End Sub

    
End Class

Open in new window


Expected Result
result1
Kindly advise.

Regards,
N
LVL 1
KevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
How did you create the columns (run-time or design-time)?  

If you added them at design-time, then you should be able to refer to the columns by name:

Dim column As Integer = grid.Columns("FirstName).Index

Then, you could set the cell value like this:

grid(column, row).Value = "Bob"
0
KevinAuthor Commented:
Hi Bob,

Thanks for your response. But I don't think your solution works to what I am trying to accomplish.

What I am trying to accomplish is:

3. If the user clicks ‘Yes” then the current system date “DD-MMM-YYYY” is inserted and saved in to the “Voided” column for the row that the user selected.

I figured out that I would need to open the connection to the access database and run a query to update the cell.

I have added the below code (Lines 29 to 55) to the frmSuperPwdVoid windows form.

Imports System.Data.OleDb
Imports System.IO
Imports Microsoft.Win32

Public Class frmSuperPwdVoid

    Private Sub btnSPwdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSPwdCancel.Click

        txtSPwd.Text = ""
        Me.Close()

    End Sub

    Private Sub btnSPwdOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSPwdOK.Click

        Try

            With My.Computer.Registry

                Dim strGetValue As String

                strGetValue = .GetValue(gRegKeyName, "SupervisorPwd", "")
                If txtSPwd.Text = strGetValue Then



                    If MsgBox("Are you sure you want to void the selected cheque?" & vbNewLine & "This process cannot be undone.", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo, "WARNING") = MsgBoxResult.Yes Then

                        Dim conn As New OleDbConnection
                        Dim cmd As New OleDbCommand
                        Dim sSQL As String = String.Empty

                        Try
                            'get connection string declared in the modFunctions.vb and assign it to conn variable
                            conn = New OleDbConnection(sConnString)
                            conn.Open()
                            cmd.Connection = conn
                            cmd.CommandType = CommandType.Text

                            sSQL = "UPDATE tblSBTHist SET [Voided] = @Voided"
                            cmd.CommandText = sSQL

                            'set paramaters
                            cmd.Parameters.Add("@Voided", OleDbType.DBDate).Value = DateAndTime.Now
                            cmd.ExecuteNonQuery()

                        Catch ex As Exception

                            MsgBox(ErrorToString)

                        Finally

                            conn.Close()

                        End Try

                    Else

                Me.Close()

                    End If

                Else
                    MsgBox("Password incorrect.", MsgBoxStyle.Exclamation, "Supervisor Password")
                    txtSPwd.Text = ""
                End If

            End With

        Catch ex As Exception

        End Try

    End Sub

    
End Class

Open in new window

result2This is putting the current date in to the correct column but it's populating ALL of the rows with the current date.

The code should only populate the row that the user selects with the current date.

Would you know how I can accomplish this?

Kindly advise.

Regards,
N
0
KevinAuthor Commented:
Forgot to answer your question. The columns were created at design time.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Bob LearnedCommented:
The query doesn't have a WHERE clause:

UPDATE tblSBTHist SET [Voided] = @Voided
0
KevinAuthor Commented:
Hi Bob,

Can you be a little more specific please?

I can put a 'WHERE' in there:

UPDATE tblSBTHist SET [Voided] = @Voided  WHERE.......

But how would I code the rest of it for the users selection in the datagridview?

I don't know how to accomplish this, this is why I am asking.

Kindly advise.

Regards,
N
0
Bob LearnedCommented:
What is the primary key for that row, so that you can get the value from the DataGridView, and use it in the WHERE clause?
0
KevinAuthor Commented:
Hi Bob, the primary key is "SBTChqId" as see in above screenshot.
0
CodeCruiserCommented:
SBTChqID is not one of the grid columns. Is there another column like ChqNum that has unique values or can you add SBTChqID column but make it invisible? Assuming that you add the primary key column, you can update your code to look like below


    Private Sub btnSPwdOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSPwdOK.Click

        Try

            With My.Computer.Registry
                Dim strGetValue As String
                strGetValue = .GetValue(gRegKeyName, "SupervisorPwd", "")
                If txtSPwd.Text = strGetValue Then
                    If MsgBox("Are you sure you want to void the selected cheque?" & vbNewLine & "This process cannot be undone.", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo, "WARNING") = MsgBoxResult.Yes Then

                        Dim conn As New OleDbConnection
                        Dim cmd As New OleDbCommand
                        Dim sSQL As String = String.Empty

                        Try
                            'get connection string declared in the modFunctions.vb and assign it to conn variable
                            conn = New OleDbConnection(sConnString)
                            conn.Open()
                            cmd.Connection = conn
                            cmd.CommandType = CommandType.Text

                            sSQL = "UPDATE tblSBTHist SET [Voided] = @Voided WHERE SBTChqID=@PrimaryID"
                            cmd.CommandText = sSQL

                            'set paramaters
                            cmd.Parameters.Add("@Voided", OleDbType.DBDate).Value = DateAndTime.Now
                            cmd.Parameters.AddWithValue("@PrimaryID", Datagridview1.SelectedRows(0).Item("SBTChqID"))
                            cmd.ExecuteNonQuery()
                        Catch ex As Exception
                            MsgBox(ErrorToString)
                        Finally
                            conn.Close()
                        End Try
                    Else
                        Me.Close()
                    End If
                Else
                    MsgBox("Password incorrect.", MsgBoxStyle.Exclamation, "Supervisor Password")
                    txtSPwd.Text = ""
                End If
            End With
        Catch ex As Exception
        End Try
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinAuthor Commented:
Hi Code Cruiser,

Thank you for your comment. Actually "SBTChqID" is one of the grid columns as I have it hidden (see line 35)
Imports System.Data.OleDb
Imports System.IO

Public Class frmSBTHist

    Private Sub frmSBTHist_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim conn As New OleDbConnection
        Dim da As New OleDbDataAdapter
        Dim ds As DataSet
        Dim tables As DataTableCollection
        Dim SBTHistSrc As New BindingSource

        'Try catch block for when no history exists

        Try

            'get connection string declared in the modFunctions.vb and assing it to conn variable

            conn = New OleDbConnection(sConnString)
            conn.Open()
            ds = New DataSet
            tables = ds.Tables
            da = New OleDbDataAdapter("Select * from [tblSBTHist]", conn)
            da.Fill(ds, "conn") 'Database name
            Dim view As New DataView(tables(0))
            SBTHistSrc.DataSource = view
            dtgSBTHist.DataSource = view

            'Displays with leading 0's in DGV
            dtgSBTHist.Columns("ChqNum").DefaultCellStyle.Format = "000000"
            'Displays as a fixed number with 2 decimal places
            dtgSBTHist.Columns("Amount").DefaultCellStyle.Format = "N2"
            'Hides ID Autonumber Column
            dtgSBTHist.Columns("SBTChqID").Visible = False

            If dtgSBTHist.Rows.Count = 0 Then
                MsgBox("No records found!")
                Me.Close()
            End If

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try

    End Sub

    Private Sub btnSBTHistClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSBTHistClose.Click

        Me.Close()

    End Sub

    Private Sub dtgSBTHistVoid_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtgSBTHist.DoubleClick

        frmSuperPwdVoid.ShowDialog()

    End Sub

End Class

Open in new window


I added your additions to my code, but was getting an error with the parameters for the Primary ID. the errors saying "Item is not a member of 'System.Windows.Forms.DataGridViewRow'. I think you might have just mistyped.

I was able to fix it by changing the below from:

sSQL = "UPDATE tblSBTHist SET [Voided] = @Voided WHERE SBTChqID = @PrimaryID"
cmd.CommandText = sSQL

'set paramaters
cmd.Parameters.Add("@Voided", OleDbType.DBDate).Value = DateAndTime.Now
cmd.Parameters.AddWithValue("@PrimaryID", frmSBTHist.dtgSBTHist.SelectedRows(0).Item("SBTChqID"))
cmd.ExecuteNonQuery()

Open in new window


To this:

sSQL = "UPDATE tblSBTHist SET [Voided] = @Voided WHERE SBTChqID = @PrimaryID"
cmd.CommandText = sSQL

'set paramaters
cmd.Parameters.Add("@Voided", OleDbType.DBDate).Value = DateAndTime.Now
cmd.Parameters.AddWithValue("@PrimaryID", frmSBTHist.dtgSBTHist.SelectedRows(0).Cells("SBTChqID").Value)
cmd.ExecuteNonQuery()

Open in new window


It's working now.

Thank you so much for your help.

Kind Regards,
N
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.