Textbox, Gridview, Search Button with SQL OPENQUERY

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]
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
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.
 

Nelly326

New member
Joined
Mar 12, 2013
Messages
2
Programming Experience
Beginner
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
 
Top Bottom