Textbox, Gridview, Search Button with SQL OPENQUERY

N

Nelly326

New member
Joined
Mar 12, 2013
Messages
2
Programming Experience
Beginner
Hi all,

I'm new to this forum and a beginner with vb.net. My question is that I am attempting to have a user enter a value into a textbox, hit search button, and then the entered record will show in a gridview. My code is not working however (I am thinking it may have to do with the OpenQuery but I am not sure). Any help is greatly appreciated!

VB.NET: 
[COLOR=#0000ff][COLOR=#0000ff][COLOR=#0000ff][FONT=Consolas][SIZE=2]Imports[/SIZE][/FONT][/COLOR][/COLOR][/COLOR][FONT=Consolas][SIZE=2][COLOR=#000000] System.Data[/COLOR][/SIZE][/FONT]
[COLOR=#0000ff][COLOR=#0000ff][COLOR=#0000ff][FONT=Consolas][SIZE=2]Imports[/SIZE][/FONT][/COLOR][/COLOR][/COLOR][FONT=Consolas][SIZE=2][COLOR=#000000] System.Data.OleDb[/COLOR][/SIZE][/FONT]
[COLOR=#0000ff][COLOR=#0000ff][COLOR=#0000ff][FONT=Consolas][SIZE=2]Imports[/SIZE][/FONT][/COLOR][/COLOR][/COLOR][FONT=Consolas][SIZE=2][COLOR=#000000] System.Data.SqlClient[/COLOR][/SIZE][/FONT]
[COLOR=#0000ff][COLOR=#0000ff][COLOR=#0000ff][FONT=Consolas][SIZE=2]Partial[/SIZE][/FONT][/COLOR][/COLOR][/COLOR][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Class[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]_Default
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Inherits[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] System.Web.UI.[/FONT][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]Page
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Protected[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Sub[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] Page_Load(sender [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Object[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas], e [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] System.[/FONT][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]EventArgs[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas]) [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Handles[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Me[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2].Load[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]End[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Sub
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2]　[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Protected[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Sub[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] BindGrid(searchText [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]String[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2])[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Dim[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] connection [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]New[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]OleDbConnection[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas]([/FONT][/FONT][FONT=Consolas][COLOR=#a31515][FONT=Consolas][COLOR=#a31515][FONT=Consolas][COLOR=#a31515]"My connection is here"[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2])[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Dim[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] cmd [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]New[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]OleDbCommand
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Dim[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] sql [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]String[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] = [/FONT][/FONT][FONT=Consolas][COLOR=#a31515][FONT=Consolas][COLOR=#a31515][FONT=Consolas][COLOR=#a31515]"SELECT * FROM OPENQUERY(123,' Select field1, PERIOD_NAME FROM RS123.MM WHERE PERIOD_NAME = @PERIOD_NAME ')"
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2]cmd.CommandText = sql[/SIZE]
[SIZE=2]cmd.CommandType = [/SIZE][/FONT][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][SIZE=2][COLOR=#2b91af]CommandType[/COLOR][/SIZE][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas][SIZE=2].Text[/SIZE]
[SIZE=2]cmd.Parameters.AddWithValue([/SIZE][/FONT][/FONT][FONT=Consolas][COLOR=#a31515][FONT=Consolas][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]"@PERIOD_NAME"[/COLOR][/SIZE][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas][SIZE=2], searchText)[/SIZE]
[SIZE=2]　[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Dim[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] dt [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]New[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]DataTable[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2]()[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Dim[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] ad [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]New[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]OleDbDataAdapter[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2](cmd)[/SIZE]
[SIZE=2]ad.Fill(dt)[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]If[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] dt.Rows.Count > 0 [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Then
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][COLOR=#008000][FONT=Consolas][COLOR=#008000][FONT=Consolas][COLOR=#008000][SIZE=2]'check if the query returns any data [/SIZE]
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas][SIZE=2]GridView1.DataSource = dt[/SIZE]
[SIZE=2]GridView1.DataBind()[/SIZE]
[/FONT][/FONT][FONT=Consolas][COLOR=#008000][FONT=Consolas][COLOR=#008000][FONT=Consolas][COLOR=#008000][SIZE=2]'No records found [/SIZE]
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][SIZE=2]Else[/SIZE]
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]End[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]If
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2]　[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]End[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Sub
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Protected[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Sub[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas] Button1_Click(sender [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Object[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][FONT=Consolas], e [/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]As[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af][FONT=Consolas][COLOR=#2b91af]EventArgs[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2])[/SIZE]
[SIZE=2]BindGrid(TextBox1.Text.Trim())[/SIZE]
[/FONT][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]End[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Sub
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE][FONT=Consolas][FONT=Consolas][SIZE=2]　[/SIZE]
[SIZE=2]　[/SIZE]
[SIZE=2]　[/SIZE]
[SIZE=2]　[/SIZE]
[/FONT][/FONT][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][SIZE=2]End[/SIZE][/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][SIZE=2][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff][FONT=Consolas][COLOR=#0000ff]Class
[/COLOR][/FONT][/COLOR][/FONT][/COLOR][/FONT][/SIZE]
 
Sort by date Sort by votes
Are you simply trying to query a database table and filter the results? OPENQUERY is about executing a query on a different server. If you just want a simple query then get rid of the outer query and just use the inner one.

By the way, you should use SqlClient rather then OleDb to connect to SQL Server unless you have a good reason to do otherwise.
 
Upvote 0 Downvote
My question isn't about the openquery I am wondering how to make my page work (ex: user enters a value into textbox, hits button, and gridview displays correct value entered and corresponding data from the sql I provided). At this time, page will load and I can click the button but the gridview is not displayed. Here is my code, any help is greatly greatly appreciated!

VB.NET: 
Imports System.Data 
Imports System.Data.OleDb 
Imports System.Data.SqlClient 
 
 
 
Partial Class _Default 
    Inherits System.Web.UI.Page 
 
    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load 
 
 
    End Sub 
 
 
    Protected Sub BindGrid(searchText As String) 
        Dim connection As New OleDbConnection("con string") 
        Dim cmd As New OleDbCommand 
        Dim sql As String = "SELECT * FROM HR.dbo.GDWFeed WHERE PERIOD_NAME = @PERIOD_NAME" 
        cmd.Connection = connection 
        cmd.CommandText = sql 
        cmd.CommandType = CommandType.Text 
        cmd.Parameters.AddWithValue("@PERIOD_NAME", searchText) 
 
 
 
        Dim dt As New DataTable() 
        Dim ad As New OleDbDataAdapter(cmd) 
        connection.Open() 
        ad.Fill(dt) 
        connection.Close() 
        If dt.Rows.Count > 0 Then 
 
            GridView1.DataSource = dt 
            GridView1.DataBind() 
 
        Else 
        End If 
 
 
    End Sub 
 
    Protected Sub Button1_Click(sender As Object, e As EventArgs) 
        BindGrid(TextBox1.Text.Trim()) 
    End Sub 
 
 
 
 
 
End Class
 
Upvote 0 Downvote

Similar threads

R
  • Question
How to print Specific Invoice with Crystal Report ?
Replies
1
Views
4K
benshaws
B
J
  • Question
Perform search on Datagridview using SQL queries
Replies
0
Views
1K
joshface98
J
L
  • Question
Question Need to update the SQL Server with the values FROM txtBOX
Replies
2
Views
3K
lmalave
L
L
  • Question
SQl Server doesnt update new values from Textbox Bind to Datatable
Replies
2
Views
3K
lmalave
L
M
  • Question
Question Displaying SQL search results in Textbox
Replies
1
Views
2K
IanRyder
I

Share this page

Latest posts

Back
Top