Solved

RowEditing Event Code

Posted on 2008-06-14
5
498 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

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

Join & Write a Comment

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

14 Experts available now in Live!

Get 1:1 Help Now