• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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

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
amillyard
Asked:
amillyard
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Bob LearnedCommented:
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
 
divinewind80Commented:
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
 
amillyardAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
divinewind80Commented:
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
 
amillyardAuthor Commented:
@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
 
JimBrandleyCommented:
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
 
amillyardAuthor Commented:
@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
 
JimBrandleyCommented:
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
 
JimBrandleyCommented:
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
 
amillyardAuthor Commented:
@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
 
amillyardAuthor Commented:
@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
 
JimBrandleyCommented:
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
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now