locked
how to access database using search function?

    Question

  • how could i build a search function button which will access my database? as the search item match the database , there must be a window pop up to show the searched data from the database.
    Sunday, January 04, 2009 2:52 PM

Answers

  • if you want to run your application without  SQL server, you could choose the Access as Substitutes,

    Here are four approaches (Take MS Access database file for example):

    1)  Via DataGridView: Update (Insert/Update/Delete) data back into MS Access database from DataGridView.

    Code sample: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2758290&SiteID=1

    Imports System.Data.OleDb

    Public Class Form1

        Dim myDA As OleDbDataAdapter

        Dim myDataSet As DataSet

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)

            con.Open()

            myDA = New OleDbDataAdapter(cmd)

            'Here one CommandBuilder object is required.

            'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  

            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)

            myDataSet = New DataSet()

            myDA.Fill(myDataSet, "MyTable")

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView

            con.Close()

            con = Nothing

        End Sub

     

        ' Save data back into database  

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Me.Validate()

            Me.myDA.Update(Me.myDataSet.Tables("MyTable"))

            Me.myDataSet.AcceptChanges()

        End Sub

    End Class

     

    2) Execute Select/Insert/Delete/Update T-SQL commands in code

       Code sample about MS Access database

       http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/

     

    3) Operate DataSet/DataTable in code

    Please check the 12th post and 13th post in this thread for detailed code sample:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2471782&SiteID=1

    Best wishes
    xingwei Hu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Xingwei Hu Monday, February 23, 2009 1:29 AM
    Monday, February 16, 2009 2:38 AM

