Results 1 to 11 of 11

Thread: Writing SQL in the code behind to Gridview on a different page

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    41

    Writing SQL in the code behind to Gridview on a different page

    I am using Visual Web Developer Express with VB.net and have a website with 2 pages in them called "Tables.aspx" and "Variables.aspx". On the Tables page the user is required to enter a variable name in to a texbox, which I want then to take the user to the Variables page and have the gridview display the sql result. In behind the search button on the "Tables.aspx" page I have the following code.

    Code:
    ' Search button code
    
    Dim search As Char
    Search = CChar(txtSearch.Text)
    
    If Search = "" Then
    MsgBox("You cannot leave this field blank.")
    
    Dim connectionString As String
    Dim cnn As SqlConnection
    Dim cmd As SqlCommand
    Dim sql As String
    
    connectionString= "Data Source= MICKEY\MICKEYSQL;Initial Catalog= NILS_Data_Dictionary;Integrated Security= True"
    sql= "SELECT dbo.NILS_tables.Table_ID, dbo.NILS_variables.Variable_Name, dbo.NILS_variables.Variable_Description FROM dbo.NILS_variables JOIN dbo.NILS_tables ON dbo.NILS_tables.Table_ID = dbo.NILS_variables.Table_ID WHERE dbo.NILS_tables.Table_Name LIKE '%' OR dbo.NILS_tables.Table_Description LIKE '%' "
    
    Session("Search") = sql
    
    cnn = New SqlConnection(connectionString)
    Try
    cnn.Open()
    cmd = New SqlCommand(sql, cnn)
    
    Response.Redirect("Variables.aspx")
    Catch ex As Exception
    End Try
    End Sub
    On page load in the "Variables.aspx" page I have the following code:

    Code:
    'Variables.aspx code
    
    Dim connectionString As String
    Dim cnn As SqlConnection
    Dim cmd As SqlCommand
    Dim sql As String
    Dim reader As SqlDataReader
    
    connectionString= "Data Source= MICKEY\MICKEYSQL;Initial Catalog= NILS_Data_Dictionary;Integrated Security= True"
    
    sql = Session("Search")
    
    cnn = New SqlConnection(connectionstring)
    Try
    cnn.Open()
    cmd = New SqlCommand(sql, cnn)
    reader.Read()
    
    lblID.Text = reader.Item(0) ' Table ID
    lblVarName.Text = reader.Item(1) ' Variable Name
    lblDescrip.Text = reader.Item(2) ' Variable Description
    
    reader.Close()
    cmd.Dispose()
    cnn.Close()
    
    Catch ex As Exception
    End Try
    
    End Sub
    On the "Variables.aspx" page I had decided to use data readers which brought up only one of the columns however I realised using data readers would mean I would have to individually position each textbox which I am not sure how to do.

    Basically what I would like to know is how I do get the result of sql= "SELECT dbo.NILS_tables.Table_ID, dbo.NILS_variables.Variable_Name, dbo.NILS_variables.Variable_Description FROM dbo.NILS_variables JOIN dbo.NILS_tables ON dbo.NILS_tables.Table_ID = dbo.NILS_variables.Table_ID WHERE dbo.NILS_tables.Table_Name LIKE '%' OR dbo.NILS_tables.Table_Description LIKE '%' "
    on to a gridview on the "Variables.aspx" page? NB the code for the search button is stored in the code behind file on the "Tables.aspx" page.
    Last edited by gep13; Nov 8th, 2013 at 02:02 AM. Reason: Added code tags

  2. #2

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    41

    Re: Writing SQL in the code behind to Gridview on a different page

    Hey guys. I have managed to get the data in to the gridview on the "Variables.aspx" page however it is bringing up all of the data rather than basing the result on what the user enters via the textbox. I tried assigning the textbox to the 2 fields on the "Tables.aspx" page but not sure if I have did it right? Could someone tell me how I assign dbo.NILS_tables.Table_Name and dbo.NILS_tables.Table_Description" to the textbox properly in my SQL statement?

    Code:
    txtSearch.Text= "dbo.NILS_tables.Table_Name" + "dbo.NILS_tables.Table_Description" 
    
    'Variables.aspx updated code'
    
    Dim connectionString As String
    Dim cnn As SqlConnection
    Dim cmd As SqlCommand
    Dim sql As String
    Dim reader As SqlDataReader
    
    connectionString= "Data Source= MICKEY\MICKEYSQL;Initial Catalog= NILS_Data_Dictionary;Integrated Security= True"
    
    sql = Session("Search")
    
    cnn = New SqlConnection(connectionstring)
    Try
    cnn.Open()
    cmd = New SqlCommand(sql, cnn)
    reader.Read()
    
    Gridview1.DataSource = reader
    Gridview1.DataBind()
    
    
    reader.Close()
    cmd.Dispose()
    cnn.Close()
    
    Catch ex As Exception
    End Try
    
    End Sub
    Last edited by gep13; Nov 8th, 2013 at 02:02 AM. Reason: Added code tags

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    41

    Re: Writing SQL in the code behind to Gridview on a different page

    Anyone have any ideas?

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Writing SQL in the code behind to Gridview on a different page

    Moved from Visual Basic .NET
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    41

    Re: Writing SQL in the code behind to Gridview on a different page

    Anyone know anything about this? I want to make sure the search box is only taking values that are LIKE the fields dbo.NILS_tables.Table_Name , dbo.NILS_tables.Table_Description?

  6. #6
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Writing SQL in the code behind to Gridview on a different page

    Hello,

    Just a couple points on this thread....

    First up, don't use MsgBox in an ASP.Net application. Although it looks like it is working when you are debugging out of Visual Studio, it won't work when you deploy the site under IIS.

    It isn't a good idea to put your connection string in your code. This is something that is likely to change from time to time, and as such you want to be able to change it without having to recompile your code. You want to put this into your web.config file.

    If you are not actually doing the sql query until you go to the Variables.aspx page, why do you have all the Sql objects in the first page?

    In order to "easily" get all the results from the query onto the page, have you thought about displaying the results in a GridView? Each row of the GridView can be styled how you want it, and it will repeat for all the results.

    Have a look here:

    http://msdn.microsoft.com/en-us/library/aa581783.aspx

    For more information.

    Gary

    P.S. I updated your posts to include CODE tags, which makes them easier to read.

  7. #7

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    41

    Re: Writing SQL in the code behind to Gridview on a different page

    Thanks for your help I have managed to get this working, now for my next problem. On my "Tables.aspx" page is their a way I can make the column "Table_Name" on the gridview clickable or as a hyperlink so that when I click on a cell it provides me with the related information via a SQL query e.g. say I clicked on a cell that said AGESX the results would appear in the gridview on the "Variables.aspx" page? Would it involve me doing something in the source view of this gridview on the "Tables.aspx" page ?
    Last edited by 2337676; Nov 8th, 2013 at 04:52 AM.

  8. #8
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Writing SQL in the code behind to Gridview on a different page

    Hello,

    Is this:

    http://msdn.microsoft.com/en-us/library/aa581796.aspx

    What you are referring to?

    Gary

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    41

    Re: Writing SQL in the code behind to Gridview on a different page

    In my SqlDataSource that I have added from the toolbox I have SELECT [Variable_Name], [Variable_Descripton] FROM [NILS_variables] WHERE ([Variable_Name] LIKE '%' + @Variable_Name + '%' in my SELECT statement. Can anyone tell me how I can add an OR clause so [Variable_Description] is LIKE my textbox on the "Tables.aspx" page which is called txtSearch. In the "How would you like to retrieve data from your textbox?" I have the "Specify columns from a table or view" radio button selected and their doesn't seem to be an option for OR?

  10. #10

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    41

    Re: Writing SQL in the code behind to Gridview on a different page

    I have managed to resolve the issues I was having. Thanks for all your help.

  11. #11
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Writing SQL in the code behind to Gridview on a different page

    Hello,

    For the benefit of the community, would it be possible for you to share your findings, so that others can benefit?

    Also, can you mark your thread as resolved?

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width