3 Replies - 2470 Views - Last Post: 23 July 2010 - 06:56 AM Rate Topic: -----

#1 vennesschan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 112
  • Joined: 20-April 10

button click to disable the sql query

Posted 21 July 2010 - 07:06 PM

Hi,

Can someone help me with the following code.

how can I disable the Page_Load sql query if the button1 is clicked.

Thanks!

  Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim StartDate As Date = txtStartDate.Text

        Dim EndDate As Date = txtEndDate.Text


        Dim sql As String = "SELECT Distinct * FROM [Report] WHERE [Cust]='" & NameLbl.Text & "' AND [t_date] BETWEEN '" & StartDate & "' AND '" & EndDate & "'"

        Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)

        Dim myAdapter As New SqlDataAdapter(sql, myConnection)
        myConnection.Open()


        Dim ds As Data.DataSet = New Data.DataSet
       
 ds.Clear()

        myAdapter.Fill(ds)

        GridView1.DataSource = ds
        GridView1.DataBind()

        myConnection.Close()


    End Sub

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

        Dim sName As String

        sName = Request.QueryString("Value")

        NameLbl.Text = (sName)


'How can I disable the following code when the button1 is clicked
        Dim StartDate As Date

        StartDate = Date.Today.AddDays(-1)

        Dim EndDate As Date

        EndDate = Date.Today.AddDays(-1)

        Dim sql As String = "SELECT Distinct * FROM [Report] WHERE [Cust]='" & NameLbl.Text & "' AND [t_date] BETWEEN '" & StartDate & "' AND '" & EndDate & "'"
       
        Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)

        Dim myAdapter As New SqlDataAdapter(sql, myConnection)
        myConnection.Open()


        Dim ds As Data.DataSet = New Data.DataSet
        myAdapter.Fill(ds)

        GridView1.DataSource = ds
        GridView1.DataBind()

        myConnection.Close()

    End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: button click to disable the sql query

#2 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1043
  • View blog
  • Posts: 4,057
  • Joined: 02-July 08

Re: button click to disable the sql query

Posted 21 July 2010 - 07:15 PM

Use a Boolean that you set with the click event. Then wrap that code with the If statement - so if you set the boolean to true then the code won't execute:

If Not <yourboolean> Then
'the code to run
End If

Was This Post Helpful? 0
  • +
  • -

#3 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 175
  • View blog
  • Posts: 636
  • Joined: 03-February 10

Re: button click to disable the sql query

Posted 23 July 2010 - 06:31 AM

Actually, hawkvalley1, that solution won't work for the initial click of the button. It appears to be a Web application and when the button is clicked during the postback the Page_Load() will be fired once before the button's click event is fired. I think that what vennesschan is looking for is to wrap all the querying code with:

 
If Not Page.IsPostBack Then
     'Query code
End If



@vennesschan, I would suggest creating a separate method to perform the querying and binding, and then call that method when you need to do the rebinding of the gridview. This will save you some coding, and improve the maintainability of your code.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
     'All of the original code can now be replaced with one call
     BindToGridView()

    End Sub

Public Sub BindToGridView()

        'Select statement
        ' making use of sql parameters -- this helps protect your data from sql injection attacks
        Dim sql As String = "SELECT Distinct * FROM [Report] WHERE [Cust]=@Cust AND [t_date] BETWEEN @StartDate AND @EndDate"

        'Dim sql As String = "SELECT Distinct * FROM [Report] WHERE [Cust]='" & NameLbl.Text & "' AND [t_date] BETWEEN '" & StartDate & "' AND '"  & EndDate & "'"
        'Wrapped in a Try .. Catch to handle errors

        Try

            'make connection object
            Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)

                'check to see if the connection is open, if not then open it
                If Not myConnection.State = ConnectionState.Open Then myConnection.Open()

                Using myCommand As New SqlCommand(sql, myConnection)
                    'add the parameters to the sql command object

                    '@Cust
                    myCommand.Parameters.Add(New SqlParameter("@Cust", NameLbl.Text))

                    '@StartDate
                    myCommand.Parameters.Add(New SqlParameter("@StartDate", txtStartDate.Text))

                    '@EndDate
                    myCommand.Parameters.Add(New SqlParameter("@EndDate", txtEndDate.Text))

                    'make the dataadapter
                    Using myDataAdapter As New SqlDataAdapter(myCommand)

                        'make the DataSet object
                        Using dsData As New DataSet

                            'Fill the dataset object
                            myDataAdapter.Fill(dsData)

                            'Set the datasource of the gridview object
                            GridView1.DataSource = dsData

                            'bind the data to the gridview
                            GridView1.DataBind()

                        End Using ' end of DataSet object
                    End Using 'end of dataadapter
                End Using 'end of command object
            End Using ' end of connection object
        Catch exp As SqlException

        Catch exp As Exception

        End Try
    End Sub

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

        Dim sName As String

        sName = Request.QueryString("Value")

        NameLbl.Text = (sName)


     'How can I disable the following code when the button1 is clicked
     'wrap this in a check for the post back
     If Not Page.IsPostBack
          BindToGridView()
     End If

    End Sub



Now the same thing happens on your page, but if the SELECT statement changes now you only have to make the change in one place plus the code is more readable.

So what is with the USING statement? In a nutshell, what this does is, it helps you dispose of managed and unmanaged objects like the connection object, without having to explicitly close and dispose of them. I also used a parameterized query instead of concatenating a string, mainly as I stated in the comments it is a safety issue (imho), it does not fit or work for all situations but if you can use it does make things easier. Not having to worry about single quotes can be a big stress relief.

This post has been edited by demausdauth: 23 July 2010 - 06:32 AM

Was This Post Helpful? 0
  • +
  • -

#4 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: button click to disable the sql query

Posted 23 July 2010 - 06:56 AM

This is an asp.net question isn't it?

In that case you have to think of the ASP.NET Page Life Cycle. In general it goes like this:

  • Page request: the browser sends a request for the page to the server and the server passes the request to ASP.NET
  • Page Initialization: ASP.NET initializes all of the server side objects required in order to process the request
  • Page Load: the controls have been initialized...the page is loaded
  • Postback Event Handling Code is Executed: events for any controls that caused the postback to happen are executed (for example: button click event, drop down list selected index changed, GridView editing etc etc)
  • PreRender: this occurs after everything has been executed, just before the controls are rendered as HTML and sent to the browser so that it can display the new page. It is the last point that you can make changes to controls
  • Rendering: controls are converted into HTML and sent to the browser
  • Unload: all of the controls/objects used for the page request are destroyed.


Your question: "How do I disable the Page_Load sql query if the button1 is clicked?"
The answer: you cannot because the page load event occurs before the button click event!

I suggest that you consider moving the SQL query from the Page Load event, to the Page PreRender event. That way you can set a page level variable (a boolean) that indicates whether or not you want to execute the SQL query.

-Frinny
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1