Solved

Alternate Datagrid input in VB.NET

Posted on 2004-03-29
15
982 Views
Last Modified: 2007-12-19
I am using VB.Net and ADO.Net to create a database application.  I need help to create an alternate method to edit or add data rows on a datagrid that was filled from a dataset.  I don’t want the users to edit the datagrid directly.  I basically want an 'edit form' to appear when the user double clicks on a row.  This form must automatically be filled with the values from the datagrid row that was clicked.  This form must allow the user to edit the row values and when done the user will click on a save button on the form which must then close the form and then update the datagrid with the changes.  Similarly I need an add button on the datagrid’s form that will open a blank 'edit form' and allow the user to input a new row that will update the datagrid.  

Any suggests would be appreciated.
0
Comment
Question by:terrenceg
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 4

Accepted Solution

by:
Jarodtweiss earned 500 total points
Comment Utility
There is no Double Click event on a row for a datagrid.
What I have done is creating my own custom datagrid and overring the OnMouseDown event.
In this method, you can check if it is a double click or not (You can check in the SystemInformation class the parameter (time and size) for the user to consider two clicks as a double click)
Then you can raise your own event and handle it to display your edit form.

To display the values from the selected line, you can do :
(I assume your datasource is a dataview --> you can also work with the CurrencyManager)

dim myView as DataView = ctype(me.dataGrid1.DataSource, DataView)
dim myRow as DataRowView = myView.item(me.dataGrid1.CurrentRowIndex)

and use myRow to get all the info to display in the edit form.

For the Addition of a new line, just remove the blank line at the end :
  myView.AllowNew = false
(you have to do it on your datasource and not on the datagrid itself)
and foresee a Add button.

To update the datagrid, just update the datasource.

myRow = myView.AddNew()
with myRow
  .BeginEdit
  .Ietm("myItem") = "NewValue" 'Coming from your form
  .EndEdit
end with
0
 
LVL 1

Expert Comment

by:theunknownknight
Comment Utility
1. Add a 2 button columns to your datagrid html as follows:

<asp:DataGrid id="dgPersonal" runat="server" Font-Size="X-Small" GridLines="None" OnItemDataBound="dgmyProj_ItemDataBound" DataKeyField="ProjectNum" OnItemCommand="DGCommandSet" BackColor="#EEEEEE" Width="500px" HorizontalAlign="Center">
                                            <HeaderStyle font-size="X-Small" font-bold="True" horizontalalign="Center" forecolor="White" backcolor="Black"></HeaderStyle>
                                            <AlternatingItemStyle backcolor="White"></AlternatingItemStyle>
                                            <ItemStyle horizontalalign="Center"></ItemStyle>
                                            <Columns>
                                                <asp:ButtonColumn Text="Add" CommandName="AddCommand"></asp:ButtonColumn>
                                                <asp:ButtonColumn Text="Edit" CommandName="EditCommand"></asp:ButtonColumn>
                                            </Columns>
                                        </asp:DataGrid>

2. Notice the DataKeyField above. This will most probably by the primary key of the database table your data will be stored in. In any case, this is the field by which you will be able to find your record using the where clause in your SQL statement. I have it set here to 'ProjectNum'

3. Notice OnItemCommand="DGCommandSet" . This is very important because is the commandset that will house the Add and Edit commands in your server side code. (Look at the CommandName Properties of the Button Columns. They are 'AddCommand' and 'EditCommand')

3. Go to code view. Insert this code:

Sub Personal(sender as Object, e As DataGridCommandEventArgs)
    Select Case e.CommandName
        Case "Add"
          response.redirect("AddNewProject.aspx")
        Case "Edit"
          Dim Selected as String = dgPersonal.DataKeys(e.Item.ItemIndex)
          dim i as integer = cint(selected)
          dim sq as string = "SELECT * from ProjectTable where ProjectNum= " & i
          FindSelectedProject(sq)
          'Note: You have to Write above Function to grab the record and get the selected values
          'Note: Alternate Method: You can store this values in session variables or pass the the above SQL string in a session variable and grab record on EditProject.aspx page load
          'using a function similar to the FindSelectedProject below
      End Select
End Sub

Function FindSelectedProject(sq)
    dim nrows as integer
    dim Oconnect as OledbConnection = new OledbConnection(ConnectionString)
    dim ODAdapt = New OledbDataAdapter(sq, Oconnect)
    dim DSet as dataset = new Dataset()
    dim Mytab as datatable = New DataTable
    try
        ODAdapt.Fill(DSet, "dgProject")
        MyTab = DSet.Tables(0)
        nrows = MyTab.Rows.Count
    catch exc as exception
        response.write("Sql Call Failed: Please Review Sql String - <br>" & sqlStr)
    finally
        Oconnect.Close
    end try
    If nrows = 0 Then
        response.write("No Record Found")
    Else
        session("ProjNum") = MyTab.Rows(loop1).item("ProjectNum")
        session("ProjName") = MyTab.Rows(loop1).item("ProjectName")
        session("ProjDate") = MyTab.Rows(loop1).item("ProjectDate")
        session("ProjLead") = MyTab.Rows(loop1).item("ProjectLead")
        response.redirect("EditProject.aspx")
    End if
