• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

need pointers on working with a dataview

I'm fairly new to Visual Basic (i'm using VS.NET 2003 by the way)... and I'm not grasping how to make a dataview useful .... here's my situation:  I've got a dataset populated with the data I need it to have ... no problem there.  I've got my data grid on the form and ready to go, and actually, before i started looking at the dataview class, i just set the data source of the datagrid to the dataset ....but when i saw the properties on the dataview class, i think it might be useful for what i'm doing .... but i keep hitting a dead end for some reason ...

here's the code i have thus far:  
************************************************
Private Sub frmAcctTypeSetup_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sqlconn As New OleDb.OleDbConnection("file name = conn.udl")
        Dim sqlstring As String
        Dim olecmd As New OleDb.OleDbCommand
        Dim oledata As New OleDb.OleDbDataAdapter
        Dim dsAcctTypes As New DataSet
        sqlconn.Open()

        sqlstring = "select AcctTypeName as 'Account Type Name', AcctTypeLife, Enabled from AcctType"
        olecmd = New OleDb.OleDbCommand(sqlstring, sqlconn)
        oledata = New OleDb.OleDbDataAdapter(olecmd)
        oledata.Fill(dsAcctTypes, "AcctType")

        dvAcctTypes = New DataView(dsAcctTypes.Tables(0))
        dgAcctTypes.DataSource = dvAcctTypes
*******************************************************

Now my question is:  How can I make this DataView useful & am I even working with it correctly in my code above?!?   Thanks!   I'll jack up the points a little since all you that help out there, really give a grade A effort when doing so.   That's always been my experience anyway!
0
trs28
Asked:
trs28
  • 7
  • 4
1 Solution
 
Alexandre SimõesManager / Technology SpecialistCommented:
I didn't understood what's your problem... the data doesn't show? what?

Cheers,
Alex
0
 
trs28Author Commented:
it's not that i have any sort of problem per se ... i'm just not familiar with dataviews & I'm not sure how to write code that will utilize the usefulness of them ... things like setting create, edit, delete properties, and well, basically anything else that someone can do with a dataview to make it functional and useful ...

0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Ok then...
First of all I think you should read this:
http://www.thescripts.com/forum/thread378649.html

After that you know that a DataView is always connected with a DataTable and that it add dome functionalities to the DataTable object.
This said you can understand that the DataView object is the presentation part of the DataTable structure and even if you don't use it explicitly it's there and the controls use it by default.

To modify data you have to use the DataTable object, not the DataView.
There you have the Rows collection where you can access the cells value giving the row index and column name.

I hope this helped,
Cheers,
Alex
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
trs28Author Commented:
i've gone through a lot of what you said, and that all makes sense to me ... but here's the issue I'm having ...

when I sort, I'll use something like
dvAcctTypes.Sort = "AcctTypeLife"

...everything sorts beautifully, but if the user double clicks on the datagrid, I need to be able to retrieve that record and display data from it in an opening form.  Normally, that's no problem ... but when I've sorted the view, the underlying dataset is ordered differently ... thus, i'm understanding it this way (via an example):

dsAcctTypes contents
****************************
[AcctTypeName],[AcctTypeLife],[Enabled]
VIP,  365,  1
Silver,  186,  1
Guest,  7,  1
Quarterly,  90,  1

(and if i do a sort on the AcctTypeLife field, the dataview will display the following in the grid....)

Guest,  7,  1
Quarterly,  90,  1
Silver,  186,  1
VIP,  365,  1

...so if the user then wants the view the info the for the "Guest" line & double clicks on it, the form populates with the VIP data ....  how do i keep those two in sync so that I can pull up the correct record, regardless of the view's sort order?    ...or rather, how would i achieve what I'm trying to do?  I've tried sorting the dataset itself (via dsAcctType.Tables("AcctType").DefaultView.Sort = "AcctTypeLife") but I'm not sure that's working or even what I want to do b/c the grid doesn't seem to update automatically ...  

Thanks for all your help!

- Travis -
0
 
trs28Author Commented:
another question too, if i'm committing any changes via an update command to the database, would I just want to modify the data via the dataset (or dataview) so that it would reflect the changes automatically (either by inserting or editing a line depending on the conditions)?

presently, once an update is sent to the server, the application triggers another search, which re-queries the database and changes the order of the dataset coming in ... thus, i have no chance at all of reselecting the record just worked with once the modify form closes ....  

I've tried using the dataview's find method, but that's gotten me absolutely nowhere too .... thanks again!

I'll jack up the points a little to make it a little more worthwhile for ya!   :)
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Ok... for the sorting issue and if you're on a Form you may use:

Me.BindingContext(dvAcctTypes).Current
This will retrieve you a DataRowView object representing the active record.
If you need the row position on the datasource you can also use:
Me.BindingContext(dvAcctTypes).Position



