Question about SQL Transaction/Stored Procedures and Identity Fields

Getting a "Procedure or function spNewPatient expects parameter @PXID (Identity Field) which was not supplied." when I try to insert data using dynamic SP and transactions. However, @PXID is an Identity field in tblPatient, so I didn't include it on the input parameters. Do I also have to begin a transaction here (since the stored procedure is technically a transaction)? Is that the reason why the @PXID is not generated by the stored procedure?

Code:

---

Public SQLParams As New List(Of SqlParameter)
Public Function ExecuteSP(SPName As String) As Integer
        Dim _ReturnVal As Integer
        Try
                SQLConn.Open()
                SQLCmd = New SqlCommand(SPName, SQLConn)
                SQLCmd.CommandType = CommandType.StoredProcedure

                For Each p As SqlParameter In SQLParams
                        SQLCmd.Parameters.Add(p)
                        SQLCmd.Parameters(p.ParameterName).Value = p.Value
                Next

                _ReturnVal = SQLCmd.ExecuteScalar()
                SQLConn.Close()
                Return _ReturnVal
        Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Encountered Critical Error")
                If SQLConn.State = ConnectionState.Open Then SQLConn.Close()
                Return -1
        End Try
       
        FlushParams()
End Function

Public Sub AddParams(Name As String, Value As Object, Optional DataType As DbType = DbType.String)
        Dim newParams As New SqlParameter With {.ParameterName = Name, .Value = Value, .DbType = DataType}
        SQLParams.Add(newParams)
End Sub

---

Code:

---

USE [dbMacondo]
ALTER PROC [dbo].[spNewPatient]
        @PXID                        int OUTPUT,
        <Input Variables>
AS
BEGIN
        BEGIN TRANSACTION
                BEGIN TRY
                        INSERT INTO tblPatient (...)
                        VALUES (...);

                        SELECT @PXID = SCOPE_IDENTITY();
                        COMMIT TRANSACTION
                END TRY

        BEGIN CATCH
                IF XACT_STATE() <> 0
                        ROLLBACK TRANSACTION
        END CATCH
END

---

Edit: I changed the order of the fields in the stored procedure. It looks like none of the parameters I tried to pass from VB to SQL were transferred... I checked the list (SQLParams) and everything was in order.

Edit 2: Checked it again, and it's still looking for the @PXID Identity Field. Removed the @PXID in the stored procedure and everything went through nicely. However, how would I be able to implement this with using an output variable for scope_identity() in the stored procedure?

i think you need to add an OUTPUT parameter to the stored proc call. check the .direction property of the parameter object.

also: is this really required?

Code:

---

SQLCmd.Parameters(p.ParameterName).Value = p.Value

---

i'd think once you created the parameter with name and value, you can add this object to the command. so on that line the value should already be right.

Try adding this in the SPROC

@PXID int OUTPUT =0,

It will default to zero but will not generate that message.

Quote:

---

Originally Posted by TysonLPrice

Try adding this in the SPROC

@PXID int OUTPUT =0,

It will default to zero but will not generate that message.

---

I was getting syntax error on both @PXID int OUTPUT=0 and @PXID =0 int OUTPUT though.

Quote:

---

Originally Posted by digitalShaman

i think you need to add an OUTPUT parameter to the stored proc call. check the .direction property of the parameter object.

also: is this really required?

Code:

---

SQLCmd.Parameters(p.ParameterName).Value = p.Value

---

i'd think once you created the parameter with name and value, you can add this object to the command. so on that line the value should already be right.

---

Removed that, thank you! I was following along a tutorial, you see. So, below is the code in its entirety. I'd like to know if there's anything else I can optimize in it, and possibly how else I can trigger exceptions.

Code:

