Hasrows return false with string even there is a result

Question

0.00/5 (No votes)

See more:

when I used HasRows it returns false with string but when I used it with int it returns true

What I have tried:

C#

SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from dbo.FZMain where ID = @ID or ChassisNO = @ChassisNO or Name = @Name or CarModel = @CarModel or NumPlate = @NumPlate or CarBrand = @CarBrand or Cases = @Cases or CarBrand = @CarBrand or PlaceOfWork = @PlaceOfWork or PlaceOfBirth = @PlaceOfBirth or Address = @Address or Status = @Status or ModelYear = @ModelYear or cert_number = @cert_number or Payment = @Payment or CustomsCertNum = @CustomsCertNum ", conn);
                
                comm.Parameters.AddWithValue("ID", TextBox1.Text);
                comm.Parameters.AddWithValue("Name", TextBox1.Text);
               comm.Parameters.AddWithValue("ChassisNO", TextBox1.Text);
                comm.Parameters.AddWithValue("CarModel", TextBox1.Text);
               comm.Parameters.AddWithValue("NumPlate", TextBox1.Text);
                comm.Parameters.AddWithValue("CarBrand", TextBox1.Text);
                comm.Parameters.AddWithValue("Cases", TextBox1.Text);
                comm.Parameters.AddWithValue("PlaceOfWork", TextBox1.Text);
                comm.Parameters.AddWithValue("PlaceOfBirth", TextBox1.Text);
                comm.Parameters.AddWithValue("Address", TextBox1.Text);
                comm.Parameters.AddWithValue("Status", TextBox1.Text);
                comm.Parameters.AddWithValue("CustomsCertNum", TextBox1.Text);
                comm.Parameters.AddWithValue("ModelYear", TextBox1.Text);
                comm.Parameters.AddWithValue("cert_number", TextBox1.Text);
                comm.Parameters.AddWithValue("Payment", TextBox1.Text);

                conn.Open();
                SqlDataReader reader = comm.ExecuteReader();
                DataTable dt = new DataTable();
                
                if (reader.HasRows)                    
                {
                    comm.Connection = conn;
                    GridView1.Visible = true;
}
else{

string display = "not found ";
                    ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + display + "');", true);
}

it always return that alert

Posted 6-Mar-20 21:25pm

Member 13058758

Updated 6-Mar-20 23:32pm

v2

Add a Solution

3 solutions

Solution 2

Without your actual data it's impossible for us to check, but I'd guess that the value in your textbox does not exactly match any of your terms: "=" in SQL requires an exact match, and an extra or missing space will cause a comparison to fail.
You could try LIKE:

SQL

... WHERE MyColumn LIKE '%' + @MyUserInput + '%' ...

as that will match "AAAAABCDDDD" and "BC" for an input of "BC" without any problems.

You also need to put the "variable" specifier in each parameter when you add it:

SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from dbo.FZMain where ID = @ID or ChassisNO = @ChassisNO or Name = @Name or CarModel = @CarModel or NumPlate = @NumPlate or CarBrand = @CarBrand or Cases = @Cases or CarBrand = @CarBrand or PlaceOfWork = @PlaceOfWork or PlaceOfBirth = @PlaceOfBirth or Address = @Address or Status = @Status or ModelYear = @ModelYear or cert_number = @cert_number or Payment = @Payment or CustomsCertNum = @CustomsCertNum ", conn);
                
comm.Parameters.AddWithValue("@ID", TextBox1.Text);
comm.Parameters.AddWithValue("@Name", TextBox1.Text);
comm.Parameters.AddWithValue("@ChassisNO", TextBox1.Text);
comm.Parameters.AddWithValue("@CarModel", TextBox1.Text);

I'd also change your query: if you are using the same input field for every comparison, then why pass it as multiple parameters? Either pass separate text boxes, or just pass the text box once:

SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from dbo.FZMain where ID = @ID or ChassisNO = @ChassisNO or Name = @Name or CarModel = @CarModel or NumPlate = @NumPlate or CarBrand = @CarBrand or Cases = @Cases or CarBrand = @CarBrand or PlaceOfWork = @PlaceOfWork or PlaceOfBirth = @PlaceOfBirth or Address = @Address or Status = @Status or ModelYear = @ModelYear or cert_number = @cert_number or Payment = @Payment or CustomsCertNum = @CustomsCertNum ", conn);

