Member Avatar for mcupryk

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

Any help would be kindly appreciated!

M

  • 2 Contributors
  • 1 Reply
  • 215 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Letscode

Dani AI

Generated

A concise diagnostic checklist and quick fixes for the scenario described by . Common reasons a stored procedure appears to do nothing are: the application is connected to a different database than expected, parameter type/length or value conversions prevent the procedure’s WHERE clause from matching rows, the stored procedure’s logic returns before performing updates, the procedure swallows errors, or the executing login lacks UPDATE rights. ’s note about datatype mismatches is relevant; the checks below expand on that.

Verify the target and the procedure behavior. Confirm the runtime connection string points to the intended server/database (a SELECT DB_NAME() executed over the same connection is definitive). Execute the stored procedure directly from SQL Server Management Studio with identical parameter values to see whether it updates rows. If the procedure still makes no change, inspect its WHERE logic, look for early RETURNs or TRY/CATCH blocks that suppress errors, and consider adding a temporary OUTPUT parameter, a final SELECT @@ROWCOUNT, or a small audit insert so the proc reports what it did.

Validate client-side typing and trace the call. Convert TextBox strings to the correct CLR types before setting parameter values (Integer.TryParse for numeric fields, DateTime parsing for dates) and explicitly set SqlDbType and sensible sizes. Use SQL Profiler or Extended Events to capture the RPC call and confirm the exact parameter values arriving at SQL Server. Also verify the app’s SQL login has the necessary UPDATE permissions.

A simple VB.NET pattern to confirm server-reported results (capture rows affected) and ensure proper disposal:

Dim rowsAffected As Integer
Using conn As New SqlConnection(connStr)
  Using cmd As New SqlCommand("stp_UpdTransOverride", conn)
    cmd.CommandType = CommandType.StoredProcedure

    Dim planYear As Integer = 0
    Integer.TryParse(txtPlanYear.Text, planYear)
    cmd.Parameters.Add("@Plan_Year", SqlDbType.Int).Value = planYear

    conn.Open()
    rowsAffected = cmd.ExecuteNonQuery()
  End Using
End Using

MsgBox("Rows affected: " & rowsAffected)

If the procedure updates when run in SSMS but rowsAffected is zero from the app, focus on connection string/credentials and parameter conversions; if it does not update in SSMS, focus on stored-proc logic or permissions. Temporary debug outputs are fine for diagnosis but should be removed from production code.

Member Avatar for Letscode

If you are using SQL server 2000,Try debugging using the Query analyzer.

Check this website,

Also check your Datatype,Make sure you are sending the same datatype to update the database.

Check your code,
I assume Policy Number is an Integer in your Database but you are sending a string.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.