Run SQL Query on button click

[Jason]

I know this is probably a very basic question but I am fairly new to asp.net and very new to the Telerik controls.

I have a basic page with a text box and button and I want to fill a RadGrid with data from a sql query when the button is clicked using the data that is typed into the textbox as a variable for my query. When I put my query into the form load event and prepopulate the textbox the query works and the grid is populated. When I try the same code in the click event of the button I don't get any data in the grid. Do I need to call a refresh of the control or something? 

[Shinu]

Hi,

Try the following code snippet.
C#:

protected void btnbind_Click(object sender, EventArgs e)
   {
       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString());
       con.Open();
       SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM table1 where id="+Convert.ToInt32(textbox1.Text)+"", con);
       DataTable dt = new DataTable();
       adapter.Fill(dt);
       con.Close();
       Radgrid1.DataSource = dt;
       Radgrid1.DataBind();
   }

Thanks,
Shinu.

[Jason]

Hi thanks for the reply. My code is in VB but I think what I had tried is similar to your example. Here is my button click code. Am I missing something?

Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click

        Dim con As New SqlConnection 
        Dim SqlDataAdapter As New SqlDataAdapter()

        Dim selectquery As String = "select sys.netbios_name0 as Name, os.caption0 as OS, os.csdversion0 as Service_Pack, DATEADD(mi,(sys.pwdLastSet0 / 600000000) + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST('1/1/1601' AS DATETIME2)) as AD_Password_Last_Set,cs.Manufacturer0 as HW_Vendor, cs.Model0 as Model, sys.distinguishedname0 as OU " + _

                    "from resswcmsprip01.sccm_ec0.dbo.v_r_system sys " + _

                    "left join resswcmsprip01.sccm_ec0.dbo.v_gs_operating_system os on os.resourceid=sys.resourceid " + _

                    "left join resswcmsprip01.sccm_ec0.dbo.v_gs_computer_system cs on cs.resourceid=sys.resourceid " + _

                    "left join resswcmsprip01.sccm_ec0.dbo.v_gs_system_console_user cu on cu.resourceid=sys.resourceid " + _

                    "join (select resourceid, MAX(lastconsoleuse0) as [MaxConsoleUse] " + _

                          "from resswcmsprip01.sccm_ec0.dbo.v_gs_system_console_user cu2 " + _

                          "group by resourceid) as tblMax on cu.resourceid=tblmax.resourceid and cu.lastconsoleuse0=tblMax.MaxConsoleUse " + _

                    "where sys.netbios_name0 like '%" & txtInput.Text & "%'"

        Dim dtTable As DataTable

  

        Dim rootWebConfig As System.Configuration.Configuration

        rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/AppCompat")

        Dim connString As System.Configuration.ConnectionStringSettings

        If (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0) Then

            connString = rootWebConfig.ConnectionStrings.ConnectionStrings("csSCCM")

  

        End If

  

        Try

            con.ConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings("csSCCM").ToString

            dtTable = New DataTable()

            con.Open()

  

            SqlDataAdapter.SelectCommand = New SqlCommand(selectquery, con)

            SqlDataAdapter.Fill(dtTable)

  

            rdgMachines.DataSource = dtTable

            rdgMachines.DataBind()

  

  

        Catch ex As Exception

            'MessageBox.Show("Error while connecting to SQL Server. " & ex.Message)

        Finally

            con.Close() 'Close connection on error or not

        End Try

    End Sub

[Jason]

I am trying to work through this but I am not having any luck. I thought calling rebind() would be the solution but it doesn't make a difference. When I step through the code it calls NeedDataSource and the code in there looks like it is ok but nothing ever gets populated in the grid.

Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
    rdgMachines.Rebind()

End Sub

Protected Sub rdgMachines_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles rdgMachines.NeedDataSource
    Dim con As New SqlConnection
    ' Dim strCurrItem As String
    Dim SqlDataAdapter As New SqlDataAdapter()
    Dim selectquery As String = "select sys.netbios_name0 as Name, os.caption0 as OS, os.csdversion0 as Service_Pack, DATEADD(mi,(sys.pwdLastSet0 / 600000000) + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST('1/1/1601' AS DATETIME2)) as AD_Password_Last_Set,cs.Manufacturer0 as HW_Vendor, cs.Model0 as Model, sys.distinguishedname0 as OU " + _
                "from resswcmsprip01.sccm_ec0.dbo.v_r_system sys " + _
                "left join resswcmsprip01.sccm_ec0.dbo.v_gs_operating_system os on os.resourceid=sys.resourceid " + _
                "left join resswcmsprip01.sccm_ec0.dbo.v_gs_computer_system cs on cs.resourceid=sys.resourceid " + _
                "left join resswcmsprip01.sccm_ec0.dbo.v_gs_system_console_user cu on cu.resourceid=sys.resourceid " + _
                "join (select resourceid, MAX(lastconsoleuse0) as [MaxConsoleUse] " + _
                      "from resswcmsprip01.sccm_ec0.dbo.v_gs_system_console_user cu2 " + _
                      "group by resourceid) as tblMax on cu.resourceid=tblmax.resourceid and cu.lastconsoleuse0=tblMax.MaxConsoleUse " + _
                "where sys.netbios_name0 like '%" & txtInput.Text & "%'"
    Dim dtTable As DataTable

    Dim rootWebConfig As System.Configuration.Configuration
    rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/AppCompat")
    Dim connString As System.Configuration.ConnectionStringSettings
    If (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0) Then
        connString = rootWebConfig.ConnectionStrings.ConnectionStrings("csSCCM")

    End If

    Try
        con.ConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings("csSCCM").ToString
        dtTable = New DataTable()
        con.Open()

        SqlDataAdapter.SelectCommand = New SqlCommand(selectquery, con)
        SqlDataAdapter.Fill(dtTable)

        rdgMachines.DataSource = dtTable

        rdgMachines.DataBind()


    Catch ex As Exception
        'MessageBox.Show("Error while connecting to SQL Server. " & ex.Message)
    Finally
        con.Close() 'Close connection on error or not
    End Try
End Sub

[Jason]

I removed the radgrid control from my project and added a new one and everything works fine. Guess it was an anomaly.