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

RowEditing Event Code

Posted on 2008-06-14
5
511 Views
Last Modified: 2013-11-26
Hello:

I'm trying to create a dynamic data command and data-adapter to populate a grid similar to the code below with autogenerateeditbutton='true'.

I know I need to populate the  GridView1_RowEditing  event but am unsure how to do that.
Several tries have been unsuccessful.

I'm trying to keep everything dynamic and do not know if I need to put my updatecommand in that event to make it workl ?

The table in the sql below could be choosen as a different table so in keeping everything dynamic I believe I'm going to have to populate the updatecommand in the rowediting event as something like  update testtable set field1=@field1 where ...... and I'm not sure how to write that update process so it updates the row they are on when they click edit.

Its running in asp.net

Thank you for any help.

-----------
Dim SQL_String As String = "select * from testtable"
Dim connection As Data.SqlClient.SqlConnection
connection = New Data.SqlClient.SqlConnection("server=USFTW007FLN3G1\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=test")
        connection.Open()
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim ds As New Data.DataSet
        Dim dataadapter As Data.SqlClient.SqlDataAdapter
        cmd.Connection = connection
        cmd.CommandText = SQL_String
        cmd.CommandType = Data.CommandType.Text
        Try
            dataadapter = New Data.SqlClient.SqlDataAdapter(cmd)
            dataadapter.Fill(ds)
            cmd.Parameters.Clear()
        Finally
        End Try
        connection.Close()
        GridView1.DataSource = ds
        GridView1.DataBind()
-------------------------
0
Comment
Question by:sny23vpb
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:JZoerman
ID: 21803417
When using custom editing, updating, etc. with the GridView control corresponding subs must exsist regardless of where your logic is performed.
 Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        Select Case e.CommandName
            Case "Edit"
                'May perform some logic here
                'Note: event RowEditing will fire subsequently
            Case "Update"
                'May perform some logic here
                'Note: event RowUpdating will fire subsequently
        End Select
    End Sub
 
  Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
        'May perform logic here. However, even if no logic performed here this sub must exsist
        'even if empty when using custom Editing in GridView1
        'Note: e.NewEditIndex is the row index that fired this event
    End Sub
 
    Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
        'May perform logic here. However, even if no logic performed here this sub must exsist
        'even if empty when using custom Updating in GridView1
        'Note: e.RowIndex is the row index that fired this event
    End Sub

Open in new window

0
 

Author Comment

by:sny23vpb
ID: 21805681
ok. Thanks.
So; if I have to use custom editing because I want my grid to be based on the table selected in a drop-down;  what is the basic syntax for me to express the update of the field with the value from the grid which I am guessing is identified by e.RowIndex.

And do I need to populate this in both the RowUpdating and RowEditing events ?
Since mine is so dynamic; I guess I"m going to have to pull down each field based on the dropdown table selected using a query against sysobjects.

I wish I could keep the standard edit functionality without having to define everything but I'm not sure how to get the system to auto-generate the updatecommand and deletecommand when the table is changed on the sqlconnection by a dropdown.
0
 
LVL 2

Expert Comment

by:JZoerman
ID: 21806949
Understand that the auto generate Update, Delete, and Insert statements generated by the wizard are created at design time, they are not generated at run time. Executing stored procedures based on the table selected would likely be best. Alternatively you could store the SQL statements in your code behind, one for each command, for each table. Obviously, this would be a problem if the number tables you wish to access is an in-determinate number. In that case, you would have to write a procedure to generate an SQL statement based on the table definition.

Couple questions, how do you want to handle or store the necessary SQL statements? Also, are you making available a set number of tables for viewing or will that number also be dynamic?
0
 

Author Comment

by:sny23vpb
ID: 21807483
Thanks. There are many tables. And it will be best if the system will automatically handle new tables in the sysobjects table without further intervention.

The only way I can think of to do that would be to write the updatecommand to dynamically determine the fields and parameters which I think would just be something like
sqldatasource.updatecommand="UPDATE [temp_tbl] SET [task] = @task  WHERE [ID] = @original_ID AND [task] = @original_task"

Then. Either the GridView1_RowUpdating or GridView1_RowEditing will have to somehow reference the row your on with code like:  ("TASK")= gridview1.task  ; but I"m not certain of how this syntax should work and have not located any good samples.

I presume I would do likewise for delete; but either use the detailsview for the insert process or dynamically create a union query to create a blank row for inserts off the grid.

I'm increasing points for all your trouble; please forward any thoughts you may have on how the syntax for the grid-updating event might work as well as anything you see that I'm missing.  All the examples I've seen are just not applicable.
0
 
LVL 2

Accepted Solution

by:
JZoerman earned 350 total points
ID: 21813347
Specify the GridView1.EditIndex = e.NewEditIndex in RowEditing event.

The trick here is to dynamically generate SQL statements based on the DataSet (see snippet for basic sample).  Then in RowCommand event, run the dynamically generated SQL statement.

As too which row will be updated will be GridView1.EditIndex. Note, you will have to provide some logic to identify which field is the identity.

To retrieve the edited values from the GridView you can use GridView1.Rows(GridView1.EditIndex).Cells(X).Text

No doubt this scenario is going to force you to write a legitimate amount of code. Hopefully this is enough information to get you going.
 Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
        'Convert row to edit mode
        GridView1.EditIndex = e.NewEditIndex
        ShowGrid()
    End Sub
 
  Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        Select Case e.CommandName
            Case "Update"
                'Run update SQL statement
        End Select
    End Sub
 
  Protected Function GenSQLUpdate(ByRef ds As DataSet) As String
 
        GenSQLUpdate = "UPDATE " & DropDownList1.SelectedValue & " SET "
 
        For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
            GenSQLUpdate = GenSQLUpdate & ds.Tables(0).Columns(i).ColumnName & "=@" & ds.Tables(0).Columns(i).ColumnName
            If i < ds.Tables(0).Columns.Count - 1 Then GenSQLUpdate = GenSQLUpdate & ","
        Next
 
        'Add where clause here
        GenSQLUpdate = GenSQLUpdate & " WHERE " & ds.Tables(0).Columns(0).ColumnName & "=@ID"
 
        Response.Write(GenSQLUpdate)
        Session("sqlUpdate") = GenSQLUpdate
 
    End Function

Open in new window

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

791 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