AddWithValue not working on SqlCommand query

Question

The method GetData in the following code works as long as I use valid column names, however, when trying to use a variable (query string parameter value) in the SQL query, I get empty results.

I am assuming I am not using the .AddWithValue method properly. Am I not writing the SQL command properly, or does it have something to do with the code placement of the .AddWithValue method call? Or something else I am missing?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.AspNet.FriendlyUrls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Koobek
{
    public partial class WebForm6 : System.Web.UI.Page
    {
        string cat = "";
        string getcat = "";

        protected void Page_Load(object sender, EventArgs e)
        {
            var segments = Request.GetFriendlyUrlSegments();
            int count = segments.Count;

            if (segments.Count > 0)
                cat = segments[0];

            string getcat = Request.QueryString["cat"];

            ListView1.DataSource = this.GetData();
            ListView1.DataBind();

            System.Diagnostics.Debug.WriteLine(getcat);
        }

        private DataSet GetData()
        {  
            string conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            string query = @"SELECT DISTINCT newcatdisplay, newclassdisplay, newclass, newcat FROM ejn_series WHERE newcat = @getcat ORDER BY newclassdisplay";
            SqlCommand cmd = new SqlCommand(query);
            cmd.Parameters.AddWithValue("@getcat", getcat);

            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;

                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);

                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            System.Console.WriteLine("empty");
                        }

                        return ds;
                    }
                }
            }
        }        
    }
}

I think the problem is with the way you define the `DataSet` with `using`. As soon as you `return` the `DataSet`, it exits the `using` block, and disposes of it. Try getting rid of the `using` and making it a normal variable declaration. — Bradley Uffner, Jul 11 '17 at 19:57
Again though, if I replace @cat in the query string with newclass ( a valid table name) I get results. — codeRed, Jul 11 '17 at 20:03
Well, you should try to use the debugger and check if really the variable _cat_ has the value that you expect — Steve, Jul 11 '17 at 20:09
You mean a valid *column* name, not *table* name, right? The table name `ejn_series` is already hard coded in the query. — Rufus L, Jul 11 '17 at 20:20
Wait, do you actually *want* to compare the `newcat` field with another field (chosen programatically) from the database table? If so, you can't use an `SqlParameter` for that. They are **only** for comparing literal values. — Bradley Uffner, Jul 11 '17 at 20:33
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Jul 11 '17 at 20:44

score -1 · Answer 1 · answered Jul 12 '17 at 04:44

-1

You cannot add parameters to a text sql statement. Do this:

 string query = @"SELECT DISTINCT newcatdisplay, newclassdisplay, 
        newclass, newcat FROM ejn_series WHERE newcat = '" +  getcat + "' " +
        "ORDER BY newclassdisplay";

answered Jul 12 '17 at 04:44

Gregg

615
6
6

Personally, I would never execute sql text in this manner but instead use a stored procedure – Gregg Jul 20 '17 at 15:41
You absolutely [can](https://stackoverflow.com/q/31420045/11683) (and [should](https://stackoverflow.com/q/332365/11683)) add parameters to a text sql statement. – GSerg Feb 01 '23 at 20:09

AddWithValue not working on SqlCommand query

1 Answers1