For the 2nd issue I'll be a little more descriptive.
By definition INSERT, UPDATE and DELETE commands modify you data.
On UPDATE and DELETE you already know the ID of the record you're modifying, on INSERT you usually don't. ID columns are usually identities so only when the INSERT is committed you know the ID it has been given.
It's a best practice to return that Id back to code or in some cases returns the whole row.
If you have the row Id you can then select it again on the grid using the DataView and the BindingContext.

For example, if you want to execute the Find method on a DataView object you must specify a Sort on the column you want to search. This is the way the DataView will know in which column to look.
The Find method returns the first row index that matches you query.
You can then use this index to set he BindingContext Position property.

Something like:

Private Sub FindValueInDataView(table As DataTable)
    Dim view As DataView = New DataView(table)
    view.Sort = "CustomerID"

    ' Find the customer named "DUMON" in the primary key column
    Dim i As Integer = view.Find("DUMON")
   
    Me.BindingContext(dvAcctTypes).Position = i
End Sub

As you have a sort applied on the grid this "internal" sort on the view won't be reflected to the user.

This code was taken from:
http://msdn2.microsoft.com/en-us/library/46d41xk2.aspx
refer to this link if you need more info about DataView.Find Method.


I hope this helped,
Cheers
0
 
trs28Author Commented:
could you give me a little help on this data binding concept?!?  To be honest with you, I'm a fairly new programmer when it comes to VB.NET.     ....so not only am I just getting used to how views work in conjunction with their datasource, but i'm also trying to implement data binding simultaneously ...and if you could just walk me through a quick but effective example (can be completely unrelated), i think it'd really help a lot (and if you could include some code too, that'd be superb)    ....it's frustrating, especially when i'm so close to making all the connections!!    

I've got the idea behind the double click thing, but i hate to admit it, but it would REALLY help if you gave me a nudge there too .... i know i'd get it, but i have a boss that likes to sell software that's not ready for release every now and again, and it wasn't long ago that I had this VB nightmare dropped on my lap!

Thanks again!   Sorry to be a "needy" asker!
0
 
trs28Author Commented:
forget that last post ... i think i've connected almost all of the dots, but if you answer me this, i think it'll all fall together for me, and i'm going to refer back to the code you suggested (which worked nicely) ....

i created a dummy project and threw a datagrid on a form ....
*****************************************************
Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim sqlconn As New OleDb.OleDbConnection("file name = C:\\sid.udl")
    Dim dsCustomer As New DataSet
    Dim oledata As OleDb.OleDbDataAdapter
    Dim sqlstring As String
 
 Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFill.Click
        sqlstring = "select finger_id, firstname, lastname from customer"

        oledata = New OleDb.OleDbDataAdapter(sqlstring, sqlconn)
        oledata.Fill(dsCustomer, "Customer")

        DataView1 = New System.Data.DataView(dsCustomer.Tables("Customer"))
        DataGrid1.DataSource = DataView1
    End Sub

    Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        FindValueInDataView(dsCustomer.Tables("Customer"))
    End Sub

    Private Sub FindValueInDataView(ByVal table As DataTable)
        Dim view As System.Data.DataView = New System.Data.DataView(table)
        Dim i As Integer

        view.Sort = "finger_id"
        i = view.Find("439")   'this is the "Test A" account

        Me.BindingContext(DataView1).Position = i
    End Sub
End Class
*******************************************************

Here's my question .... how would i locate the record, if say for example, the sqlstring value was "select finger_id, firstname, lastname from customer order by lastname"   ????   Under something like that, I would assume that I just change the sort order first.... but what if, before that "FindValueInDataView" procedure, a record was modified and a person's last name was changed due to a typo, what approach would i take to do these 3 things?   ...  1 - update the grid w/ any changed data, 2 - locate the record in the grid that was just modified, and 3 - double click on another record, but ensuring that the dataview will pull up the correct record from the dataset?  

I know you JUST went over a lot of this, and it's been helpful so far ... i'm almost there, but i think if i ask again, based off of AlexCode's suggestion and the code I put here, it should all fall together for me perfectly ....

THANKS EVERYBODY!!   I APPRECIATE THE HELP !!
0
 
trs28Author Commented:
even though i haven't figured out what I was trying to, your suggestions helped, so I'll just repost the tail end of this problem in a new question
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hey, sorry I was over my head for a while...

Do you still got problems?
Maybe you still need an example for that last comment you add?
0
 
trs28Author Commented:
Thank you very much for responding :)   Another example would be wonderful!   I'm so close to connecting the dots but there doesn't seem to be too much info on how to handle my problem out there, and well my friend, another example would be much appreciated!  ... especially regarding the "double-click issue" i'm having and considering i'm only on VS.NET 2003.

Thank you VERY much !!  

- Travis
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now