[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

c# asp retrieving multiple columns from a sql db table sorted via datetimefield

Posted on 2007-10-18
12
Medium Priority
?
408 Views
Last Modified: 2013-11-26
Development platform : c#, asp.net 2.x, Visual Studio Pro utilising Web Developer, IIS 6, SQL Server 2005

The following script is working fine for searching for a match and bringing back a single column within a record.

What I would now like to do is search primarily by these two columns:

CreatedDateTimeStamp  (sql search to select the oldest record, cross-referenced with AssignedStaffMember)


and then pull back multiple columns from the same table.

Your time and efforts with this enquiry are much apprieated.

Many thanks.

___________________________________________________________________

int ClientCaseSearch = -1;
            string strippedClientCaseString = StripNonNumeric(TextBox_ClientCase.Text);

            using (SqlConnection FORTUNEConnection2 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                FORTUNEConnection2.Open();
                SqlCommand sc2Command = new SqlCommand((String.Format("SELECT DISTINCT [MasterAccount_ID] FROM [ClientCases] WHERE [ClientCase_ID] = '{0}'", strippedClientCaseString)), FORTUNEConnection2);
                object temp = sc2Command.ExecuteScalar();
                FORTUNEConnection2.Close();

                if (temp != null)
                {
                    Session["fortuneMasterAccount"] = temp;  
                    Session["fortuneCaseAccount"] = strippedClientCaseString;
                    Response.Redirect("default.aspx");
                }
            }

            if ((MasterAccountSearch != 1) || (ClientCaseSearch != 1))
                Label_MasterAccountSearchStatus.Text = "No records were found";
0
Comment
Question by:amillyard
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20100748
I don't understand your requirement.  Are you saying that you want to modify the SQL statement builder to include an additional column?

Bob
0
 
LVL 9

Expert Comment

by:divinewind80
ID: 20100763
It sounds like you are wanting to have a selection list based only on the two columns.  Then, after the user selects the proper record, the database is queried again to return more columns from that record.  Is this correct?
0
 

Author Comment

by:amillyard
ID: 20100823
The following script is working fine for searching for a match and bringing back a single column within a record.

the above script is used for a different part of page/program -- as is good for that purpose.

as still learning sql/asp/c# etc, I was making reference to that script as a starting point to build on perhaps (unless of course there is a different scripting approach when want more than 1 column of data returned) -- the extra searching I require.  Just wanted to make sure I am going about it in the right direction.

Want to be able to search primarily by these two columns:

CreatedDateTimeStamp  (sql search to select the oldest record, getting date/time from the CreatedDateTiimeStamp field, cross-referenced with AssignedStaffMember -- i.e. find the oldest created record for AssignedStaffMember)

and then pull back multiple columns from the same table (i.e.  name, tel, email address etc)

I hope this helps a bit more -- apologies for any misunderstandings, just that I know what I want to achieve, just expressing in a language we all understand is a bit and miss sometimes.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Expert Comment

by:divinewind80
ID: 20100918
One option would be to run a similiar section of code again, with a different SQL statement.  For instance, if you ran the code as follows to get your "search columns", you could set the SQL code as follows when a person selected a record:

To Get "Search" fields:
SELECT DISTINCT [MasterAccount_ID] FROM [ClientCases] WHERE [ClientCase_ID] = '{0}'"

To get details:
SELECT * FROM [CLIENTCASES] WHERE MASTERACCOUNT_ID = " & your selected value & ""

This will return the full record based on your original selection.

Does that help?
0
 

Author Comment

by:amillyard
ID: 20101123
@divinewind80

ok, I think we are getting closer !

from the SELECT statement -- how do I pull apart the various column data?

i.e. name.text = "[sql record data]";  address.text = "[sql record data]" etc.

as my above script supplied -- the variable, strippedClientCaseString is containing 1 data entry, not multiples for example.

Session["fortuneCaseAccount"] = strippedClientCaseString;
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20103686
ExecuteScalar is optimized to return a single value. A DataReader is probably the best way to get back a single row, or a subset of the columns from a single row. Try it like this:

string name = string.Empty;
string tel = string.Empty;
string emailAddress = string.Empty;
string sql = "SELECT name, tel, email_address FROM ClientCases WHERE ClientCase_ID = " + strippedClientCaseString;

FORTUNEConnection2.Open();
SqlCommand sc2Command = new SqlCommand(sql, FORTUNEConnection2);
               
try
{
   IDataReader rdr = sc2Command.ExecuteReader();
   while (rdr.Read())
   {
      if (reader[0] != DBNull.Value)
         name = (string)reader[0];
      if (reader[1] != DBNull.Value)
         tel = (string)reader[1];
      if (reader[2] != DBNull.Value)
         emailAddress = (string)reader[2];
   }
}
catch
{
   // Do something meaningful if it fails.
}
finally  // So we do not have leaks
{
   rdr.Close();
   rdr.Dispose();
   this.CloseConnection();
}

Jim
0
 

Author Comment

by:amillyard
ID: 20105504
@Jim

thank you very much for your contribution.

some feedback is that I am not yet getting a successful compile, mainly the 'reader' does not exist in current context

and, 'rdr -- same error message.

and finally, and error as follows:  does not contain a definiton for 'CloseConnection'

I have made a few changes to script as follows:   (just trying to pull the first data across, then will unlock the other 2 once the script compiles ok.  can the variable 'name' in the SELECT statement, be named anything, or does it have to match the column name in the sql db table?  do I need to place each column table name in square brackets also?  [name] etc.




string name = string.Empty;
            string tel = string.Empty;
            string emailAddress = string.Empty;
            string sql = "SELECT name FROM ClientCases WHERE ClientCase_ID = 123";
           
            SqlDataSource FORTUNESQLDataSource = new SqlDataSource();
            FORTUNESQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["FORTUNEConnectionString"].ToString();

            using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                FORTUNEConnection1.Open();
                SqlCommand sc1Command = new SqlCommand(sql, FORTUNEConnection1);

                try
                {
                    IDataReader rdr = sc1Command.ExecuteReader();
                    while (rdr.Read())
                    {
                        if (reader[0] != DBNull.Value)
                            name = (string)reader[0];
                        //if (reader[1] != DBNull.Value)
                        //    tel = (string)reader[1];
                        //if (reader[2] != DBNull.Value)
                        //    emailAddress = (string)reader[2];
                    }
                }
                catch
                {
                    // Do something meaningful if it fails.
                }
                finally  // So we do not have leaks
                {
                    rdr.Close();
                    rdr.Dispose();
                    this.CloseConnection();
                }
            }
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20105752
I had two problems - sorry about that.
1. Need to declare the DataReader outside the try block, and 2. I changed the name from rdr to reader.
               IDataReader rdr = null;
               try
                {
                    rdr = sc1Command.ExecuteReader();
                    while (rdr.Read())
                    {
                        if (rdr[0] != DBNull.Value)
                            name = (string)rdr[0];
                        //if (rdr[1] != DBNull.Value)
                        //    tel = (string)rdr[1];
                        //if (rdr[2] != DBNull.Value)
                        //    emailAddress = (string)rdr[2];
                    }
                }
                catch
                {
                    // Do something meaningful if it fails.
                }
                finally  // So we do not have leaks
                {
                    rdr.Close();
                    rdr.Dispose();
                    this.CloseConnection();
                }

And, whatever is in the select needs to match the column names in the table. The [] around column names are superfluous.

Jim
0
 
LVL 22

Accepted Solution

by:
JimBrandley earned 2000 total points
ID: 20105760
I missed the last one - CloseConnection() is in my data layer. Since you have the connection in the using statement, you should not have to close it.

Jim
0
 

Author Comment

by:amillyard
ID: 20119259
@JimBrandley

The above adjusted code is compiling fine -- just am getting this error message when running the asp as follows:

NullReferenceException was unhandled by user code
object reference not set to an instance of an object

compiler is placing a marker at this line :: rdr.Close();
0
 

Author Comment

by:amillyard
ID: 20119294
@JimBrandley

I added ::   FORTUNEConnection1.Close();  and commented out the :

                    //rdr.Close();
                    //rdr.Dispose();

in order to remove the exception errors -- do I still need to have these in?  -- am I opening 2 connections here by accident perhaps?



string name = string.Empty;
            string tel = string.Empty;
            string emailAddress = string.Empty;
            string sql = "SELECT name FROM ClientCases WHERE ClientCase_ID = 777";
           
            SqlDataSource FORTUNESQLDataSource = new SqlDataSource();
            FORTUNESQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["FORTUNEConnectionString"].ToString();

            using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
            {
                FORTUNEConnection1.Open();
                SqlCommand sc1Command = new SqlCommand(sql, FORTUNEConnection1);

                IDataReader rdr = null;
                try
                {
                    rdr = sc1Command.ExecuteReader();
                    while (rdr.Read())
                    {
                        if (rdr[0] != DBNull.Value)
                            name = (string)rdr[0];
                        //if (rdr[1] != DBNull.Value)
                        //    tel = (string)rdr[1];
                        //if (rdr[2] != DBNull.Value)
                        //    emailAddress = (string)rdr[2];
                    }
                }
                catch (Exception ex)
                {
                    SQL_FailureHandler(ex);
                }
                finally  
                {
                    FORTUNEConnection1.Close();
                    //rdr.Close();
                    //rdr.Dispose();
                }

                Label_Name.Text = name;
                Label_Tel.Text = tel;
                Label_EmailAddress.Text = emailAddress;
            }
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20119399
That occurred because there was an exception thrown before the reader was instantiated. To find out why, set a breakpoint on this:
SQL_FailureHandler(ex);
When you hit it, quickwatch ex and see what the Message is.

To fix the null reference exception, change the finally block to this:
               finally  
                {
                    FORTUNEConnection1.Close();
                   if (rdr != null)
                  {
                      rdr.Close();
                      rdr.Dispose();
                  }
                }

Jim

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

829 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