ExecuteScalar requires an open and available Connection. The connection's current state is closed.

change it like this...

using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
FORTUNEConnection1.open();

                SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = '{0}'", Login_FORTUNE.UserName), FORTUNEConnection1);
                CompanyAgent_ID = (int)scCommand.ExecuteScalar();
                FORTUNEConnection1.Close();
            }

Thank you for your response.

Made the adjustments as suggested -- I am now getting the following error message when compiling as follows:

'System.Data.SqlClient.SqlConnection' does not contain a definition for 'open'

thts a typo
use capital "O"
.Open()

FORTUNEConnection1.Open();

Yes, it was a typo -- thanks for confirming.

Have updated, and the script compiles now ok.

There is an error as follows:

The ConnectionString property has not been initialized.

check whether this has the proper connection string

FORTUNESQLDataSource.ConnectionString !!!!

ok -- I must have deleted accidentally when cutting and pasting section of code.  

thank you for keeping me on track with this issue.

when running the program -- I am getting the following syntax error as follows (i.e. this line is highlighted by debugger):

CompanyAgent_ID = (int)scCommand.ExecuteScalar();

SQL Syntax error

try running the query in SQL and see if u are getting the desired Output..

your query is wrong ...use this line instead...

SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = '{0}')", Login_FORTUNE.UserName), FORTUNEConnection1);

there was a bracket missing...you started one after the WHERE clause and you did not end it...

getting error (running sql script direct on sql server) as follows:

Incorrect syntax near '{0}'.

can u post the scrip u ran on the server

use the statement i gave earlier...there is a bracket missing in your query...

this is the proper statement(which was also give few replies before)

SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = '{0}')", Login_FORTUNE.UserName), FORTUNEConnection1);

@gauthampj - it is clear from the query that a bracket is missing so i think that is the problem...he is not able to execute the query is SQL window because he is executing it with {0} and not by passing the value...

@amillyard, did you try this ?
SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = '{0}')", Login_FORTUNE.UserName), FORTUNEConnection1);

Yes, that seems to have worked ok.

I seem to have an type issue on this line (near the very end) --

                CompanyAgentName_ID = (string)scCommand.ExecuteScalar();

the first database connection retrieves the CompanyAgent the logged-in user is assigned too...i.e. an integer number.

the second database connection retrieves the CompanyAgentName (i.e. description - name) -- by searching the next table in database ... looking for the number retrieved from the 1st database check and search that against the 2nd search and then retrieving the name (description).

this description field is then written to a session variable.

as mentioned above -- the 2nd database search code is not quite there... but am I doing database lookup correctly (efficiently as well) -- by opening and closing 2 databases sessions and searching for each parameter seperately?

_______________________________________________________

SqlDataSource FORTUNESQLDataSource = new SqlDataSource();

            FORTUNESQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["FORTUNEConnectionString"].ToString();

            int CompanyAgent_ID;

            using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                FORTUNEConnection1.Open();

                SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = '{0}')", Login_FORTUNE.UserName), FORTUNEConnection1);
                CompanyAgent_ID = (int)scCommand.ExecuteScalar();
                FORTUNEConnection1.Close();
            }



            string CompanyAgentName_ID;

            using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                FORTUNEConnection1.Open();

                SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID], [CompanyAgentName] FROM [CompanyAgents] WHERE ([CompanyAgent_ID] = '{0}')", CompanyAgent_ID), FORTUNEConnection1);
                CompanyAgentName_ID = (string)scCommand.ExecuteScalar();
                FORTUNEConnection1.Close();
            }

            Session["fortuneCompanyAgent"] = CompanyAgentName_ID;

this is a wrong method that you are following use a join instead lijke

remove your last (FULL) code that you posted and use this alone instead...
 string CompanyAgentName_ID;

            using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                FORTUNEConnection1.Open();

                SqlCommand scCommand = new SqlCommand((String.Format("SELECT b.[CompanyAgentName] FROM [StaffMember] a,[CompanyAgents] b WHERE a.[CompanyAgent_ID] = b.[CompanyAgent_ID] and a.[ASPnetUserName] = '{0}'", Login_FORTUNE.UserName), FORTUNEConnection1);
                CompanyAgentName_ID = (string)scCommand.ExecuteScalar();
                FORTUNEConnection1.Close();
            }

Session["fortuneCompanyAgent"] = CompanyAgentName_ID;

what a pleasure, jaiganeshsrinivasan -- you really have hit the nail -- just checked and worked first time (after adding an extra bracket)

apprieate your generous time and efforts with this enquiry,

-many thanks :-)



updated script (with extra bracket)

            SqlDataSource FORTUNESQLDataSource = new SqlDataSource();
            FORTUNESQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["FORTUNEConnectionString"].ToString();

            int CompanyAgent_ID;
            string CompanyAgentName_ID;

            using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                FORTUNEConnection1.Open();

                SqlCommand scCommand = new SqlCommand((String.Format("SELECT b.[CompanyAgentName] FROM [StaffMember] a,[CompanyAgents] b WHERE a.[CompanyAgent_ID] = b.[CompanyAgent_ID] and a.[ASPnetUserName] = '{0}'", Login_FORTUNE.UserName)), FORTUNEConnection1);
                CompanyAgentName_ID = (string)scCommand.ExecuteScalar();
                FORTUNEConnection1.Close();
            }

            Session["fortuneCompanyAgent"] = CompanyAgentName_ID;