Runtime Error due to null

[lunchbox88]

Hey all.

I'm getting a runtime error when trying to go to a page and there is no record in the database.

I'll see if I can explain at all. User logs in and is shown a list of their accounts (works great). User can then select an account to view (also works great). User can then click a HyperLink and view the history on that account. This is where I'm having problems. It passes the account number to the View History page. If there is a record in the History Table, it works great. If there isn't, it gives me a runtime error. I've got the filling of the dataset in a try-catch block, but it still gives me a runtime error. Any ideas as to how to fix this? Does this even make sense?

Thanks in advance!

 

[ceyhorn]

Use an If statement to check for an empty string using this function by passing in your dbvalue.

Public Function FixNull(ByVal dbvalue) As String
        If dbvalue Is DBNull.Value Then
            Return ""
        Else
            'NOTE: This will cast value to string if
            'it isn't a string.

            Return dbvalue.ToString
        End If
    End Function

Hope this helps,

Chris

 

[BoulderBum]

May we see the lines producing the error?

 

[lunchbox88]

Sure can...

	Public Sub Page_Load(Source as Object, E as EventArgs) handles MyBase.Load
	
			
		strProspectID = Request.QueryString("ProspectID")
		dim strSQL as string = "SELECT isnull(dbo.tblHistory.NextContactDate, ' ') as NextContactDate, isnull(dbo.tblHistory.Date, ' ') as Date, isnull(dbo.tblHistory.ContactAbout, ' ') as ContactAbout, " _
							   & "isnull(dbo.tblHistory.Memo, ' ') as Memo, isnull(dbo.tblHistory.Presentations, ' ') as Presentations, isnull(dbo.tblContacts.ContactFName, ' ') as ContactFName, " _
							   & "isnull(dbo.tblContacts.ContactLName, ' ') as ContactLName, isnull(dbo.tblRep.RepFName, ' ') as RepFName, isnull(dbo.tblRep.RepLName, ' ') as RepLName, isnull(dbo.tblContacts.AccountNum, ' ') as AccountNum, " _
							   & "dbo.tblHistory.Done, isnull(dbo.tblHistory.HistoryID, ' ') as HistoryID, isnull(dbo.tblContacts.ContactID, ' ') as ContactID, isnull(dbo.tblRep.RepID, ' ') as RepID, " _
							   & "dbo.tblHistory.Blitz, dbo.tblHistory.CallBack, dbo.tblHistory.Competitor, dbo.tblHistory.db, dbo.tblHistory.Feature, " _
							   & "dbo.tblHistory.NonSales, dbo.tblHistory.NewLead, dbo.tblHistory.Upsell " _
							   & "FROM dbo.tblContacts JOIN dbo.tblHistory ON " _
							   & "(dbo.tblHistory.ContactID = dbo.tblContacts.ContactID) LEFT OUTER JOIN dbo.tblRep ON " _
							   & "(dbo.tblHistory.RepID = dbo.tblRep.RepID) WHERE dbo.tblHistory.ContactID = '" & strProspectID & "'"
				
		dmSQL = new SQLCommand(strSQL, objConnection)
		
		if not page.ispostback then
		
		try
		objConnection.Open()
		objHistory = new System.Data.DataSet()
		objAdapter = new SQLDataAdapter()
		objAdapter.SelectCommand=dmSQL
		objAdapter.Fill(objHistory, "History")
		
		response.write(objHistory.Tables(0).Rows.Count)
		
		rptrCOHist.DataSource=objHistory
		rptrCOHist.DataBind()
		
		catch ex as Exception
			Response.Write(ex.Message)
		finally 
			objConnection.Close()
		end try
		
		btnSubmit.Enabled = False
		end if
		
		hlMain.ImageURL = "../recruitment2/recruitmentmenubar_r2_c2.gif"
		hlMain.NavigateURL = "../recruitment2/MainForm.aspx?User=" & Request.QueryString("User")
		hlProspect.ImageURL = "../recruitment2/recruitmentmenubar_r2_c3.gif"
		hlProspect.NavigateURL = "../recruitment2/ProspectForm.aspx?User=" & Request.QueryString("User") & "&ProspectID=" & strProspectID
	
	End Sub

Hope this helps you help me.

 

[lunchbox88]

You can ignore the

