Gridview data populates only after button click.

[Ewan]

Hi

I have a gridview which should populate based on some criteria that the end user selects.

To enable the user to enter the required criteria i have added some Web Controls like Textbox,Drop Down Lists etc.



My code behind for getting the data is as below

Imports System.Data
Imports System.Data.SqlClient

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim myconnection As SqlConnection
        Dim mydataadapter As SqlDataAdapter
        Dim DS As DataSet


        If txtSearchRequest.Text = "" Then
            txtSearchRequest.Text = "%"
        End If

        myconnection = New SqlConnection("server=TestServer;database=TestDatabase;Trusted_Connection=yes")
        mydataadapter = New SqlDataAdapter("GetData", myconnection)
        mydataadapter.SelectCommand.CommandType = CommandType.StoredProcedure
        'Adding the parameters and assigning the values

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_request", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_request").Value = txtSearchRequest.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_process_status", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_process_status").Value = ddlProcessStatus.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_mitigated_status", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_mitigated_status").Value = ddlMitigatedStatus.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_age_slab", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_age_slab").Value = ddlAgeSlab.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_nssr", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_nssr").Value = ddlStandardNSSR.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_owner", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_owner").Value = ddlOwner.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_reassigned_to", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_reassigned_to").Value = ddlReassignedTo.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@ui_tool", SqlDbType.NVarChar, 4000))
        mydataadapter.SelectCommand.Parameters("@ui_tool").Value = ddlTool.Text

        mydataadapter.SelectCommand.Parameters.Add(New SqlParameter("@row_count", SqlDbType.Int))
        mydataadapter.SelectCommand.Parameters("@row_count").Direction = ParameterDirection.Output


        DS = New DataSet()
        mydataadapter.Fill(DS, "mydata")

        lblGetCount.Text = DS.Tables(0).Rows.Count().ToString()
        lblGetCount.Text = mydataadapter.SelectCommand.Parameters("@row_count").Value & " Records Found!"
        gridview1.DataSource() = DS.Tables("mydata").DefaultView
        gridview1.DataBind()

        mydataadapter.Dispose()
        myconnection.Close()


    End Sub

Here the value from each Textbox or Drop Down List is assigned to the SQL Parameter. which is passed on to the Stored Procedure "GetData"

My issue is that when the page loads i want all the records to be displayed.
However this is not the case. The records get populated in the Gridview only if i click on a Button.

Could anyone advise me how the same could happen at the Page_Load stage, or what could be causing the issue.

[kadghar]

a couple of IFs should do:

If Parameter1 <> nothing then
    'your code for dynamic SQL
Else
    'your Data Adapter for all entries
End If

[Ewan]

Hi kadghar,
Thanks for your reply. i have infact handled this in the SQL Procedure, and this works fine.

However my concern is that when the page loads, the gridview is not populated. Only when i click the search button it works. Im guessing this is trigrering a Post Back, and data is displayed only after the post back occurs.

My concern is : how do i get this to display when the page is loaded.

[kadghar]

At a first glance, your code in your Load event seems right. But are you sure you're retrieving the info? are you calling the right params to retrieve alll the info when there are actually no user parameters?

[Ewan]

Hi kadghar

Thanks foy your reply

yes i am updating all parameters to get the data.

pasting my SQL Code (Procedure) below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go








ALTER PROCEDURE [dbo].[GetHPSMQuestData]
	@ui_request NVARCHAR (4000),
	@ui_process_status NVARCHAR (4000),
	@ui_mitigated_status NVARCHAR (4000),
	@ui_age_slab NVARCHAR (4000),
	@ui_nssr NVARCHAR (4000),
	@ui_owner NVARCHAR (4000),
	@ui_reassigned_to NVARCHAR (4000),
	@ui_tool NVARCHAR (4000),
	@row_count INT OUTPUT


AS
DECLARE
	@fi_request NVARCHAR (4000),
	@fi_process_status NVARCHAR (4000),
	@fi_mitigated_status NVARCHAR (4000),
	@fi_age_slab NVARCHAR (4000),
	@fi_nssr NVARCHAR (4000),
	@fi_owner NVARCHAR (4000),
	@fi_reassigned_to NVARCHAR (4000),
	@fi_tool NVARCHAR (4000),
	@select NVARCHAR(4000),
	@cols_part1 NVARCHAR(4000),
	@cols_part2 NVARCHAR(4000),
	@where NVARCHAR (4000)
	

--set the parameters
SET @fi_request = 'LIKE '+''''+@ui_request+''''
	IF @ui_request = '%'
		SET @fi_request = 'LIKE '+'''%'''

