locked
update and delete (3 tier) RRS feed

  • Question

  • User-619595341 posted

    Hi, i have problems with the updating and deleting of records in database. Could some one please guide me out? i'm using ObjectDataSource and the GridView to display the results in the database.

    Below is the codes for the update and delete (in the DAL (data access layer). BTW, i'm doing 3 tier architecture in VB.

    'update the records accordingly in the database.
        Public Function UpdateBooks(ByVal title As String, ByVal borrowDate As String, ByVal dueDate As String, ByVal NameBorrower As String, ByVal book_id As Integer) As Integer
            Dim connectionString As String = My.Settings.dbConnection
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
            Dim queryString As String = "UPDATE books SET title='" & title & "',borrowDate='" & borrowDate & "',dueDate='" & dueDate & "',NameBorrower='" & NameBorrower & "' where book_id='" & book_id & "'"
            Dim dbCommand As SqlCommand = New SqlCommand(queryString, dbConnection)
            Dim rowsAffected As Integer = 0
            dbConnection.Open()
            Try
                rowsAffected = dbCommand.ExecuteNonQuery
            Finally
                dbConnection.Close()
            End Try
            Return rowsAffected
    
        End Function
    
    
        'delete one or more particular records from the database.
        Public Function DeleteBooks(ByVal book_id As Integer, ByVal title As String, ByVal borrowDate As String, ByVal dueDate As String, ByVal NameBorrower As String) As Integer
            Dim connectionString As String = My.Settings.dbConnection
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
            Dim queryString As String = "DELETE FROM books where book_id='" & book_id & "'"
            Dim dbCommand As SqlCommand = New SqlCommand(queryString, dbConnection)
            Dim rowsAffected As Integer = 0
            dbConnection.Open()
            Try
                rowsAffected = dbCommand.ExecuteNonQuery
            Finally
                dbConnection.Close()
            End Try
            Return rowsAffected
        End Function


    I don't know why, but every time i try to update (by clicking on the update button), every thing is not updated, the page would refresh then the records still remain the same in the GridView. As for delete, when i clicked on the 'delete' button, the record is not deleted, it still remains there. i'm confused! some one help me out ): thanks.


    for your information, these are the codes in the (BLL (business logic layer)):

    Public Function getUpdateBooks(ByVal title As String, ByVal borrowDate As String, ByVal dueDate As String, ByVal NameBorrower As String, ByVal book_id As Integer) As Integer
    
            Dim ds As Integer = UserDA.UpdateBooks(title, borrowDate, dueDate, NameBorrower, book_id)
    
            Return ds
    
        End Function
    
        Public Function getDeleteBooks(ByVal book_id As Integer, ByVal title As String, ByVal borrowDate As String, ByVal dueDate As String, ByVal NameBorrower As String) As Integer
    
            Dim ds As Integer = UserDA.DeleteBooks(book_id, title, borrowDate, dueDate, NameBorrower)
    
            Return ds
    
        End Function



    Friday, January 8, 2010 9:25 AM

Answers

  • User-952121411 posted

    Have you tried placing breakpoints on the code you provided and debugged it to see if the methods are actually being called correctly?  If they are indeed being called and the code is running, then you need to check the database (not the GridView results) to see if the records have actually been updated or deleted based on what you are doing.

    If the records are actually being updated or deleted in the database then the problem is with the GridView and its associated ODS, and not the code you have above.  If the code you have above is not even being called, then you have an issues with the ODS and its wired up methods in the page.

    At 1st glance I don't see anything glaring incorrect in the code other than using dynamic SQL without parameters which always presents a risk, but that is not the source of your original question.  Just as a side piece of advice, you should use command parameters instead and take a look at this article in the future for some good information:

    How To: Protect From SQL Injection in ASP.NET:

    http://msdn.microsoft.com/en-us/library/ms998271.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 8, 2010 9:57 AM
  • User-952121411 posted

    where to do the IsPostback check?
     

    That is a guess on that being the issue, but typically the IsPostBack check is in the Page_Load() event like below:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
      If Not IsPostBack Then
         'Code occurs here only during initial Page_Load()
      Else
         'Code occurs here during PostBacks
      End If
    
      'Code here occurs every time this even is called
    
    End Sub

    If you could please respond with answers to my last post with questions to help diagnose the issue, that would be helpful as well.


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 12:04 PM

All replies

  • User1588207957 posted

    did you have the IsPostback check?

    Friday, January 8, 2010 9:50 AM
  • User-952121411 posted

    Have you tried placing breakpoints on the code you provided and debugged it to see if the methods are actually being called correctly?  If they are indeed being called and the code is running, then you need to check the database (not the GridView results) to see if the records have actually been updated or deleted based on what you are doing.

    If the records are actually being updated or deleted in the database then the problem is with the GridView and its associated ODS, and not the code you have above.  If the code you have above is not even being called, then you have an issues with the ODS and its wired up methods in the page.

    At 1st glance I don't see anything glaring incorrect in the code other than using dynamic SQL without parameters which always presents a risk, but that is not the source of your original question.  Just as a side piece of advice, you should use command parameters instead and take a look at this article in the future for some good information:

    How To: Protect From SQL Injection in ASP.NET:

    http://msdn.microsoft.com/en-us/library/ms998271.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 8, 2010 9:57 AM
  • User-619595341 posted

    where to do the IsPostback check? sorry, am new in vb ):

    Saturday, January 9, 2010 6:27 AM
  • User-619595341 posted

    i think it's my codes that are making everything not workable.. because i am using my teacher's codes as sample and edited accordingly. so by right my code should work, but it didn't.

    i really don't know what to do x.x

    Saturday, January 9, 2010 6:30 AM
  • User-952121411 posted

    where to do the IsPostback check?
     

    That is a guess on that being the issue, but typically the IsPostBack check is in the Page_Load() event like below:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
      If Not IsPostBack Then
         'Code occurs here only during initial Page_Load()
      Else
         'Code occurs here during PostBacks
      End If
    
      'Code here occurs every time this even is called
    
    End Sub

    If you could please respond with answers to my last post with questions to help diagnose the issue, that would be helpful as well.


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 11, 2010 12:04 PM