response.write(objHistory.Tables(0).Rows.Count)

I should probably also mention that on my test server (local machine) I get the error "Object Reference not set to an instance of an object" and on the remote server I get a "Runtime Error."

Don't know if that makes a difference, but I thought I should let you know...

Thanks!

 

[lunchbox88]

Ok. I just realized that my previous post makes it sound like if there's a null value in the dataset, that's what's causing the error. I think I've accounted for nulls in the SQL statement, so that's not the issue. The problem is when there is NO record at all (no history for that account). That is what I'm having a problem with.

Hope this helps a little.

Thanks again!

 

[JCruz063]

lunchbox88,
The "Object reference not set to an instance of an object" error always occurs when you use an object that is null. In your case, the error appears when there are no records for a given account and that leads me to believe that the DataAdapter's Fill() method returns a null table if it has nothing to fill it up with. I haven't tested this, so don't quote me on it, but I'm quite positive that's what the problem is. In any case, do the following:
After you call the DataAdapter's Fill() method, do not try to access any of the History table's members before you check that it isn't null. Thus, do something like this:

objAdapter.Fill(objHistory, "History")
[b][COLOR=blue]If[/color] Not (objHistory.Tables(0) IsNull) [COLOR=blue]Then[/color][/b]
   [COLOR=green]' Access the table
   ' ...[/color]
[COLOR=blue]Else[/color]
[COLOR=green]   ' Do not access the table because it's null
   ' Inform the user that there's no History
   ' ...[/color]
[COLOR=blue]End If[/color]

About the line in bold above...I'm not sure if that's how you check for nullability on Visual Basic .NET as I've been working on C# for a while and kind of forgot a few things of Visual Basic. In any case, the line in bold is supposed to check if the table is null so do that whichever way it's done in Visual Basic. I believe that should work!

JC

Oh Wow!!! I was unaware of all the smileys available here at tek-tips.

 

[lunchbox88]

Thanks! That's kind of what I was thinking I needed to do, I just don't know the syntax for it. Unfortunately, yours doesn't work, so I'll keep searching for the proper syntax and hope that someone can post it here before I find it!

I'll have to find a way to send you some cookies in NY

 

[lunchbox88]

Okay, I tried:

if not ObjHistory.Tables(0).Rows is nothing then
   'set the data source for the repeater
   'call the repeaters databind() method
else
   'do something else
end if

I've never used "is nothing" before so I'm not sure that I'm doing the right thing here. I'm also starting to think that it's the stupid repeater that's making it hose up.

 

[JCruz063]

lunchbox88,
I think is this way:

[COLOR=blue]If Not[/color] IsNothing(objHistory.Tables(0)) [COLOR=blue]Then[/color]

JC


Oh Wow!!! I was unaware of all the smileys available here at tek-tips.

 

[lunchbox88]

Still getting the same error. But at least it didn't reject the syntax!

 

[lunchbox88]

Don't know if this helps. but here's the stack trace...I don't understand it, but maybe someone will

[NullReferenceException: Object reference not set to an instance of an object.]
   CompanyHistory.Page_Load(Object Source, EventArgs E) +609
   System.EventHandler.Invoke(Object sender, EventArgs e) +0
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +731

 

[ceyhorn]

Use my null function to check. It is posted above.

 

[JCruz063]

lunchbox88,
Which line does your code hangs at? I would like to see that specific line so go ahead and step through the code and let me see which line is causing all this trouble.

JC

Oh Wow!!! I was unaware of all the smileys available here at tek-tips.

 

[lunchbox88]

Well, I found my problem. See the line in my code that says "btnSubmit.Enabled = False"? That was holding it up. Because I'm using the repeater in the code behind page, and not directly in the HTML, there's a big long bunch of stuff you have to do to reference controls. It would work when there was data, I'm guessing, because since the button is inside the repeater it could find it.

ARGHHHHH. I'm really ready for the long weekend now.

Hope you guys/gals have a great 4th, and THANK YOU SO MUCH for all your help. Sorry it was just me being an idiot again.

 

[JCruz063]

lunchbox88,
Hey, don't be so hard on yourself now ! You have a great 4th of July as well. I'm glad everything's working now!

JC

Oh Wow!!! I was unaware of all the smileys available here at tek-tips.