none
SEARCH FORM HELP Please RRS feed

  • Question

  • Hello everyone, am a beginner in programming and am currently working on my own windows application .
    i have create main form an child forms using VB.Net 2015 .
    i have create a database using MySQL called "database".
    i want to create a search form to search inside database tables.
    in this form there is a combo-box to select the table and another combo-box to select the column and a text-box to write and recherche the designated word and a recherche button .
    i rely search a lot in the web but i didn't find what i'm looking for .
    please help ... thanks
    Tuesday, February 13, 2018 7:53 AM

Answers

  • No matter what the database is all data providers have a connection and command objects so in the example below which does SQL-Server for say MySql the connection is MySqlConnection and the command MySqlCommand.

    You would create a new instance of DataOperations, pass in the table name, column name and value. The call it e.g.

    Dim ops As New DataOperations

    Dim dt As New DataTable

    dt = ops.SearchEquals(cboDatabase.Text, cboColName.Text,txtValue.Text)

    I assume this would be done in the search form and sent back to the main form. So create the search form which should have a public property of type Datatable which after the search is done set the property to the data table returned from the SearchEquals function and show it via ShowDialog, have two buttons, ok and cancel where each has DialogResult set, upon closing the form check the DialogResult, if true use the DataTable

    As mentioned above you need to change the data provider.

    Imports System.Data.SqlClient
    Public Class DataOperations
        Private ConnectionString As String = "Your connection string goes here"
        ''' <summary>
        ''' Permits you to see if any method here error out.
        ''' </summary>
        ''' <returns></returns>
        Public Property HasError As Boolean
        ''' <summary>
        ''' If there was an exception we can get to it via this
        ''' property e.g. Exception.Message.
        ''' </summary>
        ''' <returns></returns>
        Public Property Exception As Exception
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="pTableName">database name</param>
        ''' <param name="pColumnName">column to search</param>
        ''' <param name="pValue">value to search</param>
        ''' <returns>DataTable</returns>
        ''' <remarks>
        ''' After calling this method be sure to check HasErrors, if true
        ''' the DataTable will be empty. If no errors and DataTable is empty
        ''' this means the search did not find any matches
        ''' 
        ''' Not really a good idea to use SELECT * but this fills your
        ''' requirements.
        ''' 
        ''' We can get much more complex with other options e.g. using
        ''' LIKE condition or IN condition which brings more challenges.
        ''' </remarks>
        Public Function SearchEquals(
            ByVal pTableName As String,
            ByVal pColumnName As String,
            ByVal pValue As Object) As DataTable
    
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = $"SELECT * FROM {pTableName} WHERE {pColumnName} = @Value"
                    cmd.Parameters.AddWithValue(pColumnName, pValue)
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                    Catch ex As Exception
                        HasError = True
                        Exception = ex
                    End Try
                End Using
            End Using
    
            Return dt
    
        End Function
    
    End Class
    
    
    Well that is a basic template.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 13, 2018 11:29 AM
    Moderator

All replies

  • Any reasons that you choose an Oracle database and then ask help in a Microsoft forum?

    Success Cor

    Tuesday, February 13, 2018 9:19 AM
  • No matter what the database is all data providers have a connection and command objects so in the example below which does SQL-Server for say MySql the connection is MySqlConnection and the command MySqlCommand.

    You would create a new instance of DataOperations, pass in the table name, column name and value. The call it e.g.

    Dim ops As New DataOperations

    Dim dt As New DataTable

    dt = ops.SearchEquals(cboDatabase.Text, cboColName.Text,txtValue.Text)

    I assume this would be done in the search form and sent back to the main form. So create the search form which should have a public property of type Datatable which after the search is done set the property to the data table returned from the SearchEquals function and show it via ShowDialog, have two buttons, ok and cancel where each has DialogResult set, upon closing the form check the DialogResult, if true use the DataTable

    As mentioned above you need to change the data provider.

    Imports System.Data.SqlClient
    Public Class DataOperations
        Private ConnectionString As String = "Your connection string goes here"
        ''' <summary>
        ''' Permits you to see if any method here error out.
        ''' </summary>
        ''' <returns></returns>
        Public Property HasError As Boolean
        ''' <summary>
        ''' If there was an exception we can get to it via this
        ''' property e.g. Exception.Message.
        ''' </summary>
        ''' <returns></returns>
        Public Property Exception As Exception
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="pTableName">database name</param>
        ''' <param name="pColumnName">column to search</param>
        ''' <param name="pValue">value to search</param>
        ''' <returns>DataTable</returns>
        ''' <remarks>
        ''' After calling this method be sure to check HasErrors, if true
        ''' the DataTable will be empty. If no errors and DataTable is empty
        ''' this means the search did not find any matches
        ''' 
        ''' Not really a good idea to use SELECT * but this fills your
        ''' requirements.
        ''' 
        ''' We can get much more complex with other options e.g. using
        ''' LIKE condition or IN condition which brings more challenges.
        ''' </remarks>
        Public Function SearchEquals(
            ByVal pTableName As String,
            ByVal pColumnName As String,
            ByVal pValue As Object) As DataTable
    
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = $"SELECT * FROM {pTableName} WHERE {pColumnName} = @Value"
                    cmd.Parameters.AddWithValue(pColumnName, pValue)
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                    Catch ex As Exception
                        HasError = True
                        Exception = ex
                    End Try
                End Using
            End Using
    
            Return dt
    
        End Function
    
    End Class
    
    
    Well that is a basic template.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 13, 2018 11:29 AM
    Moderator
  • Thank you very much for your help
    Tuesday, February 13, 2018 4:35 PM
  • Thank you very much for your help
    Your welcome :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 13, 2018 5:05 PM
    Moderator