CODEWITHSUNDEEP
c#sqldatabasevisual-studio
Britton
Britton

Reputation: 3

Trying to make a user input go through a LIKE SQL statement

Currently working on music guessing program. I have the user input lyrics and the program will try to find a match. If the program finds ones it will display the artist name. At the moment I am running trying to run a SELECT statement to find the most relevant match. When I hard code the element the console will give me the artists, but when I try to set it as the user input it displays nothing. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;


namespace Databasetesting
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Start\n");

            // MySQL Database Connection String
            string cs = @"server=192.168.0.5;userid=***;password=***;database= Music_Mixer;port=8889";

            // set up connection
            MySqlConnection con = null;

            // make a reader
            MySqlDataReader  reader = null;

            Console.WriteLine("Please enter song lyrics:");
            string uI = Console.ReadLine();

            // write a try catch statement   
            try
            {
                // cal in database
                con = new MySqlConnection(cs);

                // open connection
                con.Open();

                // Statement
                String cmdText = "SELECT Artist  FROM Songs WHERE Lyrics LIKE ('%@uI%')";

                // make a new command
                MySqlCommand cmd = new MySqlCommand(cmdText, con);

                // binding
                cmd.Parameters.AddWithValue("@uI", uI);

                // make reader = to new command
                reader = cmd.ExecuteReader();

                // run the reader and display to user
                while (reader.Read())
                {
                    string Artists = reader["Artist"].ToString();
                    Console.WriteLine(Artists);
                }
            }
            catch(MySqlException er)
            {
                Console.WriteLine(er);
            }
            finally
            {
                if(con != null)
                {
                    con.Close();
                }
                Console.ReadLine();
            }
        }
    }
}

Upvotes: 0

Views: 794

Answers (2)

dotnetspark
dotnetspark

Reputation: 581

Do this instead

string uI = "%" + Console.ReadLine() + "%";

you can also do string interpolation

string uI = $"%{Console.ReadLine()}%";

And your SQL statement

String cmdText = "SELECT Artist  FROM Songs WHERE Lyrics LIKE @uI";

Upvotes: 2

Dr Phil
Dr Phil

Reputation: 880

You can use string concatenation on the named parameter. In MySql you can try the concat operator || (depending on version, config ) or the concat function

isntead of ...LIKE ('%@uI%') go for ... LIKE concat('%',@uI,'%')

SELECT Artist  FROM Songs WHERE Lyrics LIKE concat('%',@uI,'%')

Upvotes: 1

Related Questions