Command.Prepare method is throwing NullReferenceException error

[Sagar]

Hi.

I am working on a project to migrate a web application from 1.1 to 2.0

Within in the DAL of the application, there is a call to below
function that builds a command object for later use.

Inside this function iam getting a runtime error when the command
object calls the prepare method.

oCmd.Prepare()


The error is [NullReferenceException: Object reference not set to an
instance of an object.]
System.Data.SqlClient.SqlCommand.Prepare() +85

I debugged. The oCmd object is an instance and has values in most of
the properties and also the parameters collection of the oCmd object
is set with the right parameters and values.

I noticed that the command object does not have the 'connection'
property set. That might be the reason why it throws the
NullReferenceException. But, this app has been running successfully
in .net 1.1. Is the connection property required to be set to call the
prepare method.

Can anyone suggest what might be the reason for the
NullReferenceException exception ?

Note that the .ExecuteQuery method is NOT called in this function.



Protected Overrides Function Build_CommandObject () As
SqlClient.SqlCommand
Dim IsChanged As Boolean = False
Dim oCmd As SqlClient.SqlCommand


Try




' create command object and SP name
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd = New SqlClient.SqlCommand()
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "MYPROC_DO"

' declare params
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd.Parameters.Add("@Action", SqlDBType.SmallInt)
oCmd.Parameters.Add("@Record_GUID",
SqlDBType.uniqueidentifier, 16)
oCmd.Parameters.Add("@ACCOUNT_ID",
SqlDBType.uniqueidentifier, 16)
oCmd.Parameters.Add("@Employee_ID", SqlDBType.int)

oCmd.Parameters.Add("@Employee_LanguagePreference",
SqlDBType.char, 2)
oCmd.Parameters.Add("@Employee_Added_Date",
SqlDBType.datetime)

oCmd.Parameters.Add("@Cloned_Reason", SqlDBType.varchar,
100)
oCmd.Parameters.Add("@Current_TRXN_ID",
SqlDBType.uniqueidentifier, 16)
oCmd.Parameters.Add("@AuditLog_Application_ID",
SqlDBType.TinyInt)
oCmd.Parameters.Add("@User_ID", SqlDBType.uniqueidentifier,
16)
oCmd.Parameters.Add("@Process_ID",
SqlDBType.uniqueidentifier, 16)
oCmd.Parameters.Add("@DataChanged_UTC_Stamp",
SqlDBType.datetime)


If Me.SaveAction = Save_Type.Add

oCmd.Parameters.Add("@Record_UTC_StampOriginal",
SqlDBType.DateTime).Value = oDR(Fields.Record_UTC_Stamp)
Else

oCmd.Parameters.Add("@Record_UTC_StampOriginal",
SqlDBType.DateTime).Value = oDR(Fields.Record_UTC_Stamp,
DataRowVersion.Original)
End If

' set param values
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd.Parameters("@Action").Value =
System.Convert.ToInt16(Me.SaveAction)
oCmd.Parameters("@Record_GUID").Value =
Common_Data.Property_GET_Raw(oDR, Fields.Record_GUID)
oCmd.Parameters("@Client_ID").Value =
Common_Data.Property_GET_Raw(oDR, Fields.@ACCOUNT_ID)
oCmd.Parameters("@Employee_ID").Value =
Common_Data.Property_GET_Raw(oDR, Fields.Employee_ID)


oCmd.Parameters("@Employee_LanguagePreference").Value =
Common_Data.Property_GET_Raw(oDR, Fields.Employee_LanguagePreference)
oCmd.Parameters("@Employee_Added_Date").Value
= Common_Data.Property_GET_Raw(oDR, Fields.Employee_Added_Date)

oCmd.Parameters("@Cloned_Reason").Value =
Common_Data.Property_GET_Raw(oDR, Fields.Cloned_Reason)
oCmd.Parameters("@Current_TRXN_ID").Value =
Common_Data.Property_GET_Raw(oDR, Fields.Current_Event_TRXN_ID)

oCmd.Parameters("@AuditLog_Application_ID").Value =
_MyRoot.Application_ID
oCmd.Parameters("@User_ID").Value = _MyRoot.User_ID
oCmd.Parameters("@Process_ID").Value = _MyRoot.Process_ID
oCmd.Parameters("@DataChanged_UTC_Stamp").Value =
Common_Data.Property_GET_Raw(oDR, Fields.DataChanged_UTC_Stamp)


' prepare command
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd.Prepare()

Catch ex As Exception
Error_ToEventLog_SET(Ex)
Throw New Exception("Problem encountered building command
object", ex)
End Try

Return oCmd

End Function

Thanks,
AS

 

[Göran Andersson]

Hi.

I am working on a project to migrate a web application from 1.1 to 2.0

Within in the DAL of the application, there is a call to below
function that builds a command object for later use.

Inside this function iam getting a runtime error when the command
object calls the prepare method.

oCmd.Prepare()


The error is [NullReferenceException: Object reference not set to an
instance of an object.]
System.Data.SqlClient.SqlCommand.Prepare() +85

I debugged. The oCmd object is an instance and has values in most of
the properties and also the parameters collection of the oCmd object
is set with the right parameters and values.

I noticed that the command object does not have the 'connection'
property set. That might be the reason why it throws the
NullReferenceException. But, this app has been running successfully
in .net 1.1. Is the connection property required to be set to call the
prepare method.

Can anyone suggest what might be the reason for the
NullReferenceException exception ?

I don't know why there is a NullReferenceException. The Connection
property has to be set, and the connection has to be opened before
calling the Prepare method, but it should throw an
InvalidOperationException if that is not the case. So, according to the
documentation the code could never have worked anyway, but the exception
thrown is not the one expected.

Try to set the Connection and open it before calling Prepare, and see if
the exception goes away.

 

[Sagar]

No, I opened a connection and set it to the command object, the code
moves a little further and throws the same error.
I dont think the problem is with setting the connection object, mainly
this code works good in other machines where framework 1.1 is there.

I picked the exception data in debug mode. Can you give me some hints
by seeing this data ?

Message = "Object reference not set to an instance of an object."

InnerException = Nothing

Source = "System.Data"


StackTrace " at System.Data.SqlClient.SqlCommand.Prepare()
at MYSYSTEM.DAL.Employee_DAL.Emp.Build_CommandObject() in D:
\Workspace_820\VB.NET\MYSYSTEM.DAL.Employee_DAL\Emp.vb:line 137"

 

[Göran Andersson]

No, I opened a connection and set it to the command object, the code
moves a little further and throws the same error.
I dont think the problem is with setting the connection object, mainly
this code works good in other machines where framework 1.1 is there.

I picked the exception data in debug mode. Can you give me some hints
by seeing this data ?

Message = "Object reference not set to an instance of an object."

InnerException = Nothing

Source = "System.Data"


StackTrace " at System.Data.SqlClient.SqlCommand.Prepare()
at MYSYSTEM.DAL.Employee_DAL.Emp.Build_CommandObject() in D:
\Workspace_820\VB.NET\MYSYSTEM.DAL.Employee_DAL\Emp.vb:line 137"

The documentation clearly says that the connection has to be opened when
calling Prepare.

However, calling Prepare on a stored procedure may be the cause of your
problem. The documentation says:

"If CommandType is set to StoredProcedure, the call to Prepare should
succeed, although it may cause a no-op."

The solution would simply be not to call Prepare on a stored procedure,
as it doesn't server any purpose anyway.