SqlDataReader no rows found

Question

Here is my code

public string LeaderIdLookup(string leadername)
        {
        string step = null;
        try
            {

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select EmpId,Fullname from Employee where FullName like '@LeaderName'";
            cmd.Parameters.Add(new SqlParameter("LeaderName", SqlDbType.VarChar));
            cmd.Parameters["LeaderName"].Value = leadername.Trim();
            cmd.Connection = con;
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();

            step = "assigning the value from datareader to the variable lookup as a string (leaderidlookup) ";
            if (dr.HasRows)
                Lookup = dr[0].ToString();
            else
                Lookup = "no rows found";

            dr.Close();
            return Lookup;

            }
        catch (SqlException ex)
            {
            Lasterror = string.Format("step {0} threw sql error {1}", step, ex.Message);
            Debug.Print(Lasterror);
            return string.Empty;
            }
        catch (Exception ex)
            {
            Lasterror = string.Format("step {0} threw error {1}", step, ex.Message);
            Debug.Print(Lasterror);
            return string.Empty;
            }

        }

The problem is that SqlDataReader does not return any rows I have a hunch that it has to do with the Parameter substitution because when i hardcode a name in there instead of using a parameter it works perfectly

I can not seem to figure out where im going wrong.

Remove the quotes in the sql around the parameter and match the name of the parameter: new SqlParameter("@LeaderName", SqlDbType.VarChar)); — LarsTech
– LarsTech, Commented Oct 19, 2016 at 22:57

Dejan · Accepted Answer · 2016-10-19 23:25:46Z

1

You need to remove the quotes around "LIKE '@LeaderName'" and you must specify the parameter name with a leading @. So:

... new SqlParameter("@LeaderName", ...

answered Oct 19, 2016 at 23:25

Dejan

10.5k8 gold badges84 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matthew Whited · Accepted Answer · 2016-10-20 14:43:16Z

1

You need to remove the single quotes around the variable name in the query. Otherwise you are doing a literal compare between FullName and "@LeaderName" ... which is unlikely to exist (the reason you are getting no rows). You will also need to provide the Parameter name with a leading @.

cmd.CommandText = "select EmpId,Fullname from Employee where FullName like @LeaderName";
var leaderParameter = cmd.Parameters.Parameters.Add("@LeaderName", System.Data.SqlDbType.NVarChar);
leaderParameter.Value = "%" + leaderName.Trim() + "%";

edited Oct 20, 2016 at 14:43

answered Oct 19, 2016 at 22:00

Matthew Whited

22.5k4 gold badges54 silver badges69 bronze badges

2 Comments

Frederik van Vliet Over a year ago

tried without quotes still no rows and i know the entry exists because i use the exact same value i found in the table.

Matthew Whited Over a year ago

What is the value in the leaderName variable and does it match at least one of the FullName values? If you want to use a partial search your must include % as a wild card. (ie leaderName = "%Matt%";)

beeker · Accepted Answer · 2016-10-19 23:11:43Z

-1

Where is your SqlConnection? Try something like shown below. See here for connection string examples: https://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx#Anchor_2

Your connection string will be something like this: "Data Source=localhost;Initial Catalog=MyDataBaseName;Integrated Security=true" (assuming your are connecting to localhost and your account has privileges on the database of course)

try
    {
        using (SqlConnection sqlConnection = new SqlConnection("put your connection string here"))
        {
            sqlConnection.Open();
            using (SqlCommand sqlCommand = new SqlCommand("select EmpId,Fullname from Employee where FullName like @LeaderName", sqlConnection))
            {
                sqlCommand.CommandType = System.Data.CommandType.Text;
                sqlCommand.Parameters.Add("@LeaderName", SqlDbType.VarChar).Value = leadername;
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    //lots of options here, read it how you like...
                    string EmpID = sqlDataReader["EmpID"].ToString();
                    string FullName = sqlDataReader["FullName"].ToString();
                }
            }
        }
    }
 catch (Exception ex) { throw new System.ArgumentException(ex.Message); }

edited Oct 19, 2016 at 23:11

answered Oct 19, 2016 at 21:55

beeker

8108 silver badges19 bronze badges

5 Comments

Frederik van Vliet Over a year ago

doesnt work because it denotes a parameter substitution

beeker Over a year ago

I edited my answer above, after looking at your code again. It looks like maybe you are not making the connection to the server?

Frederik van Vliet Over a year ago

I never have an sql connection string in the program itself

Frederik van Vliet Over a year ago

Itr is in web.config where i pull it from

beeker Over a year ago

That's a fine place to put the connection string. The code above is simplified. The point was, where are you making a connection to the server? If you are making the connection outside the posted method then, the answer is a mix of my previous answer and Matthews. Remove quotes from SQL string and name the parameter correctly in your C# (e.g. with @ prefix to match your SQL).

Collectives™ on Stack Overflow

SqlDataReader no rows found

3 Answers 3

Comments

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related