When using the following block of code to pull the top 5 records, I get wildly different results than running the same script in SQL Sever 2008.
DataTable results = new DataTable();
string cmdTxt = String.Format("select top 5 ID, NAME from TABLE where ID like @ID or NAME like @NAME order by ID asc");
using (SqlCommand cmd = new SqlCommand(cmdTxt, Butch.connection))
{
cmd.Parameters.Add("@ID", SqlDbType.Char, 15).Value = IdBox.Text + "%";
cmd.Parameters.Add("@NAME", SqlDbType.Char, 65).Value = IdBox.Text + "%";
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
adapter.Fill(results);
}
For example passing the parameter '0' into IdBox, I get one row in results with the Id '01', but in SQL Server I get 5 rows back with Id's '000001', '000002', '000003', '000004', '000005'. Passing '00' instead will return zero rows from my code, but the same 5 from SQL Server. If I pass '00000' I will get the same results from code as I do from SQL sever.
Is there anyway of making my code return the same results of my SQL query? I have tried using more wild card symbols in my code parameters in case it was only checking it against one character, however that didn't work. I have the same code working elsewhere that uses a NVarChar SqlDataType, but I don't know why it isn't working here.
SqlDbType.Char corresponds to CHAR, which is blank padded, if needed, to be exactly the length given.
Your query ends up being something like:
select top 5 ID, NAME from TABLE where ID like '0% ' or NAME like '% ' order by ID asc
And that pattern(s) just won't match.
Try SqlDbType.VarChar or SqlDbType.NVarChar (if the corresponding column is N... too).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments