ExecuteReader: Connection property has not been initialized error issue

Question

While executing the below code I am getting error as

ExecuteReader: Connection property has not been initialized.

[WebMethod]
public static bool GetCurrentToBin(string ToBin)
{
    SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ToString());
    conn.Open();
    CommandFunction CF = new CommandFunction();

    SqlDataReader dr;
    dr = CF.ExecuteReader("exec sp_P_WMS_Stock_Adj_Validation_Proc '" + ToBin + "'");
    dr.Read();
    if (dr.HasRows)
    {
        conn.Close();
        return true;
    }
    return false;
}

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jan 21 '17 at 08:24

Christos · Accepted Answer · 2017-01-21T17:42:51.807

2

You could refactor your code like below and I think that you will get that you want. The problem is you don't specify anywhere to the CommandFunction object the sql connection that will be used. I am not aware of this object, but I think that this is the problem. The solution below uses the class SqlCommand.

using System.Configuration;

[WebMethod]
public static string GetCurrentToBin(string ToBin)
{
    var connectionString = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();
    using(var conn = new SqlConnection(connectionString))
    {
        const string queryString = "exec sp_P_WMS_Stock_Adj_Validation_Proc @Bin";

        var sqlCommand = new SqlCommand(queryString , conn);
        sqlCommand.Parameters.AddWithValue("@Bin",ToBin);

        conn.Open();
        var reader = sqlCommand.ExecuteReader();

        if(reader.Read() && !reader.IsDBNull(0))
        {
            return reader.GetString(0);
        }
        return null;
    }
}

From the above code you could take two things:

We don't explicitly close the sql connection. Instead of closing the connection explicitly, we let the using statement to do this for us. So anytime you want to create a new sql connection, wrap it in a using statement and you will not need again to remember to close the connection.
Use only parameterized queries. Not doing so, you let your application open to sql injections.

edited Jan 21 '17 at 17:42

answered Jan 21 '17 at 08:07

Christos

53,228
8
76
108

getting error as `Incorrect syntax near 'sp_P_WMS_Stock_Adj_Validation_Proc'.` – Nad Jan 21 '17 at 09:18
@nad correct ! I have missed a semicolon. Please try the updated one and let me know. – Christos Jan 21 '17 at 09:20
Getting that error at line `var reader = sqlCommand.ExecuteReader();` **UPDATE** you have not wriiten the `exec` keyword. – Nad Jan 21 '17 at 09:23
@nad I suppose adding the exec you are ok now. Correct? – Christos Jan 21 '17 at 09:34
yes its OK now. but it is not throwing error msg from my SP..for that what should I do any idea ? – Nad Jan 21 '17 at 09:38
What do you mean? Do you want to read the results of your sp or just to see if any results returned. My answer and your post do the second, they see if there are any results returned and return either true or false. – Christos Jan 21 '17 at 09:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/133696/discussion-between-nad-and-christos). – Nad Jan 21 '17 at 09:40
hi christos are u their ? just come on chat for few sec – Nad Jan 21 '17 at 11:26
@nad hi, what happened? – Christos Jan 21 '17 at 12:55

ExecuteReader: Connection property has not been initialized error issue

1 Answers1