Object reference not set to an instance of an object.

I am getting the following error from the below procedure at the high lighted line :

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

error line :     _CopyWorkflowItemID = command.ExecuteScalar().ToString()



Function:

 Function GetWorkFlowItemId(ByVal StID As String) As String

        Dim connection As SqlConnection = Nothing

        Try
            connection = New SqlConnection(strConnection)
            Dim command As New SqlCommand(Me.GetSPX("spx_StCopyForm_SELECT"), connection)
            command.CommandType = CommandType.StoredProcedure

            Dim StIDParam As SqlParameter = New SqlParameter("@StID", SqlDbType.VarChar, 50)
            StIDParam.Value = StID
            command.Parameters.Add(StIDParam)

            Dim StSetParam As SqlParameter = New SqlParameter("@StSet", SqlDbType.Int)
            StSetParam.Value = _StSet
            command.Parameters.Add(StSetParam)

            Dim workflowIDParam As SqlParameter = New SqlParameter("@WorkflowID", SqlDbType.VarChar, 50)
            workflowIDParam.Value = _WorkflowID
            command.Parameters.Add(workflowIDParam)
            connection.Open()
            _CopyWorkflowItemID = command.ExecuteScalar().ToString()
            If Not String.IsNullOrEmpty(_CopyWorkflowItemID) Then
                If _CopyWorkflowItemID <> _WorkflowItemID Then
                    Return _CopyWorkflowItemID
                End If
            End If
        Finally
            If Not connection Is Nothing Then connection.Close() : connection.Dispose()
        End Try

        Return _WorkflowItemID
    End Function

Open in new window



Please help me !
LVL 8
PeteEngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PeteEngineerAuthor Commented:
i think its coused because of no rows in the return of store proc
0
AndyAinscowFreelance programmer / ConsultantCommented:
Instead of
           _CopyWorkflowItemID = command.ExecuteScalar().ToString()

does this work
Object obj = command.ExecuteScalar()
if not isnull(obj) then           _CopyWorkflowItemID = obj.ToString()
0
RouchieCommented:
Check that spx_StCopyForm_SELECT always returns a value regardless of whether data is affected or not
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

PeteEngineerAuthor Commented:
I found no data in table ... and nor inserting any thing into this table anywhere in the application.

So can we do an error handling here .. ?

How to do that ...

can we do by checking null of that object using a if statement

if so , what we can write in the else statement to handle this ?

Please write the code by editing my previous function posted in question.
0
RouchieCommented:
What exactly is the purpose of spx_StCopyForm_SELECT ?
Can you post the code within it?  Once this is established we can look at where to place the handling.
0
PeteEngineerAuthor Commented:
its just a select command :



ALTER PROCEDURE [dbo].[spx_StDetailCopyForm_SELECT](
@StyleID uniqueidentifier,
@StyleSet int,
@WorkflowID uniqueidentifier)

AS 
BEGIN
	SELECT	 WorkFlowItemId 
	FROM	  pStDetailForm 
	WHERE	 StyleID = @StyleID AND 
			StyleSet = @StyleSet AND 
			WorkflowID = @WorkflowID
END

Open in new window

0
AndyAinscowFreelance programmer / ConsultantCommented:
>>can we do by checking null of that object using a if statement
See my first comment.
I'm not a VB specialist but basically split that line into two lines of code - so you can check if the command.ExecuteScalar is actually returning an object or null.
0
PeteEngineerAuthor Commented:
yes it is null ,

but i want to know what sepecally i need to handle ..what to put in else statement if it is not null
0
AndyAinscowFreelance programmer / ConsultantCommented:
?

if not isnull(obj) then          
  _CopyWorkflowItemID = obj.ToString()
  ... what you have at present
else
  msgbox "oops, nothing returned from the sql side of things"
end if
0
RouchieCommented:
Only you can answer your question.  We can write the code, but we need to know your preference, e.g. what is the code attempting to do, what should it do if it succeeds, and what if it fails?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeteEngineerAuthor Commented:
ok
0
Navneet.Net Full Stack DeveloperCommented:
H!!

Where is _StSet  and _WorkflowID declared?


Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.