InsertCommand parameter problem

[jebenson]

Hello all,

I am having some trouble getting parameters for the InputCommand for a DataAdapter filled with the appropriate data to pass to a SQL Server stored procedure. Everything below executes with no errors, but no new row is added to the database. I have checked the values of the parameters just before Update is called, after the values are assigned to the DataTable, and they all show as blank. I got the basis of this code from MSDN. Can anybody tell me what I am doing incorrectly?

    If frmParent.NewAuth And frmParent.daAuth Is Nothing Then
        frmParent.daAuth = New SqlDataAdapter("SELECT * FROM Authorizations WHERE 1=0", frmParent.Conn)
    ElseIf Not frmParent.NewAuth And frmParent.daAuth Is Nothing Then
        frmParent.daAuth = New SqlDataAdapter("SELECT * FROM Authorizations WHERE ID=" & frmParent.CurrentAuthID, frmParent.Conn)
    End If

    If frmParent.dsAuth Is Nothing Then
        frmParent.dsAuth = New DataSet
        frmParent.daAuth.FillSchema(frmParent.dsAuth, SchemaType.Mapped, "Authorizations")
        frmParent.daAuth.Fill(frmParent.dsAuth, "Authorizations")

        frmParent.daAuth.InsertCommand() = New SqlCommand("spInsertAuth", frmParent.Conn)

        With frmParent.daAuth.InsertCommand
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add("@AuthYear", SqlDbType.Int, 4, "AuthYear")
            .Parameters(0).Direction = ParameterDirection.Input
            .Parameters.Add("@Amount", SqlDbType.Money, 8, "Amount")
            .Parameters(1).Direction = ParameterDirection.Input
            .Parameters.Add("@RemainingAmt", SqlDbType.Money, 8, "RemainingAmt")
            .Parameters(2).Direction = ParameterDirection.Input
            .Parameters.Add("@AuthDesc", SqlDbType.VarChar, 128, "AuthDesc")
            .Parameters(3).Direction = ParameterDirection.Input
        End With

        Dim IDParam As SqlParameter = frmParent.daAuth.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 4, "ID")
        IDParam.Direction = ParameterDirection.Output

    End If

    If frmParent.NewAuth Then
        Dim dr As DataRow

        dr = frmParent.dsAuth.Tables(0).NewRow()
        dr("AuthYear") = AuthYear.Text
        dr("Amount") = Amount.Text
        dr("RemainingAmt") = RemainingAmt.Text
        dr("AuthDesc") = AuthDesc.Text

        frmParent.dsAuth.Tables(0).Rows.Add(dr)

        frmParent.dsAuth.AcceptChanges()

        frmParent.daAuth.Update(frmParent.dsAuth, "Authorizations")

    End If

Thanks,
JEB

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[andrea96]

You need to move the AcceptChanges statement to after the Update. AcceptChanges is changing all rows of your DataSet to "unchanged", so there is nothing for the Update to update.

Andrea

 

[jebenson]

Thanks for your reply. I found the problem and it was a misspelled parameter in the stored proc...silly me.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson