Object Reference not set to an Instance of and Object

Hi there,
I am coming across an error need your help. I have just taken over someone else's work.
I have got a search form and the T-SQL for that is
ALTER PROCEDURE [dbo].[prPatientSearch_v2] 
            (
			@patientid varchar(50),
            @forename varchar(50),
            @surname varchar(50),
            @dob datetime,
            @nhs varchar(10),
            @ccg varchar(5),
            @unit varchar(10),
			@service varchar(10),
			@security varchar(10),
			@sex varchar(10),
			@rolename varchar(50),
			@userid varchar(50),
			@areaID nvarchar(max),
			@UserCCGs_csv nvarchar(max),
			@ServiceCode_Patient nvarchar(1),
			@showall nvarchar(1)
            )

AS
    DECLARE @IsCaseManager bit = 0
    DECLARE @IsClearData varchar(10) = 'none'
    DECLARE @search varchar(max)
    DECLARE @PatientIdentifiableFields varchar(max)

    SELECT @search = '' 
    SELECT @PatientIdentifiableFields = ''     

		
	IF @rolename='WebAdmin' OR @rolename='CaseManager and SuperUser' OR @rolename='Case Manager'
	BEGIN
		SELECT @IsClearData = 'webadmin'
		SELECT @PatientIdentifiableFields = 
		'p.nhs_no,p.Forename,p.Surname,p.DoB,'
	END
	ELSE
	BEGIN
		SELECT @PatientIdentifiableFields = ''''' AS nhs_no,
											 '''' AS Forename,
											 '''' AS Surname,
											 NULL AS DoB,'
	END

	--IF @PatientIdentifiableFields = ''
	--BEGIN
	--	SELECT @PatientIdentifiableFields = ''''' AS nhs_no,
	--										 '''' AS Forename,
	--										 '''' AS Surname,
	--										 NULL AS DoB,'
	--END
	
	IF @IsClearData = 'cm' 
	BEGIN	
		IF @showall <> 'Y'
		BEGIN
			SELECT @search = @search + 'cm.CASEMANAGERID = ' + @userid + ' AND '
		END
    END
	IF @IsClearData <> 'webadmin'
	BEGIN
        SELECT @search = @search + '(((p.AreaTeamCode = ' + @areaID + ' OR
									 p.AreaTeamCode IS NULL)) OR 
									  (p.ServiceCode = ''C'' AND (p.PCT_CODE IN (' + @UserCCGs_csv + ') OR p.PCT_CODE IS NULL))) AND '

	END
	
    IF @ServiceCode_Patient = 'A' OR @ServiceCode_Patient = 'C'
    BEGIN
        SELECT @search = @search + 'p.ServiceCode = ''' + @ServiceCode_Patient + ''' AND '
    END

    IF @patientid <> ''
    BEGIN
        SELECT @search = @search + 'p.patientid = ' + @patientid + ' AND '
    END

    IF @forename <> ''
    BEGIN
        SELECT @search = @search + 'p.forename like ''%' + @forename + '%'' AND '
    END

    IF @surname <> ''
    BEGIN
        SELECT @search = @search + 'p.surname Like ''%' + @surname + '%'' AND '
    END

    IF @ccg <> '' 

    BEGIN
        SELECT @search = @search + 'p.PCT_code = ''' + @ccg + ''' AND '
    END

	IF @unit <> ''
    BEGIN
        SELECT @search = @search + 'p.Unitcode = ''' + @unit + ''' AND '
    END

	IF @service <> ''
    BEGIN
        SELECT @search = @search + 'p.ServiceTypeID = ''' + @service + ''' AND '
    END

	IF @security <> ''
    BEGIN
        SELECT @search = @search + 'p.PatientSecurity = ''' + @security + ''' AND '
    END

    IF @dob <> ''
    BEGIN
        SELECT @search = @search + 'p.dob = ''' + Convert(varchar(20),@dob) + ''' AND '
    END

    IF @sex <> ''
    BEGIN
        SELECT @search = @search + 'p.Sex = ''' + @sex + ''' AND '
    END

    IF @nhs <> ''
    BEGIN
		IF Len(@nhs) < 10
		BEGIN
			SELECT @search = @search + 'p.nhs_no Like ''' + @nhs + '%'' AND '
		END
		ELSE
		BEGIN
			SELECT @search = @search + 'p.nhs_no = ''' + @nhs + ''' AND '
		END
    END

	--IF @ServiceCode_Admission <> 'A' AND @ServiceCode_Admission <> 'C'
	--BEGIN
		IF @search <> ''
		BEGIN
			SELECT @search = Left(@search,len(@search)-4)
			
			EXEC ('SELECT DISTINCT p.Patientid, ' + @PatientIdentifiableFields +
					'p.AreaID,p.CCGname,p.UnitName,
					p.PCT_Code,p.Unitcode, p.Status, p.Sex, p.secure, p.ServiceTypeID, p.PatientSecurity, p.Rating, 
					p.DateonWL, p.ServiceCode, p.DateRemoved, cm.CASEMANAGERID, cm.Firstname AS cmForename, cm.Surname AS cmSurname,  cm.Firstname  + '' '' + cm.Surname AS CaseManagerName
					FROM vw_Patients_Search p 
					LEFT OUTER JOIN vw_usercm cm ON (cm.PatientId = p.PatientID AND cm.CurrentRec = 1 AND cm.[Primary] = 1)
						 WHERE ' + @search + ' ORDER BY Surname, forename COMPUTE count(p.Patientid);')

		END
		ELSE
		BEGIN
			EXEC ('SELECT DISTINCT p.Patientid, ' + @PatientIdentifiableFields +
					'p.AreaID,p.CCGname,p.UnitName,
					p.PCT_Code,p.Unitcode, p.Status, p.Sex, p.secure, p.ServiceTypeID, p.PatientSecurity, p.Rating, 
					p.DateonWL, p.ServiceCode, p.DateRemoved, cm.CASEMANAGERID, cm.Firstname AS cmForename, cm.Surname AS cmSurname,  cm.Firstname  + '' '' + cm.Surname AS CaseManagerName
					FROM vw_Patients_Search p 
					LEFT OUTER JOIN vw_usercm cm ON (cm.PatientId = p.PatientID AND cm.CurrentRec = 1 AND cm.[Primary] = 1)
					LEFT OUTER JOIN tblAdmission a ON (a.PatientId = p.PatientID AND a.Act_Dischar_date IS NULL)
						  ORDER BY Surname, forename COMPUTE count(p.Patientid);')
		END

Open in new window


and the ASP.Net page is
<asp:GridView ID="GridView1" runat="server" Style="border: solid 1px #ccc" CssClass="grad_new"
            DataSourceID="SqlDataSource1" DataKeyNames="patientid" AutoGenerateColumns="False" EmptyDataText ="No Patients">
            <Columns>
                <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
                <asp:BoundField DataField="DateonWL" HeaderText="DateonWL" SortExpression="DateonWL" />
                <asp:BoundField DataField="DateRemoved" HeaderText="DateRemoved" SortExpression="DateRemoved" />
                <asp:TemplateField HeaderText="patientid" SortExpression="patientid">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("patientid") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label95" runat="server" Text=""></asp:Label>
                        <asp:LinkButton ID="lbSelect" runat="server" CommandName="Select" CommandArgument='<%# Bind("patientid") %>'
                            Text='<%# Bind("patientid") %>' OnClick="lbSelect_Click"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="Surname" HeaderText="Surname" SortExpression="Surname" />
                <asp:BoundField DataField="Forename" HeaderText="Forename" SortExpression="Forename" />
                <asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" DataFormatString="{0:d}"
                    HtmlEncode="false" />
                <asp:BoundField DataField="NHS_no" HeaderText="NHS_no" SortExpression="NHS_no" />
                <asp:BoundField DataField="ServiceCode" HeaderText="Service" SortExpression="Service" />
                <asp:BoundField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
                <asp:BoundField DataField="CCGname" HeaderText="CCGname" SortExpression="CCGname" />
                <asp:BoundField DataField="UnitName" HeaderText="UnitName" SortExpression="UnitName" />
                <asp:BoundField DataField="ServiceTypeID" HeaderText="ServiceTypeID" SortExpression="ServiceTypeID" />
                <asp:BoundField DataField="Rating" HeaderText="Rating" SortExpression="Rating" />
                <asp:BoundField DataField="CaseManagerName" HeaderText="Case Manager" SortExpression="CaseManagerName" />
            </Columns>
            <EmptyDataTemplate>
                <p>
                    <strong>Sorry, no records have been found that match your criteria.</strong></p>
            </EmptyDataTemplate>
        </asp:GridView>
        <asp:HiddenField ID="hfShowAll" runat="server" Value="N" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SMHConnectionString %>"
            SelectCommand="prPatientSearch_V2" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
            <SelectParameters>
                <asp:ControlParameter ControlID="txtPatientId" Name="patientid" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="txtForename" Name="forename" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="txtSurname" Name="surname" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="txtDoB" Name="dob" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="DateTime" />
                <asp:ControlParameter ControlID="txtNHSNo" Name="nhs" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="drpCCG" Name="ccg" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpUnit" Name="unit" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpServiceType" Name="service" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpSecurityLevel" Name="security" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpSex" Name="sex" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:SessionParameter SessionField="Role" Name="rolename" />
                <asp:SessionParameter SessionField="UserID" Name="userid" />
                <asp:SessionParameter SessionField="area" Name="areaid" />
                <asp:SessionParameter SessionField="UserCCGs_csv" Name="UserCCGs_csv" />
                <asp:ControlParameter ControlID="drpServicePatient" Name="ServiceCode_Patient" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="hfShowAll" Name="showall" PropertyName="Value" ConvertEmptyStringToNull="true" />                    
            </SelectParameters>
        </asp:SqlDataSource>

Open in new window


When the data is returned then it displays the data but when a null record is returned i get the error
Error
I think if there is no record returned then there shouldn't be any error and gridview should not display the reocords/

There are also Row data bound
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound

        If e.Row.RowType = DataControlRowType.DataRow Then

            Dim Stat As String
            Dim WL As String
            Dim DR As String
            Dim ab As String


            ' **************  Active Status *************************
            Stat = "<img src='../images/tree2/icons/ball_glass_blueS.gif'>"

            If e.Row.Cells(0).Text = "Active" Then
                Stat = "<img src='../images/tree2/icons/ball_glass_greenS.gif'>"
            End If

            ' **************  Waiting List Status *************************
            ab = "&nbsp;"
            WL = 0
            DR = 0
            If e.Row.Cells(1).Text <> "&nbsp;" Then
                WL = 1
            End If

            If e.Row.Cells(2).Text = "&nbsp;" Then
                DR = 1
            End If
            If WL = 1 And DR = 1 Then

                ab = "<img src='../images/tree2/icons/ball_glass_OrangeS.gif'>"
            End If



            e.Row.Cells(0).Text = Stat
            e.Row.Cells(1).Text = ab

            e.Row.Cells(2).Visible = False
        End If


        If e.Row.RowType = DataControlRowType.Header Then

            e.Row.Cells(0).Text = ""
            e.Row.Cells(1).Text = ""
            e.Row.Cells(2).Visible = False

        End If


    End Sub

Open in new window


and Databound events for the gridview
Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.DataBound


        Dim xy As Integer = GridView1.Rows.Count
        Dim xya As Integer = GridView1.PageCount

        Dim va As String = xy
        If xy = 0 Then
            va = "There are no Patients"
        End If
        If xy = 1 Then
            va = va + " Patient"
        End If
        If xy > 1 Then
            va = va + " Patients"
        End If

        lblCount.Text = va


    End Sub

Open in new window


looking forward for your help.
Ali ShahSQL DeveloperAsked:
Who is Participating?
 
CodeCruiserCommented:
Comment out all code in DataBound and RowDataBound events and try again.
0
 
CodeCruiserCommented:
Have you stepped through the DataBound and RowDataBound event code to see if one of the lines there is causing the error?
0
 
Ali ShahSQL DeveloperAuthor Commented:
Yes i have done so. When page first loads it goes into debugging and does not give any errors. When i debug row databound and then databound and when it comes back to rowdatabound it displays a message
There is no source code available for the current location
When i press F5 and then enter my search criteria it does not go back to debugging and gives the above error message of object soruce

regards
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Run the Stored Procedure in SSMS and check which row and column has null.
0
 
Ali ShahSQL DeveloperAuthor Commented:
If record doesn not exist it returns the null row in stored procedure.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I misunderstood your question.
So if the problem is only when no rows are returned, can't you validate it before fill the grid?
0
 
Ali ShahSQL DeveloperAuthor Commented:
Thanks for the message again. Do i need to do it on code behind just before the Gridview1.DataBind()?
something like If gridview1.rows.count > 0?

sorry but actually i am not into programming a lot

regards
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see your problem. I'm also not a programmer. I'm more a SQL Server guy.
What lines of code do you have before the GridView1.Visible = True?
And where's the code that calls prPatientSearch_v2 stored procedure?
0
 
Ali ShahSQL DeveloperAuthor Commented:
It is not validating the data at all.
in asp.net form the gridview is
asp:GridView ID="GridView1" runat="server" Style="border: solid 1px #ccc" CssClass="grad_new"
            DataSourceID="SqlDataSource1" DataKeyNames="patientid" AutoGenerateColumns="False" EmptyDataText ="No Patients">
            <Columns>
                <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
                <asp:BoundField DataField="DateonWL" HeaderText="DateonWL" SortExpression="DateonWL" />
                <asp:BoundField DataField="DateRemoved" HeaderText="DateRemoved" SortExpression="DateRemoved" />
                <asp:TemplateField HeaderText="patientid" SortExpression="patientid">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("patientid") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label95" runat="server" Text=""></asp:Label>
                        <asp:LinkButton ID="lbSelect" runat="server" CommandName="Select" CommandArgument='<%# Bind("patientid") %>'
                            Text='<%# Bind("patientid") %>' OnClick="lbSelect_Click"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="Surname" HeaderText="Surname" SortExpression="Surname" />
                <asp:BoundField DataField="Forename" HeaderText="Forename" SortExpression="Forename" />
                <asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" DataFormatString="{0:d}"
                    HtmlEncode="false" />
                <asp:BoundField DataField="NHS_no" HeaderText="NHS_no" SortExpression="NHS_no" />
                <asp:BoundField DataField="ServiceCode" HeaderText="Service" SortExpression="Service" />
                <asp:BoundField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
                <asp:BoundField DataField="CCGname" HeaderText="CCGname" SortExpression="CCGname" />
                <asp:BoundField DataField="UnitName" HeaderText="UnitName" SortExpression="UnitName" />
                <asp:BoundField DataField="ServiceTypeID" HeaderText="ServiceTypeID" SortExpression="ServiceTypeID" />
                <asp:BoundField DataField="Rating" HeaderText="Rating" SortExpression="Rating" />
                <asp:BoundField DataField="CaseManagerName" HeaderText="Case Manager" SortExpression="CaseManagerName" />
            </Columns>
            <EmptyDataTemplate>
                <p>
                    <strong>Sorry, no records have been found that match your criteria.</strong></p>
            </EmptyDataTemplate>
        </asp:GridView>
        <asp:HiddenField ID="hfShowAll" runat="server" Value="N" />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SMHConnectionString %>"
            SelectCommand="prPatientSearch_V2" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
            <SelectParameters>
                <asp:ControlParameter ControlID="txtPatientId" Name="patientid" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="txtForename" Name="forename" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="txtSurname" Name="surname" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="txtDoB" Name="dob" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="DateTime" />
                <asp:ControlParameter ControlID="txtNHSNo" Name="nhs" PropertyName="Text" ConvertEmptyStringToNull="true"
                    Type="String" />
                <asp:ControlParameter ControlID="drpCCG" Name="ccg" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpUnit" Name="unit" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpServiceType" Name="service" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpSecurityLevel" Name="security" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="drpSex" Name="sex" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:SessionParameter SessionField="Role" Name="rolename" />
                <asp:SessionParameter SessionField="UserID" Name="userid" />
                <asp:SessionParameter SessionField="area" Name="areaid" />
                <asp:SessionParameter SessionField="UserCCGs_csv" Name="UserCCGs_csv" />
                <asp:ControlParameter ControlID="drpServicePatient" Name="ServiceCode_Patient" ConvertEmptyStringToNull="true"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="hfShowAll" Name="showall" PropertyName="Value" ConvertEmptyStringToNull="true" />                    
            </SelectParameters>
        </asp:SqlDataSource>
0
 
CodeCruiserCommented:
>If record doesn not exist it returns the null row in stored procedure.

Do you mean it does not return anything or is it returning a row full of nulls? Grid should not having a problem if there is no record to display. This problem only appears when your code tries to do things like access rows or columns that do not exist or try to use values from cells that have nulls.
0
 
Ali ShahSQL DeveloperAuthor Commented:
Well i have tested in sql and it returns no record at all. it comes up with column header and thats it.
I am quite surprised as well as i think if there are no records then grid show not display anything instead of throwing null exception error
0
 
Ali ShahSQL DeveloperAuthor Commented:
Yes it works like that. RowDataBound was not giving any value and error gone by commentingt out this.

cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.