How do I resolve ExecuteScalar issue?

Question

User-218090889 posted

Hi Friends,

First, I want to appreciate you guys that responded to my last post. Thank you all.

Now, still in the same project I am doing,
I have set of code to handle event for GridView databound.
I want to get an Id (UserId) from database which is placed on a textbox (Id =UserIdTextBox), then use the value of the textbox to make a select count in the database, get the count value and use it for other purpose. I connect to my database using database class on a separate class file. Below is my code in code behind:

 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        Int32 UserFriendId = 0;

 if (e.Row.RowType == DataControlRowType.DataRow)
        {

 TextBox UserID = (TextBox)(e.Row.FindControl("UserIdTextBox"));
            string UserId = UserID.Text;

 string getUserFriendId = "SELECT COUNT(*) FROM TableName WHERE (UserfriendId = '"  +UserId + "')";

DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();

     ds = dbClass.ConnectDataBaseReturnDS(getUserFriendId);

    UserFriendId = ((Int32)cmd.ExecuteScalar());

}

/// This is my Database class

 public DataSet ConnectDataBaseReturnDS(string Query)
        {
            ds = new DataSet();
            con = new SqlConnection(@"Data Source=MyName;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False");
            cmd.CommandText = Query;
            cmd.Connection = con;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.ExecuteScalar();
            con.Close();
            return ds;
        }

But when I try to run the code I error as below:

Exception Details: System.InvalidOperationException: ExecuteScalar: Connection property has not been initialized.

So How do I go about that.

Answer 1

User1577371250 posted

Hi,

You need to Write a seperate method for the ExecuteScalar().

public object ExecuteDBScalar(string Query)
        {
            ds = new DataSet();
            con = new SqlConnection(@"Data Source=MyName;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False");
            cmd.CommandText = Query;
            cmd.Connection = con;
           
            con.Open();

          object result =  cmd.ExecuteScalar();
            con.Close();
            return result;
        }

object res = dbClass.ExecuteDBScalar(getUserFriendId);

int scalarValue = (Int32)res;

Answer 2

User-2057865890 posted

Hi Enzyme,

First I will recommend you to assigned a connection to sql command as shown in the link below.
http://stackoverflow.com/questions/27660124/visual-studio-c-sharp-and-sql-server-connection-property-has-not-been-initiali

the other way is mentioned in the link below.
http://stackoverflow.com/questions/5866046/executereader-connection-property-has-not-been-initialized
https://www.experts-exchange.com/questions/27800987/ExecuteScalar-Connection-property-has-not-been-initialized.html

to get more information regarding how to use SqlCommand.ExecuteScalar Method ()
visit link below.
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx

Regards

Chris

Answer 3

User-218090889 posted

Hi Lokesh B R,

I want to thank you, the code is working very well.

Thanks for you marvelous contribution.

I will like to be keeping in touch for more guidance if you will permit me.

Remain blessed

Answer 4

User1577371250 posted

Hi,

The forum is open for you always to help.

You can even leave a private message to me if you need any assistance.

Happy Coding :)