---

    Dim _params As New List(Of SqlParameter)
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If CheckErrors() = True Then
            Dim dbConn As New SqlConnection With {.ConnectionString = strConnStr}
            Dim dbCmd As SqlCommand = dbConn.CreateCommand()
            Dim dbTran As SqlTransaction

            Try
                dbConn.Open()

                dbTran = dbConn.BeginTransaction("trnAddProfile")

                dbCmd.Connection = dbConn
                dbCmd.Transaction = dbTran

                AddParams("@PXFName", strPXFName, DbType.String)
                AddParams("@PXMName", strPXMName, DbType.String)
                AddParams("@PXLName", strPXLName, DbType.String)
                AddParams("@PXAge", intPXAge, DbType.Int32)
                AddParams("@MomFName", strMomFName, DbType.String)
                AddParams("@MomMName", strMomMName, DbType.String)
                AddParams("@MomLName", strMomLName, DbType.String)
                AddParams("@MomAge", intMomAge, DbType.Int32)
                AddParams("@IsMomEmployed", blnIsMomEmployed, DbType.Boolean)
                AddParams("@IsMomStudent", blnIsMomStudent, DbType.Boolean)
                AddParams("@IsMomLiterate", blnIsMomLiterate, DbType.Boolean)
                AddParams("@IsMomHousewife", blnIsMomHousewife, DbType.Boolean)
                AddParams("@DadFName", strDadFName, DbType.String)
                AddParams("@DadMName", strDadMName, DbType.String)
                AddParams("@DadLName", strDadLName, DbType.String)
                AddParams("@DadAge", intDadAge, DbType.Int32)
                AddParams("@IsDadEmployed", blnIsDadEmployed, DbType.Boolean)
                AddParams("@IsDadStudent", blnIsDadStudent, DbType.Boolean)
                AddParams("@IsDadLiterate", blnIsDadLiterate, DbType.Boolean)
                AddParams("@CivilStatus", strCivilStatus, DbType.String)
                AddParams("@Meals", strMeals, DbType.String)
                AddParams("@Water", strWater, DbType.String)
                AddParams("@Help", blnHelp, DbType.Boolean)
                AddParams("@FamIncome", dblFamIncome, DbType.Double)
                AddParams("@FamEmployed", strFamEmployed, DbType.String)
                AddParams("@LocProx", strLocProx, DbType.String)
                AddParams("@DateCreated", dteDateNow, DbType.Date)

                dbCmd = New SqlCommand("spNewPatient", dbConn, dbTran)
                dbCmd.CommandType = CommandType.StoredProcedure
                For Each p As SqlParameter In _params
                    dbCmd.Parameters.Add(p)
                    'dbCmd.Parameters(p.ParameterName).Value = p.Value
                Next
                Dim intPatientID As Integer = dbCmd.ExecuteScalar()

                _params.Clear()

                Dim Query As String = "INSERT INTO tblProfile " &
                        "(InfectG, InfectP, Ultrasound, InfectSyphilis, InfectRubella, " &
                        "InfectToxo, InfectHBV, InfectHIV, InfectMalaria, InfectGyne, " &
                        "InfectOthers, PNCU, PNCUDate, PathAcuteHepa, PathToxemia, " &
                        "PathAIDS, PathMultiPreg, PathAnemia, PathTB, PathPTL, PathOthers, PatientID) " &
                        "VALUES (@InfectG, @InfectP, @Ultrasound, @InfectSyphilis, @InfectRubella, " &
                        "@InfectToxo, @InfectHBV, @InfectHIV, @InfectMalaria, @InfectGyne, " &
                        "@InfectOthers, @PNCU, @PNCUDate, @PathAcuteHepa, @PathToxemia, " &
                        "@PathAIDS, @PathMultiPreg, @PathAnemia, @PathTB, @PathPTL, @PathOthers, @PatientID);"

                AddParams("@InfectG", strG, DbType.String)
                AddParams("@InfectP", strP, DbType.String)
                AddParams("@Ultrasound", intUltrasound, DbType.Int32)
                AddParams("@InfectSyphilis", strInfectSyphilis, DbType.String)
                AddParams("@InfectRubella", strInfectRubella, DbType.String)
                AddParams("@InfectToxo", strInfectToxo, DbType.String)
                AddParams("@InfectHBV", strInfectHBV, DbType.String)
                AddParams("@InfectHIV", strInfectHIV, DbType.String)
                AddParams("@InfectMalaria", strInfectMalaria, DbType.String)
                AddParams("@InfectGyne", strInfectGyne, DbType.String)
                AddParams("@InfectOthers", strInfectOthers, DbType.String)
                AddParams("@PNCU", intPNCU, DbType.Int32)
                AddParams("@PNCUDate", dtePNCU, DbType.Date)
                AddParams("@PathAcuteHepa", blnPathAcuteHepa, DbType.Boolean)
                AddParams("@PathToxemia", blnPathToxemia, DbType.Boolean)
                AddParams("@PathAIDS", blnPathAIDS, DbType.Boolean)
                AddParams("@PathMultiPreg", blnPathMultiPreg, DbType.Boolean)
                AddParams("@PathAnemia", blnPathAnemia, DbType.Boolean)
                AddParams("@PathTB", blnPathTB, DbType.Boolean)
                AddParams("@PathPTL", blnPathPTL, DbType.Boolean)
                AddParams("@PathOthers", strPathOthers, DbType.String)
                AddParams("@PatientID", intPatientID, DbType.Int32)

                dbCmd = New SqlCommand(Query, dbConn, dbTran)
                dbCmd.CommandType = CommandType.Text
                For Each p As SqlParameter In _params
                    dbCmd.Parameters.Add(p)
                    'dbCmd.Parameters(p.ParameterName).Value = p.Value
                Next
                dbCmd.ExecuteNonQuery()

                dbTran.Commit()
                MsgBox("Patient record saved.", MsgBoxStyle.Information, "Save Successful")
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Parsing Data")

                Try
                    MsgBox("Attempting to rollback changes to database...", MsgBoxStyle.Exclamation, "Error Parsing Data")
                    dbTran.Rollback()
                    MsgBox("Rollback successful.", MsgBoxStyle.Exclamation, "Error Parsing Data")
                Catch ex2 As Exception
                    MsgBox("Failed to rollback changes. Rollback Exception Type: " & " " & ex2.Message, MsgBoxStyle.Exclamation, "Rollback Failed")
                End Try
            End Try
        End If
    End Sub

