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

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

Bob

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?

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.

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?

@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;

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

@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();
                }
            }

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

@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();

@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;
            }

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