Input string was not in a correct format. on SQL Select like Statement?

Question

I Created this Select Stored Procedure:

@SerialNumber varchar(50)
@PalletNumber varchar(50)  ------> Datatype int 

SELECT  
  ,SerialNumber
  , PalletNumber
 FROM  dbo.FG_FILLIN 
 WHERE (SerialNumber LIKE @SerialNumber + '%' )
 AND (PalletNumber =  @PalletNumber)

I change @palletnumber to varchar because Like '%' doesnt accept int.

---

My Data Access :

public DataSet FGSearchAll(FillinEntity fin) {

        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand cmd = new SqlCommand("FGSearchAll", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        DataSet dSet = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);

       cmd.Parameters.Add("@SerialNumber", SqlDbType.VarChar, 50).Value = fin.SerialNumber;
           cmd.Parameters.Add("@PalletNumber", SqlDbType.Int).Value = fin.PalletNumber;

        try
        {
            conn.Open();

            da.Fill(dSet, "FGDATA");

            conn.Close();
        }
        catch (SqlException)
        {
            throw;
        }
        return dSet;
    }

UI:

private void SearchAll()
    {

        BAL obj = new BAL();
        FillinEntity fin = new FillinEntity();
         fin.SerialNumber = txtSearchSerial.Text ;
         **fin.PalletNumber = Convert.ToInt32(txtSearchPallet.Text);**

        try
        {
            dsdata = obj.FGSearchAll(fin);

            if (dsdata.Tables[0].Rows.Count < 1)
            {
                MessageBox.Show("No Record Found!");

            }
            else
            {
                dgWIP.DataSource = dsdata;
                dgWIP.DataMember = "FGDATA";
            }
        }
        catch (ApplicationException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

All Things works fine if i have input Both but when i just search for Serial number and pallet has an empty value it shows an error.

Any idea?.

Thanks in regards.

Answer 1

You will have to add logic in to your Stored Procedure to check that the parameter values don't contain NULL values

You will have to do something like this:

if (@PalletNumber is Null)
begin
SELECT  
  ,SerialNumber
  , PalletNumber
 FROM  dbo.FG_FILLIN 
 WHERE (SerialNumber LIKE @SerialNumber + '%' )
end
else
begin
SELECT  
  ,SerialNumber
  , PalletNumber
 FROM  dbo.FG_FILLIN 
 WHERE (SerialNumber LIKE @SerialNumber + '%' )
 AND (PalletNumber LIKE  @PalletNumber + '%')
end

Answer 2

You need dynamic sql here or use IsNull

@PalletNumber varchar(50) = NULL -- default null

SELECT  *
 FROM  @FG_FILLIN
 WHERE (SerialNumber LIKE  @SerialNumber + '%' )
 AND (PalletNumber =  @PalletNumber OR @PalletNumber IS NULL)

Your select also has syntax error; i.e, ',' after SELECT

Answer 3

I don’t think it has anything to do with the SQL. The error message

Input string was not in a correct format.

is exactly the message you get with the FormatException thrown by Convert.ToInt32() when the input string is not a number.

Therefore, instead of:

fin.PalletNumber = Convert.ToInt32(txtSearchPallet.Text);

You should perhaps try something like...

if (!int.TryParse(txtSearchPallet.Text, out fin.PalletNumber))
{
    MessageBox.Show("Please enter a number between x and y.");
    return;
}

This will attempt to turn the string into an integer and store the result in fin.PalletNumber. If the conversion fails, the MessageBox appears to alert the user and the operation is not attempted.

Answer 4

This error is nothing to do with your SQL.

The error is in the line you asterisk'ed in your post.

fin.PalletNumber = Convert.ToInt32(txtSearchPallet.Text);

If txtSearchPallet.Text is empty, you are trying to convert an empty string into an integer, and this is what gives you the "Input string was not in a correct format." exception.

You need to first of all detect if the pallet number is an empty string, and assign -1 (say) to your FillInEntity instance.

e.g.

fin.PalletNumber = (txtSearchPallet.Text == "") ? -1 : Convert.ToInt32(txtSearchPallet.Text);

This will stop the exception happening.

I would continue then as follows:

Change the line assigning the parameter value to the following

DbParameter pallet = cmd.Parameters.Add("@PalletNumber", SqlDbType.Int);
if (fin.PalletNumber == -1) 
    pallet.Value = DBNull.Value
else
    pallet.Value = fin.PalletNumber

Change the stored proc so that @PalletNumber is of datatype int, and the where clause is

WHERE 
(
   SerialNumber LIKE @SerialNumber + '%' 
   AND 
   (
       @PalletNumber is null 
       or 
       PalletNumber =  @PalletNumber
   )
)