Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Alternate Datagrid input in VB.NET

Posted on 2004-03-29
15
988 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
ID: 10704008
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
ID: 10704279
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
ID: 10704303
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 7

Expert Comment

by:dante469
ID: 10704719
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
ID: 10710882
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
ID: 10711254
My first comment was for Winforms dataGrid.
Tell me if it is not enough to implement the solution !
0
 

Author Comment

by:terrenceg
ID: 10711464
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
 
LVL 4

Expert Comment

by:Jarodtweiss
ID: 10711793
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
ID: 10712039
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
ID: 10712118
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
ID: 10712459
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
ID: 10712674
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
ID: 10721527
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
ID: 10721543
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
ID: 10722079
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
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)…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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