Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-10
20
Medium Priority
?
4,869 Views
Last Modified: 2013-11-26
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,
0
Comment
Question by:amillyard
  • 11
  • 7
  • 2
20 Comments
 
LVL 14

Expert Comment

by:Jai S
ID: 20053964
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
 

Author Comment

by:amillyard
ID: 20054054
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20054077
thts a typo
use capital "O"
.Open()
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 14

Expert Comment

by:Jai S
ID: 20054088
FORTUNEConnection1.Open();

0
 

Author Comment

by:amillyard
ID: 20054092
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20054100
check whether this has the proper connection string

FORTUNESQLDataSource.ConnectionString !!!!
0
 

Author Comment

by:amillyard
ID: 20054126
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
 

Author Comment

by:amillyard
ID: 20054128
SQL Syntax error
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 20054154
try running the query in SQL and see if u are getting the desired Output..
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20054198
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20054199
there was a bracket missing...you started one after the WHERE clause and you did not end it...
0
 

Author Comment

by:amillyard
ID: 20054201
getting error (running sql script direct on sql server) as follows:

Incorrect syntax near '{0}'.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 20054204
can u post the scrip u ran on the server
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20054207
use the statement i gave earlier...there is a bracket missing in your query...
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20054215
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20054220
@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
 

Author Comment

by:amillyard
ID: 20054267
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20054285
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
 
LVL 14

Accepted Solution

by:
Jai S earned 2000 total points
ID: 20054289
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
 

Author Comment

by:amillyard
ID: 20054310
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question