?
Solved

c# asp.net sql read - selecting record & getting variable transferred locally

Posted on 2007-10-10
4
Medium Priority
?
589 Views
Last Modified: 2013-11-26
Development Platform : c#, asp.net 2.x, VIsual Studio Pro utilising Web Developer, IIS6, SQL Server 2005

I am trying to pull out a single user's (ASPnetUserName), associated CompanyAgent_ID number from the StaffMember database table as below -- then I want a local variable to equal the CompanyAgent_ID value.

The Login1.Username variable is from the login.aspx page (entry box) -- thats what I am wanting to compare with database -- ie. in selecting the correct username record.

In short, not sure if the sql statement is 100% correct and 2., how to get the CompanyAgent_ID value into a local variable.

Your time and efforts with this enquiry are much apprieated.

Thank you in advance.




SqlDataSource testSQLDataSource = new SqlDataSource();

            testSQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();

            System.Data.SqlClient.SqlCommand scCommand = new System.Data.SqlClient.SqlCommand();

            scCommand.CommandType = System.Data.CommandType.Text;
            scCommand.CommandText = "SELECT [ASPnetUserName], [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = @Login1.Username)";
            scCommand.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            SqlConnection sqlConnection1 = new SqlConnection(testSQLDataSource.ConnectionString);

            try
            {
                sqlConnection1.Open();
                scCommand.Connection = sqlConnection1;
                scCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SQL_FailureHandler(ex);
            }
            finally
            {
                sqlConnection1.Close();

                Label_SubmissionStatus.Text = "[ Comitted to Database OK ]";
            }
0
Comment
Question by:amillyard
  • 3
4 Comments
 
LVL 8

Accepted Solution

by:
slado2 earned 1500 total points
ID: 20051063
int CompanyAgend_ID;
using(SqlConnection sqlConnection1 = new SqlConnection(testSQLDataSource.ConnectionString))
{
  SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = '{0}'", Login1.Username), sqlConnection1);
  CompanyAgent_ID = (int)scCommand.ExecuteScalar();
  sqlConnection.Close();
}
0
 

Author Comment

by:amillyard
ID: 20053158
I have cut & pasted the supplied code.

There is an error message as follows:

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

With the previously supplied code -- was I supposed to removed/replace other code.  Is this supplied code all that is needed to mix and match with previous scripting etc.

cheers,
0
 
LVL 8

Expert Comment

by:slado2
ID: 20054471
Sorry. My fault.
int CompanyAgend_ID;
using(SqlConnection sqlConnection1 = new SqlConnection(testSQLDataSource.ConnectionString))
{
  sqlConnection.Open();
  SqlCommand scCommand = new SqlCommand(String.Format("SELECT [CompanyAgent_ID] FROM [StaffMember] WHERE ([ASPnetUserName] = '{0}'", Login1.Username), sqlConnection1);
  CompanyAgent_ID = (int)scCommand.ExecuteScalar();
  sqlConnection.Close();
}
0
 
LVL 8

Expert Comment

by:slado2
ID: 20054486
Once again:
sqlConnection1.Open(); instead of sqlConnection.Open();
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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