comm.Parameters.AddWithValue("@ID", tbID.Text);
comm.Parameters.AddWithValue("@Name", tbName.Text);
...

Or

SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from dbo.FZMain where ID = @SEARCH or ChassisNO = @SEARCH or Name = @SEARCH or CarModel = @SEARCH or NumPlate = @SEARCH or CarBrand = @SEARCH or Cases = @SEARCH or CarBrand = @SEARCH or PlaceOfWork = @SEARCH or PlaceOfBirth = @SEARCH or Address = @SEARCH or Status = @SEARCH or ModelYear = @SEARCH or cert_number = @SEARCH or Payment = @SEARCH or CustomsCertNum = @SEARCH", conn);

comm.Parameters.AddWithValue("@SEARCH", TextBox1.Text);

Posted 6-Mar-20 21:49pm

OriginalGriff

Comments

Member 13058758 7-Mar-20 4:09am

the same problem and I changed sql command

to this

SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from FZMain WHERE Name = '" + TextBox1.Text + "' or ID = '" + TextBox1.Text + "' or ChassisNO = '" + TextBox1.Text + "' or CarModel = '" + TextBox1.Text + "' or NumPlate = '" + TextBox1.Text + "' or CarBrand = '" + TextBox1.Text + "' or Cases = '" + TextBox1.Text + "' or CarBrand = '" + TextBox1.Text + "' or PlaceOfWork = '" + TextBox1.Text + "' or PlaceOfBirth = '" + TextBox1.Text + "' or Address = '" + TextBox1.Text + "' or Status = '" + TextBox1.Text + "' or ModelYear = '" + TextBox1.Text + "' or cert_number = '" + TextBox1.Text + "' or Payment = '" + TextBox1.Text + "' or CustomsCertNum = '" + TextBox1.Text + "' ", conn);

OriginalGriff 7-Mar-20 4:25am

DO NOT DO THAT!
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Show us exactly what is in a "matching row", the exact value in your textbox as seen by the debugger, and the new (parameterised) query you are trying.
We can't work with no information, and we have no access to your DB or what your user types!

Member 13058758 7-Mar-20 4:31am

i know that problem i make it to sure that there is no problem in my first sqlcommand and the problem with has-row function

OriginalGriff 7-Mar-20 5:03am

No it isn't - the HasRows property has been there for twenty years unchanged, and if it didn't work, that would have been noticed by now! :laugh:

So Occam's Razor says: it's your DB, or your user input. Honest!

Member 13058758 7-Mar-20 5:07am

:D :D so

I try to reduce command with that sql command

SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from dbo.FZMain where ID = @ID or ChassisNO = @ChassisNO or Name = @Name", conn);
and it's run correctly
you has any suggest to put all query without problem

OriginalGriff 7-Mar-20 5:18am

Add the next one in, give it exactly the same input, and see what happens.
If that works, add another, and keep trying until it stops.

For simplicity, use a fixed string instead of user input for testing, so you know it's not a "keying error"

Member 13058758 7-Mar-20 5:31am

I know where is my error this two column datatype was int I convert it to varchar and problem solved (Payment = @Payment or CustomsCertNum = @CustomsCertNum)

OriginalGriff 7-Mar-20 5:41am

There you go - it wasn't HasRows! :laugh:
Well done.

Member 13058758 7-Mar-20 5:42am

Yup :D :D

Solution 3

Why are you creating so many parameters since you are only passing one single value? As is, your query could be reduced to

C#

SqlCommand comm = new SqlCommand("SELECT Name, ChassisNO, CarModel, NumPlate, CarBrand, CustomsCertNum, ModelYear FROM dbo.FZMain WHERE ID = @value OR ChassisNO = @value OR Name = @value OR CarModel = @value OR NumPlate = @value OR CarBrand = @value OR Cases = @value OR PlaceOfWork = @value OR PlaceOfBirth = @value OR Address = @value OR Status = @value OR ModelYear = @value OR cert_number = @value OR Payment = @value OR CustomsCertNum = @value", conn);
comm.Parameters.AddWithValue("@value", TextBox1.Text);

