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

Development Platform: c#, asp.net 2.x, Visual Studio Pro utilising Web Developer, IIS 6, SQL 2005 server.

The following scripting is producing the following error -- any thoughts as to why?


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

________________________________________________________________________________
            SqlDataSource FORTUNESQLDataSource = new SqlDataSource();

            int CompanyAgent_ID;

            using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                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();
            }


your time and effort with this enquiry is much apprieated.

many thanks,
amillyardAsked:
Who is Participating?
 
Jai STech ArchCommented:
the full code that you have to use

SqlDataSource FORTUNESQLDataSource = new SqlDataSource();

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

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;
0
 
Jai STech ArchCommented:
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();
            }

0
 
amillyardAuthor Commented:
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'
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Jai STech ArchCommented:
thts a typo
use capital "O"
.Open()
0
 
Jai STech ArchCommented:
FORTUNEConnection1.Open();

0
 
amillyardAuthor Commented:
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.
0
 
Jai STech ArchCommented:
check whether this has the proper connection string

FORTUNESQLDataSource.ConnectionString !!!!
0
 
amillyardAuthor Commented:
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();
0
 
amillyardAuthor Commented:
SQL Syntax error
0
 
Gautham JanardhanCommented:
try running the query in SQL and see if u are getting the desired Output..
0
 
Jai STech ArchCommented:
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);

0
 
Jai STech ArchCommented:
there was a bracket missing...you started one after the WHERE clause and you did not end it...
0
 
amillyardAuthor Commented:
getting error (running sql script direct on sql server) as follows:

Incorrect syntax near '{0}'.
0
 
Gautham JanardhanCommented:
can u post the scrip u ran on the server
0
 
Jai STech ArchCommented:
use the statement i gave earlier...there is a bracket missing in your query...
0
 
Jai STech ArchCommented:
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);

0
 
Jai STech ArchCommented:
@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);


0
 
amillyardAuthor Commented:
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;
0
 
Jai STech ArchCommented:
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;

0
 
amillyardAuthor Commented:
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;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.