Command.Prepare method is throwing NullReferenceEx ception 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 [NullReferenceEx ception: Object reference not set to an
instance of an object.]
System.Data.Sql Client.SqlComma nd.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
NullReferenceEx ception. 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
NullReferenceEx ception exception ?

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

Protected Overrides Function Build_CommandOb ject () As
SqlClient.SqlCo mmand
Dim IsChanged As Boolean = False
Dim oCmd As SqlClient.SqlCo mmand
Try


' create command object and SP name
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd = New SqlClient.SqlCo mmand()
oCmd.CommandTyp e = CommandType.Sto redProcedure
oCmd.CommandTex t = "MYPROC_DO"

' declare params
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd.Parameters .Add("@Action", SqlDBType.Small Int)
oCmd.Parameters .Add("@Record_G UID",
SqlDBType.uniqu eidentifier, 16)
oCmd.Parameters .Add("@ACCOUNT_ ID",
SqlDBType.uniqu eidentifier, 16)
oCmd.Parameters .Add("@Employee _ID", SqlDBType.int)

oCmd.Parameters .Add("@Employee _LanguagePrefer ence",
SqlDBType.char, 2)
oCmd.Parameters .Add("@Employee _Added_Date",
SqlDBType.datet ime)

oCmd.Parameters .Add("@Cloned_R eason", SqlDBType.varch ar,
100)
oCmd.Parameters .Add("@Current_ TRXN_ID",
SqlDBType.uniqu eidentifier, 16)
oCmd.Parameters .Add("@AuditLog _Application_ID ",
SqlDBType.TinyI nt)
oCmd.Parameters .Add("@User_ID" , SqlDBType.uniqu eidentifier,
16)
oCmd.Parameters .Add("@Process_ ID",
SqlDBType.uniqu eidentifier, 16)
oCmd.Parameters .Add("@DataChan ged_UTC_Stamp",
SqlDBType.datet ime)
If Me.SaveAction = Save_Type.Add

oCmd.Parameters .Add("@Record_U TC_StampOrigina l",
SqlDBType.DateT ime).Value = oDR(Fields.Reco rd_UTC_Stamp)
Else

oCmd.Parameters .Add("@Record_U TC_StampOrigina l",
SqlDBType.DateT ime).Value = oDR(Fields.Reco rd_UTC_Stamp,
DataRowVersion. Original)
End If

' set param values
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd.Parameters ("@Action").Val ue =
System.Convert. ToInt16(Me.Save Action)
oCmd.Parameters ("@Record_GUID" ).Value =
Common_Data.Pro perty_GET_Raw(o DR, Fields.Record_G UID)
oCmd.Parameters ("@Client_ID"). Value =
Common_Data.Pro perty_GET_Raw(o DR, Fields.@ACCOUNT _ID)
oCmd.Parameters ("@Employee_ID" ).Value =
Common_Data.Pro perty_GET_Raw(o DR, Fields.Employee _ID)
oCmd.Parameters ("@Employee_Lan guagePreference ").Value =
Common_Data.Pro perty_GET_Raw(o DR, Fields.Employee _LanguagePrefer ence)
oCmd.Parameters ("@Employee_Add ed_Date").Value
= Common_Data.Pro perty_GET_Raw(o DR, Fields.Employee _Added_Date)

oCmd.Parameters ("@Cloned_Reaso n").Value =
Common_Data.Pro perty_GET_Raw(o DR, Fields.Cloned_R eason)
oCmd.Parameters ("@Current_TRXN _ID").Value =
Common_Data.Pro perty_GET_Raw(o DR, Fields.Current_ Event_TRXN_ID)

oCmd.Parameters ("@AuditLog_App lication_ID").V alue =
_MyRoot.Applica tion_ID
oCmd.Parameters ("@User_ID").Va lue = _MyRoot.User_ID
oCmd.Parameters ("@Process_ID") .Value = _MyRoot.Process _ID
oCmd.Parameters ("@DataChanged_ UTC_Stamp").Val ue =
Common_Data.Pro perty_GET_Raw(o DR, Fields.DataChan ged_UTC_Stamp)
' prepare command
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - -
oCmd.Prepare()

Catch ex As Exception
Error_ToEventLo g_SET(Ex)
Throw New Exception("Prob lem encountered building command
object", ex)
End Try

Return oCmd

End Function

Thanks,
AS

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Sagar wrote:

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 [NullReferenceEx ception: Object reference not set to an
instance of an object.]
System.Data.Sql Client.SqlComma nd.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
NullReferenceEx ception. 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
NullReferenceEx ception exception ?

I don't know why there is a NullReferenceEx ception. The Connection
property has to be set, and the connection has to be opened before
calling the Prepare method, but it should throw an
InvalidOperatio nException 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.

--
Göran Andersson
_____
http://www.guffa.com

[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.Dat a"
StackTrace " at System.Data.Sql Client.SqlComma nd.Prepare()
at MYSYSTEM.DAL.Em ployee_DAL.Emp. Build_CommandOb ject() in D:
\Workspace_820\ VB.NET\MYSYSTEM .DAL.Employee_D AL\Emp.vb:line 137"

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Sagar wrote:

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.Dat a"
StackTrace " at System.Data.Sql Client.SqlComma nd.Prepare()
at MYSYSTEM.DAL.Em ployee_DAL.Emp. Build_CommandOb ject() in D:
\Workspace_820\ VB.NET\MYSYSTEM .DAL.Employee_D AL\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.

--
Göran Andersson
_____
http://www.guffa.com