Error: Object reference not set to an instance of an object

[Krissy]

Hi All!!

I occasionally get this error

--------------------------------------
System.NullReferenceException: Object reference not set to an instance of an
object.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

at System.Data.SqlClient.SqlCommand.ExecuteScalar()

-------------------------------------------

on this line in my code:

active = .ExecuteScalar


My Code is featured below.

Any idea why this error occurs? It only happens sometimes, not all the time
The page is running on IIS5, Windows 2000 server

Thanks for any help!!


------------------------


Public Shared sqlConnForSelect As SqlConnection =
MyClass.DataConnection.CreateSqlConnection()

Public Shared sqlcmdForSelect As New SqlCommand("", sqlConnForSelect)

Public Shared sqladp As New SqlDataAdapter(sqlcmdForSelect)


Public Shared Function ActiveCustomers(ByVal Agent As String) As Integer
Dim active As Integer

With sqlcmdForSelect

' *** Clear Parameters
.Parameters.Clear()
.CommandType = CommandType.Text

.CommandTimeout = 45

Dim SQLString As String

SQLString = "SELECT Active=count(id) " & _
" FROM cust.dbo.customer " & _
" WHERE agent is not null and active = 'Y' " & _
" AND agent = @Agent"

' *** Open Connection
If .Connection.State = ConnectionState.Open Then :
..Connection.Close() : End If
If .Connection.State = ConnectionState.Closed Then :
..Connection.Open() : End If

If SQLString <> "" Then
.CommandText = SQLString

.Parameters.Add("@Agent", SqlDbType.VarChar, 100).Value = Agent

active = .ExecuteScalar
Else
active = 0
End If

' *** Close Connection
If .Connection.State = ConnectionState.Open Then :
..Connection.Close() : End If
End With

Return active
End Function

 

[kevin]

Probably the connection object, but I would set break points and debug, or
do some

If IsNothing(objToTest) Then Response.Write("MyObject is Nothing here")

Kevin

 

[Val Mazur]

Hi,

If it fails on ExecuteScalar, then it looks like your Command variable is
destroyed at some moment of time. Try to put some debugging code into Catch
section to see if variable is not Nothing.

 

[Krissy]

I'll give that a go. Thanks

It is a bit hard to debug as it doesn't happen ALL the time

: Hi,
:
: If it fails on ExecuteScalar, then it looks like your Command variable is
: destroyed at some moment of time. Try to put some debugging code into
Catch
: section to see if variable is not Nothing.
:
: --
: Val Mazur
: Microsoft MVP
:
:
: : > Hi All!!
: >
: > I occasionally get this error
: >
: > --------------------------------------
: > System.NullReferenceException: Object reference not set to an instance
of
: > an
: > object.
: > at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
: > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
: >
: > at System.Data.SqlClient.SqlCommand.ExecuteScalar()
: >
: > -------------------------------------------
: >
: > on this line in my code:
: >
: > active = .ExecuteScalar
: >
: >
: > My Code is featured below.
: >
: > Any idea why this error occurs? It only happens sometimes, not all the
: > time
: > The page is running on IIS5, Windows 2000 server
: >
: > Thanks for any help!!
: >
: >
: > ------------------------
: >
: >
: > Public Shared sqlConnForSelect As SqlConnection =
: > MyClass.DataConnection.CreateSqlConnection()
: >
: > Public Shared sqlcmdForSelect As New SqlCommand("", sqlConnForSelect)
: >
: > Public Shared sqladp As New SqlDataAdapter(sqlcmdForSelect)
: >
: >
: > Public Shared Function ActiveCustomers(ByVal Agent As String) As Integer
: > Dim active As Integer
: >
: > With sqlcmdForSelect
: >
: > ' *** Clear Parameters
: > .Parameters.Clear()
: > .CommandType = CommandType.Text
: >
: > .CommandTimeout = 45
: >
: > Dim SQLString As String
: >
: > SQLString = "SELECT Active=count(id) " & _
: > " FROM cust.dbo.customer " & _
: > " WHERE agent is not null and active = 'Y' " & _
: > " AND agent = @Agent"
: >
: > ' *** Open Connection
: > If .Connection.State = ConnectionState.Open Then :
: > .Connection.Close() : End If
: > If .Connection.State = ConnectionState.Closed Then :
: > .Connection.Open() : End If
: >
: > If SQLString <> "" Then
: > .CommandText = SQLString
: >
: > .Parameters.Add("@Agent", SqlDbType.VarChar, 100).Value =
Agent
: >
: > active = .ExecuteScalar
: > Else
: > active = 0
: > End If
: >
: > ' *** Close Connection
: > If .Connection.State = ConnectionState.Open Then :
: > .Connection.Close() : End If
: > End With
: >
: > Return active
: > End Function
: >
: >
:
: