When trying to search a SQL database via C# based on any entered search criteria, I get an error that the input string is not in the correct format

Question

I'm trying to search my database using a stored procedure through my program. The goal is to search using any of the three criteria entered. ID#, first name and/or last name. I created the following stored procedure in MySql:

CREATE DEFINER=`mainuser`@`localhost` PROCEDURE `searchvisitor`(
    enteredid int,
    enteredfn varchar(25),
    enteredln varchar(25)
)
begin
    select visitors.visitorid, visitors.firstname, visitors.lastname, visitors.middleinitial from visitors where visitors.visitorid = enteredid or visitors.firstname like '%enteredfn%' or visitors.lastname like '%enteredln%';
end

My C# code is as follows:

Database query:

public DataView searchUserQuery(int id, string fn, string ln)
    {
        using (MySqlConnection conn = new MySqlConnection(Helper.connVal("ntpantry")))
        {
            conn.Open();
            DataTable dt = new DataTable();
            string lgquery = "searchvisitor";
            MySqlCommand cmd = new MySqlCommand(lgquery, conn);
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("enteredid", id);
            cmd.Parameters.AddWithValue("enteredfn", fn);
            cmd.Parameters.AddWithValue("enteredln", ln);
            MySqlDataReader dr = cmd.ExecuteReader();
            dt.Load(dr);
            DataView dv = new DataView(dt);
            return dv;
        }
    }

Bridge query (try editing or removing extra logic in one place):

public DataView Searchvisitor()
    {
        if (GetVisitor.VisitorFirstName.Length == 0)
        {
            GetVisitor.VisitorFirstName = " ";
        }
        if (GetVisitor.VisitorLastName.Length == 0)
        {
            GetVisitor.VisitorLastName = " ";
        }
        return dq.searchUserQuery(Convert.ToInt32(GetVisitor.VisitorID), GetVisitor.VisitorFirstName, GetVisitor.VisitorLastName);
    }

Call after clicking the search button:

private void button2_Click(object sender, EventArgs e)
    {
        //Add get set to put logic in bridge data instead
        GetVisitor.VisitorID = Convert.ToInt32(textBox_searchid.Text);
        GetVisitor.VisitorFirstName = textBox_searchfn.Text;
        GetVisitor.VisitorLastName = textBox_searchln.Text;
        data_searchresults.DataSource = bd.Searchvisitor();
        
        
        DataGridViewColumn columnid = data_searchresults.Columns[0];
        columnid.Width = 40;
        DataGridViewColumn columnfn = data_searchresults.Columns[1];
        columnfn.Width = 60;
        DataGridViewColumn columnln = data_searchresults.Columns[2];
        columnln.Width = 60;

        //was currently working on fixing search results. works for id but not first name or last name. Also results box only shows 2 items
    }

If needed, here are the attributes I use:

public class GetVisitor
{
    public static int VisitorID { get; set; }
    public static string VisitorFirstName { get; set;}
    public static string VisitorLastName { get; set;}
}

The effect is very good when searching for ID! But if I try to search only first name and/or last name I get the following error:

System.FormatException: 'Input string was not in a correct format.'

My thought was that the name being empty was causing it to have formatting issues in the whitespace, so I tried entering a space to add some content, but it didn't work. Will it go back to how my stored procedure is set up or in C# code? I tried it for a while but ran into trouble.

Thanks in advance!

Answer 1

There is almost certainly a lot of irrelevant information in this question. This almost certainly has nothing to do with the database or the query or anything other than converting string to a number. You didn't even tell us where the exception was thrown, but I'm guessing it's here:

GetVisitor.VisitorID = Convert.ToInt32(textBox_searchid.Text);

If the TextBox does not contain a valid representation of a 32-bit integer, the code will fail with this error message. The obvious solution is to validate the user input before converting it to a number. It is a TextBox after all, so the user may enter anything.

I recommend you first test if the user has entered anything. If they don't, then you can assume they didn't try to search by ID and act accordingly. How you do this is up to you, but I would do it completely differently. But this is beyond the scope of this question. If they entered something then you should check if it is a valid number and stop if not. Only in this case should you continue. The TryParse method of any numeric type (and some others) can complete validation and conversion in one step, e.g.

if (textBox_searchid.TextLength == 0)
{
    // No ID entered
}
else if (int.TryParse(textBox_searchid.Text, out var id))
{
    // Use ID here
}
else
{
    //Invalid ID entered
}