none
How update database from DataSet? RRS feed

  • Question

  • At the business tier, I am receiving back from the client a DataSet ("PenDataSet") containing a single DataTable ("Results").

    The "Results" DataTable looks like this:

     

    PenID   PenYardSequence
    234 1
    492 3
    272 2
    239 4
    827 5

     

    When the client retrieved the DataTable it allowed the user to reorder the sequence only (i.e. no inserts or deletes if that matters).

     

    So now in the business tier I've just received this DataSet and I need to update the Pen table in our SQL Server database.  I have a proc UpdatePenYardSequence that looks like this:

     

    CREATE PROCEDURE dbo.UpdatePenYardSequence
    @PenID int,
    @PenYardSequence smallint
    AS
    UPDATE Pen
    SET PenYardSequence = @PenYardSequence
    WHERE PenID = @PenID

     

    I just don't know what to write in my business tier code to get the changes into the database.  I've written the below which does not error out.. still the changes don' tmake it to the database:

     

    *********************************************************************
    Dim Connection As New SqlConnection()
    Connection.ConnectionString = My.Settings.COWFeedyardConnectionString

    Dim Command As New SqlCommand()
    Command.Connection = Connection
    Command.CommandType = CommandType.StoredProcedure
    Command.CommandText = "UpdatePenYardSequence"

    Dim DataAdapter As New SqlDataAdapter()
    DataAdapter.UpdateCommand = Command
    DataAdapter.Update(PenDataSet, "Results")

    *********************************************************************

    What am I doing wrong, please?

    Thanks,
    Ron

     

     

    Sunday, July 15, 2007 6:23 PM

All replies

  • You need to add code to create parameters you are passing to the stored procedure and map them to the columns in your DataTable, so it knows where to take values from. So the code should look like (you need to check it for syntax, since I do not have Visual Studio right now, but it will give you an idea)

     

    Dim oPenID as SqlParameter = New SqlParameter ("@PenID", SqlDbType.Int, 0, "PenID")

    Dim oPenYS as SqlParameter = New SqlParameter ("@PenYardSequence", SqlDbType.SamllInt, 0, "PenYardSequence")

     

    Command.CommandText = "UpdatePenYardSequence"

    Command.Parameters.Add(oPenID)

    Command.Parameters.Add(oPenYS)

    Dim DataAdapter As New SqlDataAdapter()


    DataAdapter.UpdateCommand = Command
    DataAdapter.Update(PenDataSet, "Results")

    Monday, July 16, 2007 10:40 AM
    Moderator