I need to trace or view that the stored proc is saving the data.

[macupryk]

I have a stored procedure and it is not saving the values in the table. How can I view that the stored procedure is saving the values.

Private Sub DoSave()
       Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("FinSolMainDBConn")
       Dim connfinsol As New System.Data.SqlClient.SqlConnection(ConnectionString)
       Dim strsql As String
       Dim adapter As New SqlDataAdapter
       Dim transaction As SqlTransaction
       Dim myCommand As New SqlCommand("stp_UpdTransOverride ", connfinsol)
       myCommand.CommandType = CommandType.StoredProcedure

       'Add Parameters
       ' 1 Policy Number
       myCommand.Parameters.Add("@Policy_Nbr", SqlDbType.VarChar, 7).Value = txtPolicyNumber.Text

       ' 2 Original Tranaction Override
       myCommand.Parameters.Add("@Trans_CodeOrig", SqlDbType.VarChar, 6).Value = cmbOriginalTransCode.Text

       ' 3 Transaction Effective Date 
       myCommand.Parameters.Add("@Trans_Eff_Date", SqlDbType.DateTime, 8).Value = dteEffDt.Value

       ' 4 Override Action 
       myCommand.Parameters.Add("@Override_Code", SqlDbType.VarChar, 2).Value = cmbOverrideAction.Text

       ' 5 Override New/Renewal 
       myCommand.Parameters.Add("@NR_CodeOvr", SqlDbType.VarChar, 1).Value = cmbOverrideNR.Text

       ' 6 Override Transaction Code 
       myCommand.Parameters.Add("@Trans_CodeOvr", SqlDbType.VarChar, 6).Value = cmbOverrideTransactionCode.Text

       ' 7 Modified UID 
       myCommand.Parameters.Add("@ModifiedUID", SqlDbType.NVarChar, 48).Value = txtUserId.Text

       ' 8 Plan Year 
       myCommand.Parameters.Add("@Plan_Year", SqlDbType.Int, 4).Value = txtPlanYear.Text
       'Open Connection
       Try
           'Open Connection
           connfinsol.Open()
           myCommand.ExecuteNonQuery()
           MsgBox("Data Saved Successfully !", MsgBoxStyle.Information, Me.Text)
       Catch ex As Exception
           MsgBox(ex.Message, MsgBoxStyle.Critical, Me.Text)
       Finally
           If myCommand.Connection.State = ConnectionState.Open Then
               myCommand.Connection.Close()
           End If
       End Try

   End Sub

Edited February 22, 2006 by PlausiblyDamp

[ost]

Have you testet the Stored Procedure in Query Analyzer?

[samsmithnz]

I would use the profiler to see exactly what your parameters you're passing in look like, and then plop it into Query Analysiser like ost sugguested.