Opening a form based on DataGridView selection.

[agroom]

I've got a form with a datagridview. I setup a ContextMenuStrip that the user can right click on a hilited row and view additional information about that entry (based on the primary key) in another form that pops up.

The code I have for this works but here's where i see an issue. The form has just a single RichTextBox and a Close button. It displays additional notes about the selected entry. Databindings to the textbox are setup at design. The problem I see is that I'm only going to disply one record and there's no option for the user to browse other records, so I shouldn't need to fill the table adaptor with the entire dataset. Is there a way I can load it with just a single record?

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

' This is what I think it can be cleaned up
Me.TblNotesTableAdapter.Fill(Me.NotesDataSet.tblNotes)

'This filters the data so that the correct record is left to view
Me.NotesBindingSource.Filter = "fldProjectNum = '" & FindNotes & "'"

'If the record does not exist, then a new record is added
If Me.NotesBindingSource.Count = 0 Then
Me.NotesDataSet.tblNotes.AddtblNotesRow(FindNotes, "")
End If

End Sub

 

[jmcilhinney]

You need to add a new query to your TableAdapter to get just the record you want. Open the DataSet in the designer, right-click the TableAdapter and select Add Query. Open the Query Desinger and add a filter to your ID column that is the name of the column preceded by an "@" symbol. When you leave that cell the SQL code should update to include a WHERE clause something like:

WHERE (ID = @ID)

When it comes time to name your query you should use the the name of the parameters, so you would use FillByID and GetDataByID as the method names. Now, instead of calling Fill in your code you can call FillByID and provide the ID value of the record for which you want to get the data. Note also that if all you're getting is that one record then don't use a DataSet at all. Call GetDataByID instead, which will return a DataTable without the overhead of the DataSet.

 

[cjard]

The problem I see is that I'm only going to disply one record and there's no option for the user to browse other records, so I shouldn't need to fill the table adaptor with the entire dataset. Is there a way I can load it with just a single record?

You dont fill a table adapter with a dataset, you use a tableadapter to fill a datatable from a data base.. As the chinese proverb says; "The first step to wisdom is by calling things their proper names"

 

[agroom]

Note also that if all you're getting is that one record then don't use a DataSet at all. Call GetDataByID instead, which will return a DataTable without the overhead of the DataSet.

I created a new query and it works fine by calling .FillByProjectNum (name I gave it). But I don't understand how I can do it without a dataset. One of the parameters for .FillBy... is a datatable, but I have to use the dataset to reference it:

Me.TblNotesTableAdapter.FillByProjectNum(Me.NotesDataSet.tblNotes, FindNotes)

So how do I reference the datatable without the dataset? You'll have to excuse me if this sounds noobish because I am .

 

[agroom]

I've also got another issue now. Below is the code for my close and form load. On close, i want it to auto-save any changes that have been made without prompt. When I open an existing note and edit it, I get an error on the following line:

Me.TblNotesTableAdapter.Update(Me.NotesDataSet.tblNotes)
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

I use this same code to update in other places in my program though, so I'm not sure why it's not working here. Also, if I create a new note, modify its contents and close, it works fine and saves.

Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
        Me.Validate()
        Me.NotesBindingSource.EndEdit()
        Try
            Me.TblNotesTableAdapter.Update(Me.NotesDataSet.tblNotes)
        Catch ex As Exception
            MsgBox("Sorry, an unexpected error has occured")
            Exit Sub
        End Try
        Me.Close()
End Sub

Private Sub frmNotes_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Form_Resize()
        Me.TblNotesTableAdapter.FillByProjectNum(Me.NotesDataSet.tblNotes, FindNotes)
        If Me.NotesBindingSource.Count = 0 Then
            Dim AddNote As MsgBoxResult
            AddNote = MsgBox("No notes for selected log entry.  Do you wish to add a new note?", _
            MsgBoxStyle.YesNo, "Record Not Found")
            If AddNote = vbYes Then
                Me.NotesDataSet.tblNotes.AddtblNotesRow(FindNotes, "")
            Else
                Me.Close()
                Exit Sub
            End If
        End If
        Me.Text = "Notes for Log Entry " & FindNotes
        Me.Visible = True
End Sub

Thanks in advance for any help!

 

[jmcilhinney]

I created a new query and it works fine by calling .FillByProjectNum (name I gave it). But I don't understand how I can do it without a dataset. One of the parameters for .FillBy... is a datatable, but I have to use the dataset to reference it:

Me.TblNotesTableAdapter.FillByProjectNum(Me.NotesDataSet.tblNotes, FindNotes)

So how do I reference the datatable without the dataset? You'll have to excuse me if this sounds noobish because I am .

I love it when people read half a post and then ask a question that's answered by the half they didn't read. In my previous post I specifically address this question. When a query is added to a TableAdapter two methods are created: one to populate an existing DataTable and one to create a new DataTable. The default query is exposed via the Fill and GetData methods. Your new query should have been exposed via the FillByProjectNum and GetDataByProjectNum methods if you created it properly. In my last post I specifically stated that you should call the GetData method, which returns a DataTable, instead of the Fill method, which requires an existing DataTable.

 

[cjard]

I created a new query and it works fine by calling .FillByProjectNum (name I gave it). But I don't understand how I can do it without a dataset. One of the parameters for .FillBy... is a datatable, but I have to use the dataset to reference it:

Me.TblNotesTableAdapter.FillByProjectNum(Me.NotesDataSet.tblNotes, FindNotes)

So how do I reference the datatable without the dataset? You'll have to excuse me if this sounds noobish because I am .

Oh.. erm. Let me explain a few things:

A DataSet is a collection of DataTables. Within a typed dataset you have named access to the data tables. In a non typed dataset it behaves more like a classic collection that is indexed by nuimbers or strings:

myTypedDataSet.tblNotes
myUntypedDataSet.Tables("tblNotes")


Both these lines of code return some form of datatable. When using a Typed dataset, we get a typed DataTable back. Specifically the type of the data table in the former case is that of tblNotesDataTable
In the latter case, we just get a generic DataTable back

The advantage of using typed vs untyped objects is that we can get things right at design time and also be specific about what we want our objects to be. You might not notice if you have a setting called Option Explicit turned off but if it is turned on (recommended as it encourages precise and clear thinking hence better programming) the following two statements, only one would work:

'increment SomeIntegerColumn in the first row of the table, by 1
myTypedDataSet.myTypedDataTable(0).SomeIntegerColumn += 1
[U]myUntypedDataSet.Tables("myUntypedDataTable").Rows(0).Item("SomeIntegerColumn") += 1[/U]
[U]

[/U]

The second item fails with a wiggly line because everything on the left of the += is a path to an object that is simply of type Object
It is not possible to add 1 to an object - Object could be Anything, a string, a textbox, an HTTPDataStream etc..

To get the latter code to work would require this, more messy code:

DirectCast(myUntypedDataSet.Tables("myUntypedDataTable").Rows(0).Item("SomeIntegerColumn"), Integer) += 1


i.e. we would have to remember that the item is an integer, and we have to cast it before the compiler will be satisfied we know what we are doing

So the lessons?

Typed is better then untyped. Always try to use typed objects
(typed) DataSets are collections of (typed) DataTables
DataSets also hold and maintain relationships and these are useful later
(typed) DataTables are collections of (typed) DataRows
DataRows are like arrays - fixed number of cells

Typed stuff (generated by the IDE at least) usually has nice things like named properties you can use to make your code neat, readable and self-documenting.



Now to the next part of your question:

When you drag and drop a datatable out of the Data Sources window in the IDE< and drop it on a form, the IDE investigates what dataset that table is a member of. it is the entire DataSet that is added to the form, and the controls are linked to the dataset but they have an additional property set called DataMember - this is a string property that points the component to the right table in the set.

So your form has a DataSet on it rather than a DataTable. This is necessary because if you have things on your form, likle customer details and all that customer's order info then you *want* them to be there as part of a dataset. that way, any relations you have drawn into the set get activated and used..

