Trying to search a SQL database via C# based on any search criteria entered, Getting Input string was not in a correct format

I am attempting to search my database with a stored procedure via my program. The goal is for it to search using any of the three criteria entered. ID#, Firstname, and/or Lastname. 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:

the 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;
        }
    }

The bridgeing query (to try edit or remove 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);
    }

And the call after hitting 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
    }

And in case needed, here are my properties used:

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

When searching for the ID it works great! but if i try and just search the firstname and or lastname i get the following error:

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

My thoughts are that the names are null causing it to have format issues on whatever is left blank so I tried to entered a space to add something but it did not work. Does it go back to how my stored procedure is set up or is it in the C# code? I tried at it for a while and am having trouble.

Thanks in advance!

>Solution :

There’s almost certainly a whole lot of irrelevant information in that question. This almost certainly has nothing to do with databases or queries or anything other than converting a string to a number. You didn’t even tell us where the exception was thrown but I’m guessing that it’s here:

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

If that TextBox doesn’t contain a valid representation of a 32-bit integer then that code will fail with that 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 might enter anything.

I would suggest that you first test whether the user has entered anything at all. If they haven’t then you can assume that they’re not trying to search by ID and proceed accordingly. How you do that is up to you but I would do it quite differently. That’s beyond the scope of this question though. If they have entered something then you should check whether it’s a valid number and stop if it’s not. Only if it is should you proceed. The TryParse method of any numerical type (and a few others besides) can do the validation and conversion in a single 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
}

Leave a Reply