End Function
0
 
LVL 1

Expert Comment

by:theunknownknight
Comment Utility
Sorry, change this

Sub Personal(sender as Object, e As DataGridCommandEventArgs)
    Select Case e.CommandName
        Case "Add"
          response.redirect("AddNewProject.aspx")
        Case "Edit"
          Dim Selected as String = dgPersonal.DataKeys(e.Item.ItemIndex)
          dim i as integer = cint(selected)
          dim sq as string = "SELECT * from ProjectTable where ProjectNum= " & i
          FindSelectedProject(sq)
          'Note: You have to Write above Function to grab the record and get the selected values
          'Note: Alternate Method: You can store this values in session variables or pass the the above SQL string in a session variable and grab record on EditProject.aspx page load
          'using a function similar to the FindSelectedProject below
      End Select
End Sub


to this

Sub DGCommandSet(sender as Object, e As DataGridCommandEventArgs)
    Select Case e.CommandName
        Case "AddCommand"
          response.redirect("AddNewProject.aspx")
        Case "EditCommand"
          Dim Selected as String = dgPersonal.DataKeys(e.Item.ItemIndex)
          dim i as integer = cint(selected)
          dim sq as string = "SELECT * from ProjectTable where ProjectNum= " & i
          FindSelectedProject(sq)
          'Note: You have to Write above Function to grab the record and get the selected values
          'Note: Alternate Method: You can store this values in session variables or pass the the above SQL string in a session variable and grab record on EditProject.aspx page load
          'using a function similar to the FindSelectedProject below
      End Select
End Sub
0
 
LVL 7

Expert Comment

by:dante469
Comment Utility
May I suggest a simple straightforward approach that utilises built in .Net features...

Simply add a hyperlink column to your existing datagrid that opens another window that handles the edit/add.

For consistency I always make the first column (column 0) of my datagrid contain the primary key of the table to be edited...  If you don't want the user to see this column then set the visibility = False and ReadOnly = True...

Add the following lines to your datagrid...  My explample illustrates a datafield called EID that opens a form called empaddedit.aspx passing a parameter called EID that contains the content of the EID column...


<asp:HyperLinkColumn Text="Employee ID" DataNavigateUrlField="EID" DataNavigateUrlFormatString="javascript:var win=window.open('empaddedit.aspx?EID={0}',null,'width=400,height=400');" DataTextField="EID" HeaderText="Employee ID">
      <ItemStyle Font-Bold="True" Wrap="False" Width="20%"></ItemStyle>
</asp:HyperLinkColumn>

In the empaddedit.aspx form have the following code at pageload to determine if add/edit...


If Not Page.IsPostBack Then
      Dim EID As String = ""
      if not Request("EID") is nothing then
            Request("EID")
      end if

        If EID.Length = 0 Then
            'Adding a new record
            '
            '

                Else
            'Editing existing record
            '
            'load record from DB into form and allow edit

        End If
End If



Also the following is a great ref for advanced datagrid techniques...

http://aspnet.4guysfromrolla.com/articles/040502-1.aspx

Trust this resolves your question.

Have Fun,
Dante
0
 

Author Comment

by:terrenceg
Comment Utility
Thanks for all your responses, however I am terribly sorry for not mentioning that this is for a VB.Net Win Forms application and not for the Web.
0
 
LVL 4

Expert Comment

by:Jarodtweiss
Comment Utility
My first comment was for Winforms dataGrid.
Tell me if it is not enough to implement the solution !
0
 

Author Comment

by:terrenceg
Comment Utility
Thanks Jearodtweiss for your comments.  I am busy trying your suggestion but I have no idea how to overide the OnMouseDown event etc. Can you give me more details on how to raise the event so that I can display the edit form.  Thanking you in advance.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:Jarodtweiss
Comment Utility
Here is my override of OnMouseDown :
It was more complex and I have extract only the part related to the DoubleClick event (I hope not having forgotten somehting...)
In that example I raise two events, one to specify that we have clicked on a row, and one to specify that we have double clicked.

Do not forget to declare those variables :
  'For the double click
  Private m_LastLeftClickTime As DateTime
  Private m_LastLeftClickPosition As Point

and the events :
  'Event raised when a click occurs on a row
  Public Event RowLeftClick As CommonTypes.SelectedItemEventHandler
  'Event raised when a double click on a row has occured
  Public Event RowLeftDoubleClick As CommonTypes.SelectedItemEventHandler

my Custom EventArgs is simply : (it may not be useful for you....)

Public Class SelectedItemEventArgs
  Inherits EventArgs

  Private m_NewItemIndex As Integer
  Private m_OldItemIndex As Integer
  Private m_SelectedText As String

  Public ReadOnly Property NewItemIndex() As Integer
    Get
      Return m_NewItemIndex
    End Get
  End Property

  Public ReadOnly Property OldItemIndex() As Integer
    Get
      Return m_OldItemIndex
    End Get
  End Property

  Public ReadOnly Property SelectedText() As String
    Get
      Return m_SelectedText
    End Get
  End Property

  Public Sub New(ByVal oldItem As Integer, ByVal newItem As Integer)
    m_OldItemIndex = oldItem
    m_NewItemIndex = newItem
  End Sub
  Public Sub New(ByVal oldItem As Integer, ByVal newItem As Integer, ByVal SelectedText As String)
    m_OldItemIndex = oldItem
    m_NewItemIndex = newItem
    m_SelectedText = SelectedText
  End Sub
  Public Sub New(ByVal newItem As Integer)
    m_OldItemIndex = -1
    m_NewItemIndex = newItem
  End Sub
  Public Sub New(ByVal newItem As Integer, ByVal SelectedText As String)
    m_OldItemIndex = -1
    m_NewItemIndex = newItem
    m_SelectedText = SelectedText
  End Sub
End Class


  Protected Overloads Overrides Sub OnMouseDown(ByVal e As MouseEventArgs)
      Dim hti As DataGrid.HitTestInfo = Me.HitTest(e.X, e.Y)

        'Management of the double click
        If hti.Type = Windows.Forms.DataGrid.HitTestType.Cell _
        OrElse hti.Type = Windows.Forms.DataGrid.HitTestType.RowHeader Then
          If e.Button = MouseButtons.Left Then
            'It is click on a row --> Raise the click event
            RaiseEvent RowLeftClick(Me, New CommonTypes.SelectedItemEventArgs(hti.Row))

            'Is it a double click? possible if : same position and correct timing
            If Date.Now.Subtract(m_LastLeftClickTime).TotalMilliseconds < SystemInformation.DoubleClickTime _
            AndAlso Math.Abs(Me.m_LastLeftClickPosition.X - e.X) <= SystemInformation.DoubleClickSize.Width _
            AndAlso Math.Abs(Me.m_LastLeftClickPosition.Y - e.Y) <= SystemInformation.DoubleClickSize.Height Then
              RaiseEvent RowLeftDoubleClick(Me, New CommonTypes.SelectedItemEventArgs(hti.Row))
            End If
            m_LastLeftClickTime = Date.Now
            m_LastLeftClickPosition = New Point(e.X, e.Y)
          End If
        End If

        'Normal working for all other cases --> Call Base Class sub
          MyBase.OnMouseDown(e)
  End Sub

0
 

Author Comment

by:terrenceg
Comment Utility
Hi Jarodtweiss, i don't know whether I am I totally confused or what but when I look at the events available on my datagrid I can see a DoubleClick event there already, so why do I need the above code to create it?  I have tried using the existing Datagrid Double Click event and I can now basically get the current row index.  I assume that I now need to find a way to read the column values for that row.  Am I going in the right direction or have I missed the whole thing.
0
 
LVL 4

Expert Comment

by:Jarodtweiss
Comment Utility
You are perfectly right ! :-)
I haven't used it for several reasons :
The existing event is inheriting from the control one and so will be raised for any double click (event on a column header, or outside ther ows...)
So you can use it, but I think that it will produce some unexpected results (eg the user wants to sort a column, and perform a double click instead of a single one to get the DESC order instead of ASC) but he will enter in edit mode.

Of course, you can test on what the user has clicked (using the HitTest method as shown in my example), but I was prefering to expose a specific event.
But you are right, your solution will also work !
0
 

Author Comment

by:terrenceg
Comment Utility
Ok, I now understand were you are comming from and it does make sense to do it your way. I have tried to use the above code but I get the following error:

Type 'CommonTypes.SelectedItemEventHandler' is not defined.

Where and what do I need to define?
0
 
LVL 4

Expert Comment

by:Jarodtweiss
Comment Utility
Yeah, it is beacause I have defined the SelectedItemEventHandler class in a separated namespace.
Just Remove "CommonTypes" and it should work fine !
You can also use the default EventHandler as well
0
 

Author Comment

by:terrenceg
Comment Utility
Jarodtweiss, I finally got your code to work but there are some issues that I am sure you can clear up.

Firstly I still had a problem with defining the 'CommonTypes.SelectedItemEventHandler',  I removed the CommonTypes bit and still got the error.  I then used EventHandler and it accepted it - is that Ok.

Secondly, when I run my program and double click on a cell it just highlights it but if I double click on the row header on the left I get the disired result.  I would like to double click on any cell on a row to open my form.
0
 
LVL 4

Expert Comment

by:Jarodtweiss
Comment Utility
That problem is simple in fact :
Clicking on a cell allow it to enter in edition mode.
That's why it doesn't work as you wish.
To resolve that, you siply have to turn all the cells read only (there is a Readonly property for each GridColumnStyle)
0
 

Author Comment

by:terrenceg
Comment Utility
Thanks Jarodtweiss, The readonly property solved the problem.  Thanks again for all your help, I have really learn't a lot in the process.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now