Winforms: How to query a database with a sql function using SqlCommand?

Question

I have a method which takes a single string parameter (ID).

I want to use SqlCommand to return a DataTable of results from a query. I'm trying to call a table function from my database (Sql Server) in the query and pass in my ID parameter. The contents of this DataTable will then populate a Combobox. Here's what I have so far...

 public string populateCompanyTransSellingEntityLookUp(string BlockId)
    {
        string _sql = "";  

        SqlCommand _comm = new SqlCommand();
        _comm.Parameters.AddWithValue("(@block_id", BlockId);
        _comm.CommandText = "SELECT [name] FROM dbo.fnGetBlockCompanyWIList(@block_id) ORDER BY [name]; ";
        _comm.Connection = _conn;
        _comm.CommandTimeout = _command_timeout;

        DataTable dt = new DataTable();
        try
        {
            SqlDataReader myReader = _comm.ExecuteReader();
            dt.Load(myReader);
        }
        catch (Exception)
        {

            throw;
        }

        Combo.DataSource = dt;
        return _sql;                
    }

But i'm getting a error, "Must declare scalar variable '@block_id'". why?

_comm.Parameters.AddWithValue("@block_id", BlockId);_comm.CommandText = "SELECT [company_int_name] FROM dbo.fnGetBlockCompanyWIList(@block_id) ORDER BY [company_int_name]; "; — kostas ch.
– kostas ch., Commented Mar 13, 2014 at 15:16
What exactly is wrong? Do you get an exception? Is the result set empty? Nobody can help you if you don't ask a question. — Sean Airey
– Sean Airey, Commented Mar 13, 2014 at 15:27

Selman Genç · Accepted Answer · 2014-03-13 16:05:59Z

4

You have an extra bracket here, you should remove it:

 _comm.Parameters.AddWithValue("(@block_id", BlockId);
                               ^^^

And perhaps it doesn't matter but give value to your parameter after you set the CommandText:

_comm.CommandText = "SELECT [name] FROM dbo.fnGetBlockCompanyWIList(@block_id) ORDER BY [name]; ";
_comm.Parameters.AddWithValue("@block_id", BlockId);

answered Mar 13, 2014 at 16:05

Selman Genç

102k15 gold badges126 silver badges193 bronze badges

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

2 Comments

V-SHY Over a year ago

how about _comm.Connection = _conn;, can it be placed after _comm.CommandText?

Selman Genç Over a year ago

@V-SHY yes,it shouldn't matter.they are different properties.The important thing is setting the Connection property before try to execute the command.

Collectives™ on Stack Overflow

Winforms: How to query a database with a sql function using SqlCommand?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related