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?
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
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?
Code:
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