SET @fi_process_status = 'LIKE '+''''+@ui_process_status+''''
	IF @ui_process_status = '%'
		SET @fi_process_status = 'LIKE '+'''%'''

SET @fi_mitigated_status = 'LIKE '+''''+@ui_mitigated_status+''''
	IF @ui_mitigated_status = '%'
		SET @fi_mitigated_status = 'LIKE '+'''%'''

SET @fi_age_slab = 'LIKE '+''''+@ui_age_slab+''''
	IF @ui_age_slab = '%'
		SET @fi_age_slab = 'LIKE '+'''%'''

SET @fi_nssr = 'LIKE '+''''+@ui_nssr+''''
	IF @ui_nssr = '%'
		SET @fi_nssr = 'LIKE '+'''%'''

SET @fi_owner = 'LIKE '+''''+@ui_owner+''''
	IF @ui_owner = '%'
		SET @fi_owner = 'LIKE '+'''%'''

SET @fi_reassigned_to = 'LIKE '+''''+@ui_reassigned_to+''''
	IF @ui_reassigned_to = '%'
		SET @fi_reassigned_to = 'LIKE '+'''%'''

SET @fi_tool = 'LIKE '+''''+@ui_tool+''''
	IF @ui_tool = '%'
		SET @fi_tool = 'LIKE '+'''%'''


--build the SQL statement
SET @select =		'SELECT '
SET @cols_part1 = 
					'CONVERT(NVARCHAR,[wr_picked],3) as [Picked],
					[request] as [Request],
					[description] as [Description],
					[WRE],
					[OpCo],
					[delivery_days] as [Age],
					[age_group] as [Age Group],
					[NSSR],[OWNER],
					[escalation_status] as [Escalation Status],
					[escalated_to(specific_name)] as [Escalated To],
					CONVERT(NVARCHAR,[escalated_date_to_RG],3) AS [Escalated Date],'
SET @cols_part2 =
					'[resolver_teams] as [Resolver Team],
					CONVERT(NVARCHAR,[ETA_provided],3) as [ETA],
					[Customer_Affecting] as [Customer Affecting],
					CONVERT(NVARCHAR,[date_last_update_given_to_requester/user],3) as [Last Update Given],
					[reason_for_delay] as [Delay Reason],
					convert(NVARCHAR(100),[action_taken])+''...''as [Agent Comments],
					[delay_cat] as [DelayCatagory],
					[Tool] 
					from dbo.test_environ_hpsm '
SET @where =		'WHERE request '+ @fi_request+
					' AND ISNULL(process_status,'''') '+ @fi_process_status+
					' AND ISNULL(mitigated_status,'''') '+ @fi_mitigated_status+
					' AND ISNULL(age_group,'''') '+ @fi_age_slab+
					' AND ISNULL(NSSR,'''') '+ @fi_nssr+
					' AND ISNULL(OWNER,'''') '+ @fi_owner+
					' AND ISNULL(reassigned_to,'''') '+ @fi_reassigned_to+
					' AND ISNULL(Tool,'''') '+ @fi_tool
--run the SQL Statement
EXECUTE (@select+@cols_part1+@cols_part2+@where)

SELECT @row_count = @@ROWCOUNT

I have updated the frontend data to select % as a default parameter (on first load) for all the filters. (Same as the pic attached)
If a field carries %, this gets handled accordingly in the SQL Procedure.

Getting the data is not the issue.. the issue is the data does not populate at on page load, it populates only after the Search button (shown in the attached pic) is clicked.

[kadghar]

I see, why don't you put a breakpoint in the Load event and allow debuging, just to see if the SUB is running complete an correctly?

Perhaps the issue is with te IF/THEN you put for txtSearchReques t.Text = ""

[Ewan]

Hi kadghar

Tried the breakpoint and debugging option. The Sub runs without any errors.

[Ewan]

Hi I managed to get a fix to this.
Not sure if this is the best option to choose, but it works.

The issues was, the databind for filters were happening together with gridview databind , so posibilities are that correct/all parameters are not passed on to the SQl Procedure to get the data as required and all required parameters get passed on correctly when the Search button is clicked(This is what i presumed).

So i update the code in Page_PreInit to bing the filters before the page_load event, so in page load event the parameters get passed on as required.

    Private Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
        If Not IsPostBack Then
            ddlAgeSlab.DataBind()
            ddlProcessStatus.DataBind()
            ddlMitigatedStatus.DataBind()
            ddlStandardNSSR.DataBind()
            ddlOwner.DataBind()
            ddlTool.DataBind()
            ddlReassignedTo.DataBind()
        End If
    End Sub

[kadghar]

Thats a good solution. ^.^
Or you could just simple put the "empty" parameters when is not posting back on the load form.