Thus, we are happy that a DataSet is a collection of DataTables, and we are also happy that our form has a DataSet on it..



We come to the point about having to use a DataSet in the fillby, whereas I said you have to use a datatable..

In your code:
Me.NotesDataSet.tblNotes

Me refers to the form
.NotesDataSet refers to the dataset on the form
.tblNotes refers to the tblNotes DataTable within the dataset


So dont worry.. we are still passing a datatable to the fillby, the dataset keeps hold of it too, and when FIll has finished it is full of data, still within the dataset, on the form and accessible in your form's code

You naturally have to use the dataset because thats the only thing you have a reference to, that has a reference to the datatable you want to fill

I find it helps me to picture objects as balls, and references asbits of string with a tag on the end. I have a form-ball and stuck to it is a bit of paper with NotesDataSet written on. Tied to the paper is a bit of string that leads to another ball that represents my dataset. that too has several bits of paper one of which says tblNotes, and that paper is strung to the notes table ball:

(Form object called [B]Me[/B])[NotesDataset]---------(A NotesDataSet object)[tblNotes]--------(a tblNotesDataTable object)

If i said in the main form code:
Dim x as tblNotesDataTable = Me.NotesDataSet.tblNotes

i would have this:

(Form object called Me)[NotesDataset]---------(A NotesDataSet object)[tblNotes]--------(a tblNotesDataTable object)
                                                                                           |
(Form object called Me)[x]-----------------------------------------------------------------+

a bit of paper tag with X written on, tied directly to the tblNotes datatable..
I could pass that in to the fillby and it would fill the same dataset

 

[cjard]

I've also got another issue now. Below is the code for my close and form load. On close, i want it to auto-save any changes that have been made without prompt. When I open an existing note and edit it, I get an error on the following line:

Me.TblNotesTableAdapter.Update(Me.NotesDataSet.tblNotes)
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

I use this same code to update in other places in my program though, so I'm not sure why it's not working here. Also, if I create a new note, modify its contents and close, it works fine and saves.

Open the DataSet designer surface and click on the box headed tblNotesTableAdapter

In the properties box you will see the props of the table adapter.

It has a Select command, an Insert, Update and Delete commands


When you make a new note and add it to the datatable, subsequently calling Update() will cause the INSERT command to be used to send the new note to the database

If you change an existing note, the UPDATE command is used to return the changes to the database.

The error message:
Update requires a valid UpdateCommand when passed DataRow collection with modified rows

means that the tableadapter HAS NO (FUNCTIONING) UPDATE command. Insert cannot be used to update data.


Do not confuse the .Update() method of a table adapter with an update statement - they are different things. If I were microsoft I would have called the method .Persist() instead to indicate that the method is used for sending changes to the back end data store

If your TA has no..

..DELETE command, then you will get an error if you delete a row from the datatable..
..UPDATE command, then you will get an error if you modify a row in the datatable..
..INSERT command, then you will get an error if you add a row to the table..
​

..and then attempt to call an Update() on the table adapter.


The error message is reasonably self explanatory, but I'll admit its confusing to see the word update mentioned twice, with different meanings

 

[cjard]

Do please note Mr McIlhinney's advice; the reasoning is sound, and because you appear to be forging your own relationships rather than having a DataRelation manage them for you, you might well be better off heeding his advice of GetData rather than Fill

Using either Fill or GetData will leave you with the goal of having a datatable containing data, that can be linked to a view control like a datagrid. If you were using relations I'd advocate Fill, if you are not using them then either method is suitable, and GetData is the easier one to understand if youre not firm in thinking in OO terms

 

[agroom]

Wow, that was a load to digest, but extremely helpful! Things are starting to make sense now...scarry! hehe I guess my problem comes with not knowing VS2005 well enough to know that I needed to assign an update command to update. I kinda figured it was setup automatically when I bound the control at design. I wouldn't have expected this had I setup everything at runtime.

I keept my dataset becaue I am going to setup future relationships, but I also was able to create an update command to get it working

I greatly appreciate everyones help!!!