none
How do you perform a search command using Visual Basic 2008? RRS feed

Answers

  •  ACCOUNTINGONLINE.US wrote:

    Error

    MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@strString varchar(30)

    AS
    SELECT City, State, Manager
    FROM store
    WHERE (Ci' at line 2


     

    Hi,

     

    That error occurs as you are trying to use SQL Server code in MySQL. Also in one of your code snippets I observed you using System.Data.SqlClient. That's the provider for SQL Server and not for MySql.

     

    Check this and this small tutorial to learn about the provider for MySql.

     

    HTH,

    Suprotim Agarwal

    Saturday, June 21, 2008 7:18 AM
  •  ACCOUNTINGONLINE.US wrote:

    This is the code that I am using: Question, How do I send the search info from the textboxt to the Mysql command?

     

    You have to add parameters to the Command object. Your statement will change as:

     

    "select * from store where city=?City"

     

    command.Parameters.AddWithValue("?City", textBox1.Text);

    Note: Please confirm and try out the syntax as I have not worked with MySQL.

     

    HTH,

    Suprotim Agarwal

    Monday, June 23, 2008 8:43 AM

All replies

  • Hi,

     

    Please elaborate your requirement. What do you want to perform a search on/?

     

    HTH,

    Suprotim Agarwal

     

    Monday, June 16, 2008 3:14 AM





  • I Would like to search by name, State City and address using a form, this is what I have so far to search by Id.




    SEARCH CODE / DATABASE



    Private Sub FillByCustomerIDToolStripButton_Click( _

    ByVal sender As System.Object, ByVal e As System.EventArgs) _

    Handles FillByCustomerIDToolStripButton.Click

    Try

    Dim customerID As Integer = Convert.ToInt32(CustomerIDToolStripTextBox.Text)

    Me.CustomersTableAdapter.FillByCustomerID( _

    Me.MMABooksDataSet.Customers, customerID)

    If CustomersBindingSource.Count = 0 Then

    MessageBox.Show("No customer found with this ID. " _

    & "Please try again.", "Customer Not Found")

    End If

    Catch ex As FormatException

    MessageBox.Show("Customer ID must be an integer.", _

    "Entry Error")

    Catch ex As SqlException

    MessageBox.Show("SQL Server error # " & ex.Number _

    & ": " & ex.Message, ex.GetType.ToString)

    End Try

    End Sub


    ANOTHER CODE SAMPLE # 2

    Public Class Form1


    Private Sub FillByCustomerIDToolStripButton_Click( _

    ByVal sender As System.Object, ByVal e As System.EventArgs) _

    Handles FillByCustomerIDToolStripButton.Click

    Try

    Dim customerID As Integer = CInt(CustomerIDToolStripTextBox.Text)

    Me.CustomersTableAdapter.FillByCustomerID( _

    Me.MMABooksDataSet.Customers, customerID)

    Me.InvoicesTableAdapter.FillByCustomerID( _

    Me.MMABooksDataSet.Invoices, customerID)

    Catch ex As InvalidCastException

    MessageBox.Show("Customer ID must be an integer.", "Entry Error")

    Catch ex As SqlException

    MessageBox.Show("SQL Server error # " & ex.Number _

    & ": " & ex.Message, ex.GetType.ToString)

    End Try

    End Sub


    End Class

    ANOTHER SAMPLE


    ' TO GET ALL THE CUSTOMERS


    Private Sub FillToolStripButton_Click(ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles FillToolStripButton.Click

    Try

    Me.CustomersTableAdapter.Fill(Me.MMABooksDataSet.Customers)

    Catch ex As SqlException

    MessageBox.Show("SQL Server error # " & ex.Number _

    & ": " & ex.Message, ex.GetType.ToString)

    End Try

    End Sub


    End Class



    Tuesday, June 17, 2008 2:21 AM
  • This is the code that I have; but it does not work:

    I have a text box named ---> tbBuscar

    There are four fields: storeId, City,State, Manager

    I would like to enter any of those fields in the tbBuscar to be able to find the record in the database



    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
            ' Search /Buscar

            Try
                Dim storeID As Integer = Convert.ToInt32(tbBuscar.Text)
                'I went to the field store id and right clic/new query and then gave name fillByStore
                Me.StoreTableAdapter.FillByStore(Me.MystoreDataSet.store)
                If StoreBindingSource.Count = 0 Then
                    MessageBox.Show("No customer found with this ID. " _
                        & "Please try again.", "Customer Not Found")
                End If
            Catch ex As FormatException
                MessageBox.Show("Customer ID must be an integer.", _
                    "Entry Error")
            Catch ex As SqlException
                MessageBox.Show("SQL Server error # " & ex.Number _
                    & ": " & ex.Message, ex.GetType.ToString)
            End Try

    Wednesday, June 18, 2008 1:30 PM
  • Hi,

     

    Create a stored procedure in your SQL database in the following manner:

     

    Code Snippet

    CREATE PROCEDURE spSearchByBuscar

    @strString varchar(30)

    AS

    SELECT City, State, Manager
    FROM [yourtablename]
    WHERE (City LIKE '%' + @strString + '%' OR State LIKE '%' + @strString + '%' OR Manager LIKE '%' + @strString + '%')
    RETURN

     

     

     

    In your code:

     

    Code Snippet

    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
            ' Search /Buscar

            Try

    Dim cmd As SqlCommand = New SqlCommand("spSearchByBuscar", New SqlConnection("YourConnectionString"))

    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@strString", tbBuscar.Text)

    cmd.Connection.Open()

    Dim dr As SqlDataReader = cmd.ExecuteReader()

    ' Now loop through the reader and display the records

    Catch ex As SqlException
                MessageBox.Show("SQL Server error # " & ex.Number _
                    & ": " & ex.Message, ex.GetType.ToString)

    End Try

    ' Write finally and close the connection

     

    End Sub

     

     

    HTH,

    Suprotim Agarwal

    Wednesday, June 18, 2008 3:23 PM
  • Thank you for your help!!

     

    I am Using MySql with VB 2008

     

     

    http://dev.mysql.com/doc/refman/5.0/en/connector-net-using-stored.html

     

     

    25.2.5.3.1. Introduction
    25.2.5.3.2. Creating Stored Procedures from Connector/NET
    25.2.5.3.3. Calling a Stored Procedure from Connector/NET
    25.2.5.3.1. Introduction

    With the release of MySQL version 5 the MySQL server now supports stored procedures with the SQL 2003 stored procedure syntax.

    A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.

    Stored procedures can be particularly useful in situations such as the following:

    • When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.

    • When security is paramount. Banks, for example, use stored procedures for all common operations. This provides a consistent and secure environment, and procedures can ensure that each operation is properly logged. In such a setup, applications and users would not get any access to the database tables directly, but can only execute specific stored procedures.

    Connector/NET supports the calling of stored procedures through the MySqlCommand object. Data can be passed in and our of a MySQL stored procedure through use of the MySqlCommand.Parameters collection.

    Note

    When you call a stored procedure, the command object makes an additional SELECT call to determine the parameters of the stored procedure. You must ensure that the user calling the procedure has the SELECT privilege on the mysql.proc table to enable them to verify the parameters. Failure to do this will result in an error when calling the procedure.

    This section will not provide in-depth information on creating Stored Procedures. For such information, please refer to http://dev.mysql.com/doc/mysql/en/stored-procedures.html.

    A sample application demonstrating how to use stored procedures with Connector/NET can be found in the Samples directory of your Connector/NET installation.

    25.2.5.3.2. Creating Stored Procedures from Connector/NET

    Stored procedures in MySQL can be created using a variety of tools. First, stored procedures can be created using the mysql command-line client. Second, stored procedures can be created using the MySQL Query Browser GUI client. Finally, stored procedures can be created using the .ExecuteNonQuery method of the MySqlCommand object:

    Visual Basic Example

    Dim conn As New MySqlConnection
    Dim cmd As New MySqlCommand
    
    conn.ConnectionString = "server=127.0.0.1;" _
        & "uid=root;" _
        & "pwd=12345;" _
        & "database=test"
    
    Try
        conn.Open()
        cmd.Connection = conn
    
        cmd.CommandText = "CREATE PROCEDURE add_emp(" _
            & "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " _
            & "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " _
            & "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END"
     
        cmd.ExecuteNonQuery()
    Catch ex As MySqlException
        MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try



    25.2.5.3.3. Calling a Stored Procedure from Connector/NET

    To call a stored procedure using Connector/NET, create a MySqlCommand object and pass the stored procedure name as the .CommandText property. Set the .CommandType property to CommandType.StoredProcedure.

    After the stored procedure is named, create one MySqlCommand parameter for every parameter in the stored procedure. IN parameters are defined with the parameter name and the object containing the value, OUT parameters are defined with the parameter name and the datatype that is expected to be returned. All parameters need the parameter direction defined.

    After defining parameters, call the stored procedure by using the MySqlCommand.ExecuteNonQuery() method:

    Visual Basic Example

    Dim conn As New MySqlConnection
    Dim cmd As New MySqlCommand
    
    conn.ConnectionString = "server=127.0.0.1;" _
        & "uid=root;" _
        & "pwd=12345;" _
        & "database=test"
    
    Try
        conn.Open()
        cmd.Connection = conn
    
        cmd.CommandText = "add_emp"
        cmd.CommandType = CommandType.StoredProcedure
    
        cmd.Parameters.Add("?lname", 'Jones')
        cmd.Parameters["?lname"].Direction = ParameterDirection.Input
    
        cmd.Parameters.Add("?fname", 'Tom')
        cmd.Parameters["?fname"].Direction = ParameterDirection.Input
    
        cmd.Parameters.Add("?bday", #12/13/1977 2:17:36 PM#)
        cmd.Parameters["?bday"].Direction = ParameterDirection.Input
    
        cmd.Parameters.Add("?empno", MySqlDbType.Int32)
        cmd.Parameters["?empno"].Direction = ParameterDirection.Output
    
        cmd.ExecuteNonQuery()
    
        MessageBox.Show(cmd.Parameters["?empno"].Value)
    Catch ex As MySqlException
        MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try



    Wednesday, June 18, 2008 4:42 PM

  • Hi:

    Is there a way to do it with a query instead; I am getting some errors trying to setup the stored procedure.

    Thanks,
    Friday, June 20, 2008 1:42 PM

  • I tried to create the Stored Procedure this way:



    CREATE PROCEDURE spSearchByBuscar

    @strString varchar(30)


    AS

    SELECT City, State, Manager

    FROM store

    WHERE (City LIKE '%' + @strString + '%' OR State LIKE '%' + @strString + '%' OR Manager LIKE '%' + @strString + '%')

    RETURN





    This is the Error that I get:



    Error

    SQL query:

    CREATE PROCEDURE spSearchByBuscar@strString varchar( 30 ) AS SELECT City, State, Manager
    FROM store
    WHERE (

    City LIKE '%' + @strString + '%'
    OR State LIKE '%' + @strString + '%'
    OR Manager LIKE '%' + @strString + '%'

    )
    RETURN

    MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@strString varchar(30)

    AS
    SELECT City, State, Manager
    FROM store
    WHERE (Ci' at line 2





    Friday, June 20, 2008 1:48 PM

  • In the form have a text box and a button to find or search info/record


    Private Sub FillByCustomerIDToolStripButton_Click( _
                ByVal sender As System.Object, ByVal e As System.EventArgs) _
                Handles FillByCustomerIDToolStripButton.Click
            Try
                Dim customerID As Integer = Convert.ToInt32(CustomerIDToolStripTextBox.Text)
                Me.CustomersTableAdapter.FillByCustomerID( _
                    Me.MMABooksDataSet.Customers, customerID)
                If CustomersBindingSource.Count = 0 Then
                    MessageBox.Show("No customer found with this ID. " _
                        & "Please try again.", "Customer Not Found")
                End If
            Catch ex As FormatException
                MessageBox.Show("Customer ID must be an integer.", _
                    "Entry Error")
            Catch ex As SqlException
                MessageBox.Show("SQL Server error # " & ex.Number _
                    & ": " & ex.Message, ex.GetType.ToString)
            End Try
        End Sub
    Saturday, June 21, 2008 4:48 AM

  • Imports System.Data.SqlClient

    Public Class Form1

        Private Sub FillByCustomerIDToolStripButton_Click( _
                ByVal sender As System.Object, ByVal e As System.EventArgs) _
                Handles FillByCustomerIDToolStripButton.Click
            Try
                Dim customerID As Integer = CInt(CustomerIDToolStripTextBox.Text)
                Me.CustomersTableAdapter.FillByCustomerID( _
                    Me.MMABooksDataSet.Customers, customerID)
                Me.InvoicesTableAdapter.FillByCustomerID( _
                    Me.MMABooksDataSet.Invoices, customerID)
            Catch ex As InvalidCastException
                MessageBox.Show("Customer ID must be an integer.", "Entry Error")
            Catch ex As SqlException
                MessageBox.Show("SQL Server error # " & ex.Number _
                    & ": " & ex.Message, ex.GetType.ToString)
            End Try
        End Sub

    End Class
    Saturday, June 21, 2008 4:53 AM
  •  ACCOUNTINGONLINE.US wrote:

    Error

    MySQL said:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@strString varchar(30)

    AS
    SELECT City, State, Manager
    FROM store
    WHERE (Ci' at line 2


     

    Hi,

     

    That error occurs as you are trying to use SQL Server code in MySQL. Also in one of your code snippets I observed you using System.Data.SqlClient. That's the provider for SQL Server and not for MySql.

     

    Check this and this small tutorial to learn about the provider for MySql.

     

    HTH,

    Suprotim Agarwal

    Saturday, June 21, 2008 7:18 AM

  • I am looking for VB .net

    These examples apply to C#

    Thanks,


    Saturday, June 21, 2008 7:23 AM
  • Hi,

     

    Check this tool: Convert C# to VB.NET

     

    HTH,

    Suprotim Agarwal

     

    Saturday, June 21, 2008 8:01 AM

  • Great Help thank you so much this is working very well so far!!!

    This is the code that I am using: Question, How do I send the search info from the textboxt to the Mysql command?


    'Lista de Stores

        Private Sub Button26_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button26.Click
            'Usa esta linea para buscar/search MySql Database ....incluye .....arriba 'Imports MySql.Data.MySqlClient 

            Dim MyConString As String = "SERVER=localhost;" + "DATABASE=mystore;" + "UID=root;" + "PASSWORD=;"

            'Declarando la variable y asignando el textbox a esta variable
    ' Here I am declaring the variable to read the text box / How do I send the user input to the query?

            Dim tbBuscar As String = TextBox1.Text

            Dim connection As New MySqlConnection(MyConString)
            Dim command As MySqlCommand = connection.CreateCommand()
            Dim Reader As MySqlDataReader
            'Seleccionar todos los empleados
            command.CommandText = "select * from store where city='Salem'"
            connection.Open()
            Reader = command.ExecuteReader()
            While Reader.Read()
                Dim thisrow As String = ""
                For i As Integer = 0 To Reader.FieldCount - 1
                    thisrow += Reader.GetValue(i).ToString() + ","
                Next
                ListBox2.Items.Add(thisrow)
            End While
            connection.Close()
        End Sub

    Monday, June 23, 2008 2:53 AM

  • How Do I Find Record Using MySql and Visual Basic 2008 / Search Record Using Visual Basic and Mysql Similar to this?

    Visual Basic
    ' Takes a DbConnection and creates a DbCommand to retrieve data
    ' from the Categories table by executing a DbDataReader.
    Private Shared Sub DbCommandSelect(ByVal connection As DbConnection)

    Dim queryString As String = _
    "SELECT CategoryID, CategoryName FROM Categories"

    ' Check for valid DbConnection.
    If Not connection Is Nothing Then
    Using connection
    Try
    ' Create the command.
    Dim command As DbCommand = connection.CreateCommand()
    command.CommandText = queryString
    command.CommandType = CommandType.Text

    ' Open the connection.
    connection.Open()

    ' Retrieve the data.
    Dim reader As DbDataReader = command.ExecuteReader()
    Do While reader.Read()
    Console.WriteLine("{0}. {1}", reader(0), reader(1))
    Loop

    Catch ex As Exception
    Console.WriteLine("Exception.Message: {0}", ex.Message)
    End Try
    End Using
    Else
    Console.WriteLine("Failed: DbConnection is Nothing.")
    End If
    End Sub

    Monday, June 23, 2008 3:39 AM
  •  ACCOUNTINGONLINE.US wrote:

    This is the code that I am using: Question, How do I send the search info from the textboxt to the Mysql command?

     

    You have to add parameters to the Command object. Your statement will change as:

     

    "select * from store where city=?City"

     

    command.Parameters.AddWithValue("?City", textBox1.Text);

    Note: Please confirm and try out the syntax as I have not worked with MySQL.

     

    HTH,

    Suprotim Agarwal

    Monday, June 23, 2008 8:43 AM

  • Thank you so much this worked great!!!

    Private Sub Button30_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button30.Click
            'Search Field
            Dim MyConString As String = "SERVER=localhost;" + "DATABASE=mystore;" + "UID=root;" + "PASSWORD=;"

            'Declarando la variable y asignando el textbox a esta variable
            Dim tbBuscar As String = TextBox1.Text
            Dim micomando As String
            Dim connection As New MySqlConnection(MyConString)
            Dim command As MySqlCommand = connection.CreateCommand()
            Dim Reader As MySqlDataReader
            'Seleccionar todos los empleados
            'command.CommandText = "select manager,city,state from store"
            'micomando = "SELECT * FROM employee WHERE  '%tbBuscar%' LIMIT 0, 50"
            micomando = "select * from store where city=?City"

            ' Aqui estoy usando parametro para buscar en el texto/puedes usar variable tbBuscar
            command.Parameters.AddWithValue("?City", TextBox1.Text)
            command.CommandText = micomando
            connection.Open()
            Reader = command.ExecuteReader()
            While Reader.Read()
                Dim thisrow As String = ""
                For i As Integer = 0 To Reader.FieldCount - 1
                    thisrow += Reader.GetValue(i).ToString() + ","
                Next
                ListBox2.Items.Add(thisrow)
            End While
            connection.Close()

            'Esto es un ejemplo de Union de dos tablas


        End Sub
    Tuesday, June 24, 2008 1:18 PM

  • This search works great and the result is sent to a list Box; How do I find the Record ( a Single Record) to be found by the ToolStripButton2. Using this same code that works.

    ToolStripButton2

    Private Sub Button30_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button30.Click
            'Search Field/ comment only
            Dim MyConString As String = "SERVER=localhost;" + "DATABASE=mystore;" + "UID=root;" + "PASSWORD=;"

            'Declarando la variable y asignando el textbox a esta variable / comment only
            Dim tbBuscar As String = TextBox1.Text
            Dim micomando As String
            Dim connection As New MySqlConnection(MyConString)
            Dim command As MySqlCommand = connection.CreateCommand()
            Dim Reader As MySqlDataReader
            'Seleccionar todos los empleados/ comment only
            'command.CommandText = "select manager,city,state from store"/ comment only
            'micomando = "SELECT * FROM employee WHERE  '%tbBuscar%' LIMIT 0, 50"/ comment only
            micomando = "select * from store where city=?City"

            ' Aqui estoy usando parametro para buscar en el texto/puedes usar variable tbBuscar/ comment only
            command.Parameters.AddWithValue("?City", TextBox1.Text)
            command.CommandText = micomando
            connection.Open()
            Reader = command.ExecuteReader()
            While Reader.Read()
                Dim thisrow As String = ""
                For i As Integer = 0 To Reader.FieldCount - 1
                    thisrow += Reader.GetValue(i).ToString() + ","
                Next
                ListBox2.Items.Add(thisrow)
            End While
            connection.Close()

            'Esto es un ejemplo de Union de dos tablas/ comment only
    Tuesday, June 24, 2008 1:25 PM