[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

RowEditing Event Code

Posted on 2008-06-14
5
Medium Priority
?
526 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
[X]
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
  • 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 1400 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 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