Command.Prepare method is throwing NullReferenceException error

Discussion in 'ASP .Net' started by Sagar, Jun 8, 2008.

  1. Sagar

    Sagar Guest

    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, [email protected]_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
     
    Sagar, Jun 8, 2008
    #1
    1. Advertisements

  2. 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.
     
    Göran Andersson, Jun 8, 2008
    #2
    1. Advertisements

  3. Sagar

    Sagar Guest

    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"
     
    Sagar, Jun 9, 2008
    #3
  4. 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, Jun 10, 2008
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.