---

Also worth nothing is that I got this warning message from dbTran.Rollback():

Variable dbTran is used before it has been assigned a value. A null reference exception could result at runtime.

How did this happen when dbTran was used earlier in initiating the transaction? The example on the KB also denotes the same usage.

What is this?

Code:

---

USE [dbMacondo]
ALTER PROC [dbo].[spNewPatient]
        @PXID                        int OUTPUT,
        <Input Variables>
AS
BEGIN
        BEGIN TRANSACTION
                BEGIN TRY
                        INSERT INTO tblPatient (...)
                        VALUES (...);

                        SELECT @PXID = SCOPE_IDENTITY();
                        COMMIT TRANSACTION
                END TRY

        BEGIN CATCH
                IF XACT_STATE() <> 0
                        ROLLBACK TRANSACTION
        END CATCH
END

---

USE statement?

Where are you executing this?

USE needs GO and only works in MANAGEMENT studio!

Regardless - show the whole SPROC please.

Are you creating these SPROC's on the fly and placing them in the database to be executed??

Why are you doing that?

Hi szlamany! I'm calling the SPROC from the front end. The SPROCs were made within SQL. Here's the code in question:

Code:

---

USE [dbMacondo]
GO
/****** Object:  StoredProcedure [dbo].[spNewPatient]    Script Date: 27/03/2016 10:42:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spNewPatient]
        @DateCreated                datetime,
        @DateModified                datetime,
        @PXFName                        nvarchar(50),
        @PXMName                        nvarchar(50),
        @PXLName                        nvarchar(50),
        @PXAge                                int,
        @StreetAddress                nvarchar(100),
        @City                                nvarchar(50),
        @ContactNum                        nvarchar(20),
        @MomFName                        nvarchar(50),
        @MomMName                        nvarchar(50),
        @MomLName                        nvarchar(50),
        @MomAge                                int,
        @IsMomEmployed                bit,
        @IsMomStudent                bit,
        @IsMomLiterate                bit,
        @IsMomHousewife                bit,
        @DadFName                        nvarchar(50),
        @DadMName                        nvarchar(50),
        @DadLName                        nvarchar(50),
        @DadAge                                int,
        @IsDadEmployed                bit,
        @IsDadStudent                bit,
        @IsDadLiterate                bit,
        @CivilStatus                nvarchar(20),
        @Meals                                nvarchar(20),
        @Water                                nvarchar(20),
        @Help                                bit,
        @FamIncome                        decimal(8,2),
        @FamEmployed                nvarchar(20),
        @LocProx                        nvarchar(20)
        --@PXID                                int OUTPUT
AS
BEGIN
        BEGIN TRANSACTION
                BEGIN TRY
                        INSERT INTO tblPatient (
                                PXFName,
                                PXMName,
                                PXLName,
                                PXAge,
                                StreetAddress,
                                City,
                                ContactNum,
                                MomFName,
                                MomMName,
                                MomLName,
                                MomAge,
                                IsMomEmployed,
                                IsMomStudent,
                                IsMomLiterate,
                                IsMomHousewife,
                                DadFName,
                                DadMName,
                                DadLName,
                                DadAge,
                                IsDadEmployed,
                                IsDadStudent,
                                IsDadLiterate,
                                CivilStatus,
                                Meals,
                                Water,
                                Help,
                                FamIncome,
                                FamEmployed,
                                LocProx,
                                DateCreated,
                                DateModified
                        ) VALUES (
                                @PXFName,
                                @PXMName,
                                @PXLName,
                                @PXAge,
                                @StreetAddress,
                                @City,
                                @ContactNum,
                                @MomFName,
                                @MomMName,
                                @MomLName,
                                @MomAge,
                                @IsMomEmployed,
                                @IsMomStudent,
                                @IsMomLiterate,
                                @IsMomHousewife,
                                @DadFName,
                                @DadMName,
                                @DadLName,
                                @DadAge,
                                @IsDadEmployed,
                                @IsDadStudent,
                                @IsDadLiterate,
                                @CivilStatus,
                                @Meals,
                                @Water,
                                @Help,
                                @FamIncome,
                                @FamEmployed,
                                @LocProx,
                                @DateCreated,
                                @DateModified
                        );

                        SELECT CAST(SCOPE_IDENTITY() AS INT);
                        COMMIT TRANSACTION
                END TRY

        BEGIN CATCH
                IF XACT_STATE() <> 0
                        ROLLBACK TRANSACTION
                RAISERROR (N'This is message %s %d.', 10, 1, N'number', 5);
        END CATCH
END
GO

---

Ok - you are using a SELECT statement to return a single row with the new id - that's cool.

Seems like odd code for creating parameters.

You are using EXECUTESCALAR which matches that SELECT of a single value very nicely. Why do you want to use OUTPUT parameter instead?

You have a transaction within the SPROC - so you DO NOT want to have a CLIENT SIDE transaction - get rid of that.

You should have "SET NOCOUNT ON" within the SPROC - at the very top - so that ROW count messages do not interfere with output to the client (old trick I have always used).

What error messages are you getting now? That SPROC code must have caused the client code you have that's calling the SPROC to change as well - right?

Here is a simple piece of code to call a SPROC

Code:

---

Try
    Using dcn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("LocalSQLServerAWC" & credDB).ToString)
        Using cmd As New SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "dbo.awc_ExcelProcess"
            cmd.Connection = dcn
            cmd.CommandTimeout = 0
            cmd.Parameters.AddWithValue("@SQLGuid", ctrlval2)
            cmd.Parameters.AddWithValue("@VisualName", vname)
            cmd.Parameters.AddWithValue("@username", username)
            dcn.Open()
            strMessage = cmd.ExecuteScalar().ToString
        End Using
    End Using
Catch ex As Exception
    strSuccess = ""
    strMessage = ex.Message.Replace("""", "'").Replace("\", "\\")
End Try

---

and the very simple SPROC

Code:

---

Use Acctfiles
Go
Drop Procedure awc_ExcelProcess
Go
Create Procedure awc_ExcelProcess @SQLGuid varchar(36), @VisualName varchar(255), @username varchar(100)
As
Set NoCount On
Begin Tran
Insert into ExcelProcess_T values (@SQLGuid,@VisualName,@username,GetDate())
Select Scope_Identity() "ExcelId"
Commit
Go
Grant Execute on awc_ExcelProcess to AcctfilesUser
Go

---