Selecting a record from Data Grid

[Michaelcree]

Hi

I am currently trying to learn VB.NET 2005. I have a data grid and would like to select a record in that grid and open a form displaying that records details

Is this posible and how?

Thanks

 

[Kalisto]

Um,off the top of my head, add a Select Button column to the grid

In the Codebehind add a method to catch the

Datagrid.SelectedIndexChanged event

from there, extract the data (the Sender and arguments should have all the information you need)

 

[bburnell]

Add a button to your datagrid and double click on it. Add the following code:

    Private Sub dgDataGrid1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgDataGrid1.CellContentClick
        '*** If user clicks on the column or row headers, ignore and exit ***
        If (e.ColumnIndex < 0) Or (e.RowIndex < 0) Then
            Exit Sub
        End If

        '*** If user clicks on the "EDIT" button, proceed to View/Edit screen ***
        If (e.ColumnIndex = 0) Then
            ViewGUID = dgDataGrid1.Rows.Item(e.RowIndex).Cells(1).Value.ToString.Trim
            ViewQuoteNumber = dgDataGrid1.Rows.Item(e.RowIndex).Cells(4).Value.ToString.Trim
            ViewSerialNumber = dgDataGrid1.Rows.Item(e.RowIndex).Cells(6).Value.ToString.Trim
            ViewProductionPlant = dgDataGrid1.Rows.Item(e.RowIndex).Cells(8).Value.ToString.Trim
            Console.Write("Preparing to View/Edit: " & ViewGUID & " - " & ViewSerialNumber & " - " & ViewProductionPlant & vbCr)
            Me.Hide()
            frmViewEdit.Show()
            My.Application.DoEvents()
        End If
    End Sub

The index values go from 0 to whatever the last field # is (in my example 8). I used column "0" as my edit button. You could also use a loop to get the data "from 0 to count" or something similar...

I hope this helps!
Brett

 

[Michaelcree]

Thanks for this Bret.

OK I know this is going to sound like a silly question but bear in mind I am only just started getting my head around this stuff!

I create a form with txt fields matching the ones on the datagrid that the button on the datagrid then opens?

Michael

 

[woogoo]

Hi,

What I usually do here is have a hidden column on the datagrid that contains the record's Primary Key value.

Then, when the user double clicks or uses a context menu (i.e. Update, Print, Delete) this number is looked up using (MyGrid.CurrentRow.Cells(0).Value()) and passed to the opening form's constructor. This number is then passed to a stored procedure to retrieve the record from the database with the result displayed in the controls on the form.

By adopting this approach I can Delete, Print, Update or take any applicable action on a single row, if you enable multi select then you can do the required action on each of the selected rows.

Hope this helps.


Hope this helps.

 

[Michaelcree]

Is there an example database where I can see this in action so I can get a better understanding?

 

[bburnell]

Create a new form and pull a datagrid control onto it. Then use the Northwind database to connect to. From there, play with the controls and you should see what we mean. If not, I will try to create one with the sample database and post it here.
Brett

P.S. Here is Northwind in case you don't already have it!

http://www.microsoft.com/downloads/...12-0356-46a0-8da2-eebc53a68034&displaylang=en

 

[Michaelcree]

Hi
Sorry about this!
I have used the pubs database to create a datagridwiew and placed a button on the bottom of that form then created another form with some text boxes on but still cannot see how to link the selected record on the datagrid and populate the txt fields on the form with that record. Hope that makes sense

Michael

 

[PsychoCoder]

Michaelcree,

You could also use a DataGridView and obtain what you are looking for with the DataGridView1_CellClick, then inside that procedure to get the value of your field containing the primary key use

DataGridView1.CurrentRow.Cells(0).Value.ToString

And this will return the value of yuor primary key (given taht your key is in the first cell)

Senior Qik III, ASP.Net, VB.Net ,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM Users WHERE clue > 0
0 Rows Returned

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[bburnell]

So you are trying to pull data from a datagridview on one form to another with text boxes on it? Are you trying to pull over the whole dataset or just one row? If it's one row you are trying to pull over, how are you telling the "form" what row you want? I'm trying to figure out what you want. Sorry.
Brett

 

[Michaelcree]

Hi Bret

It is just the data from one row in the datagridform view(Which ever record that is selected by the user)to pull onto the form and display.

It is how you get the textboxes on the form to display the same information as in the datagrid that I am stuck on.

Michael

 

[bburnell]