All replies

  • Hi kentck86,

    If you want to search data using tablename, you may write code like this:

    I use two forms. Form1: textbox, button;Form2: datagridview

    Imports System.Data.SqlClient  
     
    Public Class Form1  
     
        Private Sub btnSearch_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles btnSearch.Click  
            Dim conn As SqlConnection  
            Dim da As SqlDataAdapter  
            Dim dt As DataTable  
     
            conn = New SqlConnection("Data Source=(local)\SQLEXPRESS;Initial Catalog=Student;Integrated Security=True;")  
     
            da = New SqlDataAdapter  
            dt = New DataTable  
     
            da.SelectCommand = New SqlCommand("SELECT * from " & TextBox1.Text, conn)  
     
            Try 
                conn.Open()  
                da.Fill(dt)  
                conn.Close()  
            Catch ex As Exception  
                MsgBox("No result")  
                conn.Close()  
            End Try 
     
            If dt.Rows.Count = 0 Then 
                Return 
            End If 
     
            Dim f As New Form2  
            f.Show()  
            f.DataGridView1.DataSource = dt  
     
        End Sub 
    End Class 

    Does this works for you? If you have any further questions or concerns, please update the thread and we will have a future discussion.

     

     

    Best Regards

    Yichun Feng




    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Xingwei Hu Friday, January 09, 2009 9:25 AM
    • Unmarked as answer by Xingwei Hu Friday, January 09, 2009 9:49 AM
    • Marked as answer by Martin Xie - MSFT Friday, January 09, 2009 1:29 PM
    • Unmarked as answer by kentck86 Monday, February 02, 2009 2:30 PM
    • Marked as answer by Xingwei Hu Sunday, February 08, 2009 4:00 PM
    • Unmarked as answer by kentck86 Saturday, February 14, 2009 2:11 PM
    Wednesday, January 07, 2009 8:45 AM
  • Hi kentck86,

    As Yichen said could finish what you want, however,we make a search query that is based on the filed of a table generally.
    for example:
    you could also modify the da.selectCommand.

     da.SelectCommand = New SqlCommand("select * from User_table  where User_name like '%" & TextBox1.Text & "%' ", conn)  
     

    Select [filed] from [tablename] like'%"[string]"%'

    When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned.

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

    The behavior of the Like operator depends on the Option Compare Statement. The default string comparison method for each source file is Option Compare Binary.


    Characters in pattern                Matches in string

    ?

                                  Any single character

    *      

                                  Zero or more characters

    #

                                   Any single digit (0–9)

    [charlist]

                                  Any single character in charlist

    [!charlist]

                                  Any single character not in charlist


    Best wishes

    xingwei Hu
     
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Martin Xie - MSFT Friday, January 09, 2009 1:29 PM
    • Unmarked as answer by kentck86 Monday, February 02, 2009 2:30 PM
    Friday, January 09, 2009 10:55 AM
  • i am not search data using tablename .
    my database is going to be so simple which consists of only  two column, all i want to do is to matched the inserted phrase to be search in the textbox with the column A item, if match found, then column A and column B item will be display together as the results . by the way, since i am using SQL server, may i know if my completed application able to run in a without sql server installed pc? 
    Monday, February 02, 2009 2:29 PM
  •  

    hi xingwei, i have been tried out the LIKE  operator and it does return me the result,
    but the way i am doing is different from what is suggested by yichen above,
    my database table consists of only two column, first column is terms , second column is synonym
    i am searching the data item in my table by using the query builder and filter LIKE @terms + '%'

    i built a GUI form which has the code :

     

    Public Class Form1 
     
        Private Sub DictionaryBindingNavigatorSaveItem_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles DictionaryBindingNavigatorSaveItem.Click 
            Me.Validate() 
            Me.DictionaryBindingSource.EndEdit() 
            Me.TableAdapterManager.UpdateAll(Me.PhraseSearchingDataSet) 
     
        End Sub 
     
        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load 
            'TODO: This line of code loads data into the 'PhraseSearchingDataSet.dictionary' table. You can move, or remove it, as needed. 
            'Me.DictionaryTableAdapter.Fill(Me.PhraseSearchingDataSet.dictionary) 
     
        End Sub 
     
        Private Sub FillBytermsToolStripButton_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles FillBytermsToolStripButton.Click 
            Try 
                Me.DictionaryTableAdapter.FillByterms(Me.PhraseSearchingDataSet.dictionary, TermsToolStripTextBox.Text) 
            Catch ex As System.Exception 
                System.Windows.Forms.MessageBox.Show(ex.Message) 
            End Try 
     
        End Sub 
    End Class 




    there are several improvement i wish to do:
    1) if no match found, an alert of NO result found displayed.
    2) as my data in first column are word, so i would like my filter match exactly the word
       and display result, display an alert for user if the word is not complete.

    example:

    search target: basis
    inserted word : bas (NOT complete, and should display an alert)

    3) null entry in search target, display alert.

    really wish that i could complete these functions. thank you for helping.

    Tuesday, February 03, 2009 4:09 PM
  • Hi kentck86,
    This is a sample for you, you could modify it by yourslft, please pay attention to update the dataTable including the primary key column, or it would failed.
    Imports System.Data.SqlClient  
     
    Public Class Form1  
     
        Private cnStr As String = "Data Source=tim-nb-pc\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True" 
        Private cmdstr As String = "select User_name,User_type from User_table" 
        Private cn As New SqlConnection(cnStr)  
        Private cmd As New SqlCommand(cmdstr, cn)  
        Private sda As New SqlDataAdapter(cmd)  
        Dim scb As New SqlCommandBuilder(sda)  
        Dim dt As New DataTable()  
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
            'Try  
     
            cn.Open()  
            dt.Clear()  
            sda.SelectCommand = New SqlCommand("select * from User_table  where User_name like '" & TextBox1.Text & "%' ", cn)  
            sda.Fill(dt)  
            Me.DataGridView1.DataSource = dt 
            cn.Close()  
            Dim count As Int32 = dt.Rows.Count  
            If count > 0 Then  
                MsgBox("Find the user")  
            Else  
                MsgBox("NO RESULT,Could not find any user")  
            End If  
     
     
            'Catch ex As Exception  
            '    MsgBox("error connection")  
            'End Try  
     
     
        End Sub  
     
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
            cn.Open()  
            sda.Fill(dt)  
            Me.DataGridView1.DataSource = dt 
            cn.Close()  
        End Sub  
     
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click  
            Try  
     
                cn.Open()  
     
                Dim sda As New SqlDataAdapter(cmd)  
                Dim scb As New SqlCommandBuilder(sda)  
                sda.Update(dt)  
                dt.AcceptChanges()  
                cn.Close()  
     
     
                Me.DataGridView1.Refresh()  
                dt.Dispose()  
                MsgBox("update succese")  
            Catch ex As Exception  
                MsgBox("update failed")  
            End Try  
             
        End Sub  
         
    End Class  
     


    Best wishes
    Xingwei Hu
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Xingwei Hu Sunday, February 08, 2009 3:59 PM
    • Unmarked as answer by kentck86 Friday, February 13, 2009 3:06 PM
    Wednesday, February 04, 2009 1:58 PM
  • hi xing wei,now my database is working already.
    now i am faing another problem, i couldnt run my database program in a pc that is not installed SQL server program.
    is there any other way that could make it still manage to run the database program in a pc without SQL program installed?
    thanks...
    Friday, February 13, 2009 3:06 PM
  • if you want to run your application without  SQL server, you could choose the Access as Substitutes,

    Here are four approaches (Take MS Access database file for example):

    1)  Via DataGridView: Update (Insert/Update/Delete) data back into MS Access database from DataGridView.

    Code sample: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2758290&SiteID=1

    Imports System.Data.OleDb

    Public Class Form1

        Dim myDA As OleDbDataAdapter

        Dim myDataSet As DataSet

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)

            con.Open()

            myDA = New OleDbDataAdapter(cmd)

            'Here one CommandBuilder object is required.

            'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  

            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)

            myDataSet = New DataSet()

            myDA.Fill(myDataSet, "MyTable")

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView

            con.Close()

            con = Nothing

        End Sub

     

        ' Save data back into database  

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Me.Validate()

            Me.myDA.Update(Me.myDataSet.Tables("MyTable"))

            Me.myDataSet.AcceptChanges()

        End Sub

    End Class

     

    2) Execute Select/Insert/Delete/Update T-SQL commands in code

       Code sample about MS Access database

       http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/

     

    3) Operate DataSet/DataTable in code

    Please check the 12th post and 13th post in this thread for detailed code sample:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2471782&SiteID=1

    Best wishes
    xingwei Hu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Xingwei Hu Monday, February 23, 2009 1:29 AM
    Monday, February 16, 2009 2:38 AM