none
Updating oracle with datagrid on a webform RRS feed

  • Question

  •  

    I have been trying for 2 weeks to update an oracle table with a datagrid on my website. I have tried everything I can find on the web and nothing works. The connection is fine, the datagrid shows on the page with the correct records, I click 'Edit', the textboxes for editing appear, I edit the values, I click Update, and big spectacular NOTHING happens! I debug and my variables have the old values. I've tried using a datasource and the automatic update statements and that's even worse, I can't get past the errors. I finally resorted to writing my own statements to fill/bind/update the grid, but nothing happens.

    PLEASE tell me how this can be done! (Sorry about the smileys in my parameters, can't get rid of them.)

    This is what I have right now (just the latest of many disasters):

     

    Code Snippet

    Imports System.Data.OracleClient

    Imports System.Web.UI.WebControls.SqlDataSourceCommandEventArgs

     

    Partial Class ValidTbl

    Inherits System.Web.UI.Page

     

    Dim objDB As New OraGeneric()

     

    Dim getAccType, getAccDesc, getAccStat As String

     

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not Page.IsPostBack Then

    bindAccType()

    End If

    End Sub

     

    Public Sub bindAccType()

    Dim sql3 As New System.Text.StringBuilder()

    sql3.Append("SELECT ACC_TYPE_CODE, ACC_TYPE_DESC, ACC_TYPE_STAT ")

    sql3.Append("FROM  TBL_ACC_TYPE ORDER BY ACC_TYPE_CODE ")

    gvAccType.DataSource = objDB.GetData(sql3.ToString, String.Empty)

    gvAccType.DataBind()

    End Sub

     

    Public Sub gvAccType_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles gvAccType.RowCancelingEdit

    gvAccType.EditIndex = -1

    bindAccType()

    End Sub

     

    Public Sub gvAccType_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles gvAccType.RowEditing

    gvAccType.EditIndex = e.NewEditIndex

    bindAccType()

    End Sub

     

    **THIS IS WHERE IT STOPS WORKING, IT REFUSES TO GET THE NEW VALUES

     

    Public Sub gvAccType_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles gvAccType.RowUpdating

    Dim ID As Integer = CInt(gvAccType.DataKeys(e.RowIndex).Value)

    getAccType = CType(gvAccType.Rows(e.RowIndex).Cells(0).Controls(0), TextBox).Text

    getAccDesc = CType(gvAccType.Rows(e.RowIndex).Cells(1).Controls(0), TextBox).Text

    getAccStat = CType(gvAccType.Rows(e.RowIndex).Cells(2).Controls(0), TextBox).Text

    Dim sqlUpdAccType As New System.Text.StringBuilder

    sqlUpdAccType.Append("UPDATE TBL_ACC_TYPE SET ACC_TYPE_DESC = :param1, ACC_TYPE_STATUS = :param2  ")

    sqlUpdAccType.Append("WHERE ACC_TYPE_CODE = :param3 ")

    objDB.Execute_Nonquery3(sqlUpdAccType.ToString, getAccType, getAccDesc, getAccStat)

    gvAccType.EditIndex = -1

    bindAccType()

    e.Cancel = True

    End Sub

     

    End Class

     

    This is in the OraGeneric.vb file, this works fine, I tested it by loading the variables:

     

    Public Sub Execute_Nonquery3(ByVal sql As String, ByVal param1 As String, ByVal param2 As String, ByVal param3 As String)

     

    Try

    Using connection As New OracleConnection(conn)

    Using command As New OracleCommand(sql, connection)

    If param1.Trim().Length > 0 Then

    command.Parameters.AddWithValue(":param1", param1)

    End If

    If param2.Trim().Length > 0 Then

    command.Parameters.AddWithValue(":param2", param2)

    End If

    If param3.Trim().Length > 0 Then

    command.Parameters.AddWithValue(":param3", param3)

    End If

    command.Connection.Open()

    command.ExecuteNonQuery()

    command.Connection.Close()

    End Using

    End Using

    Catch ex As OracleException

    Throw

    Catch EX As Exception

    Throw

    End Try

     

    End Sub

     

     

    Friday, June 6, 2008 9:04 PM

Answers

  • According to MSDN, setting e.Cancel property will attempt cancel the update operation. Remove this call from RowUpdating event, otherwise you will continue experiencing problems and strange behavior in your application.

     

    Refer to MSDN documentation for more details: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowupdating(VS.80).aspx

     

    For a "new row" functionality, you can create additional fields to read the the columns data from the user and an "Add" button. To perform the insert, run the Oracle's INSERT command.

     

    One more comment: make sure you validate and sanitize the user input before you use it to avoid SQL injection attacks.

     

    Good luck!

    Wednesday, June 11, 2008 10:31 PM
    Moderator
  • The e.Cancel = true at the end of the sub will not prevent the update, the update has already occurred when it gets there. I will however use it to cancel the operation before the update if the data does not validate, when I get around to validating.

     

    That MSDN page you are sending me to is the first place I went to find information on how to use the datagrid with an SQLDataSource and it does not work with Oracle. There are several people on the web who claim to have done it, but not without extensively editing the source code, increasing the potential for errors, which kind of makes the 'automatic' part useless to those who use Oracle. MSDN does not have any kind of comprehensive instructions on using the Oracle client in visual studio, anything I've tried from there has never worked, including the entire section on 'Oracle Data Provider for .NET'. There is nothing useful for using Oracle with VS controls. Any useful help I've gotten has been on the web, where I found the vb code to do this without an SQLDataSource, as you can see in my code.

     

    To add a row to the grid you have make every column a template and then rewrite all your code to read the template controls. I used textboxes in a disappearing panel, it works great, the only place I need a template is for the 'New' button, it doesn't misplace everything on the page, and I don't have to mess with the 'automatic' insert statement in the source code. Now that it's working, I can get around to 'sanitizing'. But thanks for answering.
    Thursday, June 12, 2008 3:43 PM

All replies

  • Please remove: 'e.Cancel = True' call from gvAccType_RowUpdating

    It looks like you are canceling the update operation immediately after performing it.

    Let me know if it solves the problem. If it does not, please post full sources of ASPX and VB files. Just remember to replace the connection string and other business-sensitive details before you post the code.

    Tuesday, June 10, 2008 4:44 AM
    Moderator
  • No, e.Cancel = True only resets the grid after the update. I did finally get it to work by messing with the parameters, and I renamed my execute query sub. I'm not sure which one did the trick. Now if I can just figure out how to do an insert row I'll be all set! Thanks for the reply, I really wish the sqlDataSource automatic update/insert/delete statements would work properly with Oracle, this is more trouble than it's worth.

    Wednesday, June 11, 2008 7:46 PM
  • According to MSDN, setting e.Cancel property will attempt cancel the update operation. Remove this call from RowUpdating event, otherwise you will continue experiencing problems and strange behavior in your application.

     

    Refer to MSDN documentation for more details: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowupdating(VS.80).aspx

     

    For a "new row" functionality, you can create additional fields to read the the columns data from the user and an "Add" button. To perform the insert, run the Oracle's INSERT command.

     

    One more comment: make sure you validate and sanitize the user input before you use it to avoid SQL injection attacks.

     

    Good luck!

    Wednesday, June 11, 2008 10:31 PM
    Moderator
  • The e.Cancel = true at the end of the sub will not prevent the update, the update has already occurred when it gets there. I will however use it to cancel the operation before the update if the data does not validate, when I get around to validating.

     

    That MSDN page you are sending me to is the first place I went to find information on how to use the datagrid with an SQLDataSource and it does not work with Oracle. There are several people on the web who claim to have done it, but not without extensively editing the source code, increasing the potential for errors, which kind of makes the 'automatic' part useless to those who use Oracle. MSDN does not have any kind of comprehensive instructions on using the Oracle client in visual studio, anything I've tried from there has never worked, including the entire section on 'Oracle Data Provider for .NET'. There is nothing useful for using Oracle with VS controls. Any useful help I've gotten has been on the web, where I found the vb code to do this without an SQLDataSource, as you can see in my code.

     

    To add a row to the grid you have make every column a template and then rewrite all your code to read the template controls. I used textboxes in a disappearing panel, it works great, the only place I need a template is for the 'New' button, it doesn't misplace everything on the page, and I don't have to mess with the 'automatic' insert statement in the source code. Now that it's working, I can get around to 'sanitizing'. But thanks for answering.
    Thursday, June 12, 2008 3:43 PM