Hi Michael,
Double click on the datagrid and put the code in from my first post. Except, you have to change the names of your fields and use the cell(column #) to retrieve the data. The row # should be set by the mouse click. You can then retrieve the data as a global variable or as <form name>.<field name>. You are getting the data successfully in the datagrid though, right?
Brett

 

[PsychoCoder]

Michaelcree,

What I would do is this:

I would use a DataGridView as stated above, then do a query on the database based on the row selected (DataGridView1.CurrentRow.Cells(0).Value.ToString) then with the return information (use a function that returns a DataSet like:

Public Function BindBugList(ByVal dgvBugs As DataGridView) As DataSet
    sSqlProc = "Procedure_Name_Here"
    oCommand.Parameters.Clear()
    With oCommand
        .CommandText = sSqlProc
        .CommandType = CommandType.StoredProcedure
        .Connection = YourConnectionObject
    End With
    daBugs.SelectCommand = oCommand
    daBugs.Fill(dsBugs)
    Return dsBugs
End Function

Then on Form1 do:

'Form2 = your form name
'dsBugs = your DataSet name
Form2.TextBox1.Text = dsBugs.Tables(0).Rows(0).Item("Field_Name").ToString
Form2.TextBox2.Text = dsBugs.Tables(0).Rows(0).Item("Field_Name").ToString
....... and so on .......

This should do what you're looking for.

Senior Qik III, ASP.Net, VB.Net ,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM Users WHERE clue > 0
0 Rows Returned

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[bburnell]

Yeah, this is probably what you want if you want the whole row. My example is only pulling 3 or 4 items off the row, not the whole row.
Brett

 

[Michaelcree]

Thanks you both for this I will give it a go and see how I get on.

Could I ask you one more question? Not how to do something but I think it may be a case of what is best practice.

When I have developed Access databases as a person opens a record I have written the user name date and time they have opened the record to a table and when they close the records the time they closed it to prevent two users from accessing the same record and making changes at the same time. With .NET I have read that this is not the best way of doing things. Do you think this is still a valid way of doing record locking or what is the best way to lock a record whilst it is being edited?

Michael

 

[bburnell]

Michael,

This isn't my specialty, but I'm pretty sure there is a way to lock the data withing .net. Maybe by data binding? You may have to get another expert on this. Sorry...

Brett

 

[jbenson001]

What you have suggested for record locking is the only way I know of to keep 2 people from modifing the same row of data.

 

[PsychoCoder]

Michaelcree,

I kind of worked on your question last night and came up with something that might work for you. When 2 users (or more) concurrently change regords at the same time in Access it causes a DBConcurrencyException, I came up with this solution that might help you with your quest. First is a procedure for reading the data

Public Overloads Overrides Function ReadData() As DataTable
 Me.Table = New DataTable(Me.TableName)
 Me.Table.Locale = CultureInfo.InvariantCulture
 Try
   daDataAdapter.Fill(Me.Table)
   Return Me.Table
 Catch ex As OleDbException
   Me.CurrentException = ex
   Return Me.Table
 End Try
End Function

Then this procedure for saving the data

Public Overloads Overrides Sub SaveData(ByVal dataTable As DataTable)
 Me.Table = dataTable
 Dim trTransaction As OleDbTransaction = Nothing
'Save the data
 Try
   'Set up the conection manually
   InitializeConnection
   cnConnection.Open
   'Begin a transaction
   trTransaction = cnConnection.BeginTransaction
   'Make all database changes
   Me.AffectedRecords = daDataAdapter.Update(Me.Table)
   'Commit the changes
   trTransaction.Commit
 Catch ex As DBConcurrencyException
   'An DBConcurrency error occurred, so we roll the transaction back
   Me.CurrentException = ex
   trTransaction.Rollback
 Catch ex As OleDbException
   'An error occurred, so we roll the transaction back
   Me.CurrentException = ex
   trTransaction.Rollback
 Finally
   'Close the connection that we manually opened
   trTransaction = Nothing
   cnConnection.Close
   cnConnection = Nothing
 End Try
End Sub

Along with the following class file

Imports System
Imports System.Data
Namespace MultiTier.Data

 Public MustInherit Class Data
   Private dtDataTable As DataTable
   Private exException As Exception
   Private lngAffectedRecords As Long
   Private strConnectionString As String
   Private strTableName As String

   Private Sub New()
   End Sub

   Protected Sub New(ByVal connection As String)
     Me.ConnectionString = connection
     Me.ConstructDataAdapter
   End Sub

   Protected Sub New(ByVal connection As String, ByVal tableName As String)
     Me.ConnectionString = connection
     Me.TableName = tableName
     Me.ConstructDataAdapter
   End Sub

   Protected Property Table() As DataTable
     Get
       Return dtDataTable
     End Get
     Set
       dtDataTable = value
     End Set
   End Property

   Public Property ConnectionString() As String
     Get
       Return strConnectionString
     End Get
     Set
       strConnectionString = value
     End Set
   End Property

   Public Property TableName() As String
     Get
       Return strTableName
     End Get
     Set
       strTableName = value
     End Set
   End Property

   Public Property AffectedRecords() As Long
     Get
       Return lngAffectedRecords
     End Get
     Set
       lngAffectedRecords = value
     End Set
   End Property

   Public Property CurrentException() As Exception
     Get
       Return exException
     End Get
     Set
       exException = value
     End Set
   End Property

   Protected MustOverride Sub ConstructDataAdapter()

   Public MustOverride Function ReadData() As DataTable

   Public MustOverride Sub SaveData(ByVal dataTable As DataTable)

   Public MustOverride Sub InitializeConnection()
 End Class
End Namespace

Hope this helps or at least puts you on the right track to solving your problem.

Senior Qik III, ASP.Net, VB.Net ,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM Users WHERE clue > 0
0 Rows Returned

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~