-
Nov 4th, 2013, 04:09 AM
#1
Thread Starter
Member
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
-
Nov 4th, 2013, 05:04 AM
#2
Thread Starter
Member
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
-
Nov 4th, 2013, 08:57 AM
#3
Thread Starter
Member
Re: Writing SQL in the code behind to Gridview on a different page
-
Nov 5th, 2013, 02:00 AM
#4
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Nov 5th, 2013, 10:47 AM
#5
Thread Starter
Member
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?
-
Nov 8th, 2013, 02:08 AM
#6
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.
-
Nov 8th, 2013, 04:47 AM
#7
Thread Starter
Member
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.
-
Nov 8th, 2013, 06:37 AM
#8
Re: Writing SQL in the code behind to Gridview on a different page
-
Nov 11th, 2013, 04:24 AM
#9
Thread Starter
Member
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?
-
Nov 11th, 2013, 07:18 AM
#10
Thread Starter
Member
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.
-
Nov 15th, 2013, 02:29 AM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|