Question about SQL Transaction/Stored Procedures and Identity Fields

[riechan]

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?

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

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?

[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?

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.

[TysonLPrice]

Try adding this in the SPROC

@PXID int OUTPUT =0,

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

[riechan]

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.

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?

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.

    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.

[szlamany]

What is this?

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?

[riechan]

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

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

[szlamany]

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

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

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