Passing a sqlcommand as a parameter. Throws invalid object error even if the object exist in the database

Question

I have a problem with the following code. Please help.

What I am trying to do is pass a sqlcommand to a function which then returns a dataset.

The function 'Get data' takes sqlcommand as a parameter. This function is in class 'DatabaseUtilities'

    //Initializing sql connection
    static SqlConnection _Connection = new SqlConnection("Data Source=(local);Initial Catalog=db_Test;Integrated Security=True");

    //Connection property
    public static SqlConnection Connection
    {
        get {return _Connection;}
    }

    //The class that takes sqlcommand as parameter
    public static DataSet GetData(SqlCommand Command)
    {
        _Connection.Open();

        SqlDataAdapter Adapter = new SqlDataAdapter();
        Adapter.SelectCommand = Command;

        DataSet Table = new DataSet();

        Adapter.Fill(Table);

        _Connection.Close();

        return Table;
    }

This is how sqlcommand is passed into the above function. This function is from a different class.

public DataSet GetLogByDate(string SearchValue)
    {
        Command.CommandType = CommandType.StoredProcedure;
        Command.Connection = DatabaseUtilities.Connection;

        Command.CommandText = "sp_GetLogByDate";
        Command.Parameters.AddWithValue("@LogDate", SearchValue);

        return GetData(Command);
    }

This code throws the fllowing error.

Invalid object name 'sp_GetLogByDate'.

I do have the above stored procedure in my database. I have no idea why this happened. Could anyone help?

static SqlConnectionis not a good idea when you use multiple threads (e.g. ASP.NET) and even Connection-Pooling is enabled (default): — Tim Schmelter
– Tim Schmelter, Commented Mar 14, 2013 at 17:20

MarcinJuraszek · Accepted Answer · 2013-03-14 17:21:19Z

2

You have to connect Command with Connection:

//The class that takes sqlcommand as parameter
public static DataSet GetData(SqlCommand Command)
{
    Command.Connection = _Connection;

answered Mar 14, 2013 at 17:21

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

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

2 Comments

Abhishek Singh Over a year ago

I did that in the class that passes the sqlcommand. public DataSet GetLogByDate(string SearchValue) { Command.CommandType = CommandType.StoredProcedure; Command.Connection = DatabaseUtilities.Connection;

JeffO Over a year ago

@AbhishekSingh Command.Connection = DatabaseUtilities.Connection is not the same as the instance _Connection which is the one you opened.

Collectives™ on Stack Overflow

Passing a sqlcommand as a parameter. Throws invalid object error even if the object exist in the database

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