If some of the columns are not of varchar/nvarchar type, it could be wise to create distinct parameters and use proper parsing method. At the moment, all your parameters will be created and passed as nvarchar.

Finally, you are the only one having all elements to fully investigate what is going on; we do not have your database schema (specifically, we would need to know the datatype of the columns). And we are missing a clear description of the issue. Which value(s) do produce results, and which one(s) don't? You may also put a breakpoint in your code and start a debugging session; this would allow you to investigate your variables and find the core reason of your issue.

Posted 6-Mar-20 22:00pm

phil.o

Comments

Member 13058758 7-Mar-20 4:10am

the same problem
return false with string but int return data
I changed my sqlcommand to this and also the same problem
SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from FZMain WHERE Name = '" + TextBox1.Text + "' or ID = '" + TextBox1.Text + "' or ChassisNO = '" + TextBox1.Text + "' or CarModel = '" + TextBox1.Text + "' or NumPlate = '" + TextBox1.Text + "' or CarBrand = '" + TextBox1.Text + "' or Cases = '" + TextBox1.Text + "' or CarBrand = '" + TextBox1.Text + "' or PlaceOfWork = '" + TextBox1.Text + "' or PlaceOfBirth = '" + TextBox1.Text + "' or Address = '" + TextBox1.Text + "' or Status = '" + TextBox1.Text + "' or ModelYear = '" + TextBox1.Text + "' or cert_number = '" + TextBox1.Text + "' or Payment = '" + TextBox1.Text + "' or CustomsCertNum = '" + TextBox1.Text + "' ", conn);

phil.o 7-Mar-20 4:24am

Never build SQL scripts by concatenating strings. You had it right using parameters in the first place, why did you revert back to the wrong method?
Besides, I'm not saying that the query I wrote down would solve the issue, but rather that there is no point in creating several parameters for a single value. OriginalGriff is right when he says you should use LIKE operator rather than = when comparing strings (solution 2).

Member 13058758 7-Mar-20 4:34am

I try It the same problem
when I put sql query in SQlServer it retrieve data

phil.o 7-Mar-20 4:48am

As I said, you are the only one who can investigate this properly, because only you have all relevant elements. Please use your debugger. Do you need help on how to conduct a proper debug session?

Member 13058758 7-Mar-20 4:52am

I try to reduce command with that sql command

SqlCommand comm = new SqlCommand("Select Name, ChassisNO, CarModel, NumPlate,CarBrand,CustomsCertNum,ModelYear from dbo.FZMain where ID = @ID or ChassisNO = @ChassisNO or Name = @Name", conn);
and it's run correctly
you has any suggest to put all query

phil.o 7-Mar-20 5:07am

Keep adding fields one-by-one and testing, until you find which one is causing the query to return zero result. It will give you/us a valuable information as to what is happening. I can also see that you still did not replace strict equality testing with LIKE operator, as it has been advised.

Member 13058758 7-Mar-20 5:43am

I know where is my error this two column datatype was int I convert it to varchar and problem solved (Payment = @Payment or CustomsCertNum = @CustomsCertNum)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13058758 · Accepted Answer · 2020-03-06T23:32:00

Solution 4

I know where is my error this two column datatype was int I converted it to varchar and problem solved

C#

(Payment = @Payment or CustomsCertNum = @CustomsCertNum)

Posted 6-Mar-20 23:32pm

Member 13058758

Comments

phil.o 8-Mar-20 6:41am

That is a terrible idea to modify the datatype of columns rather than the code working on these columns. Now you end up with columns not having proper type (there is a reason everything is not stored as string in a database).

Richard Deeming 9-Mar-20 17:29pm

As phil said, leave the columns using the correct data type, and change how you pass the parameters.

if (int.TryParse(TextBox1.Text, out int value))
{
    comm.Parameters.AddWithValue("CustomsCertNum", value);
    comm.Parameters.AddWithValue("Payment", value);
}
else
{
    comm.Parameters.AddWithValue("CustomsCertNum", DBNull.Value);
    comm.Parameters.AddWithValue("Payment", DBNull.Value);
}