Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

read multiple fields from a db table.

Development platform: c#, asp.net 2.x, Visual Studio Pro utilsing Web Developer, IIS 6, SQL Server 2005

I am trying to read multiple fields from a db table.  checked the sql statement -- which is collecting a table ok ...but the variable value is not being transferred to: clientcase and masteraccount

your time and efforts with this enquiry are much apprieated.



            string clientcase = string.Empty;
            string masteraccount = string.Empty;
            string sql = "SELECT [ClientCase_ID], [MasterAccount_ID] FROM [ClientCases]";
           
            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)
                            clientcase = (string)rdr[0];
                        if (rdr[1] != DBNull.Value)
                            masteraccount = (string)rdr[1];
                    }
                }
                catch (Exception ex)
                {
                    SQL_FailureHandler(ex);
                }
                finally  
                {
                    FORTUNEConnection1.Close();
                    //rdr.Close();
                    //rdr.Dispose();
                }
            }
0
amillyard
Asked:
amillyard
  • 3
  • 3
1 Solution
 
JimBrandleyCommented:
Set breakpoints on these two lines:
if (rdr[0] != DBNull.Value)

and
SQL_FailureHandler(ex);

To see which you hit. If the first, quickwatch rdr to see what is returned. If the second, quickwatch ex to see what the Message property shows.

Jim
0
 
amillyardAuthor Commented:
rdr[0] returns a value 777  (which is correct figure -- checking the db table entry)
ok -- I think I know whats going on here now... there appears to be casting issue from int to string
will update the variable fields to int and see what happens.

while I am doing that -- could you kindly confirm that the above db connection is being closed ok -- just that the commented out text causes the asp page the stop working

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

many thanks
0
 
amillyardAuthor Commented:
ok, changing from string to int has made a difference and can see the db table record items is being collected successfully.

have added back in the following code:

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

which interestingly seems to be working also now.
0
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.

 
JimBrandleyCommented:
1. Since clientcase and masteraccount are strings, you can:
   clientcase = rdr[0].ToString();
   masteraccount = rdr[1].ToString();

2. Since you have the using statement creating the connection, it should close at the end of the block, but it is save to leave the explicit close in the finally block.

3. If you change the finally block to:
finally  
{
    FORTUNEConnection1.Close();
    if (rdr != null)
    {
         rdr.Close();
        rdr.Dispose();
    }
}

you should be safe. Those two statements should cause an exception only if there is an exception thrown before the reader is created.

Jim

0
 
amillyardAuthor Commented:
@JimBrandley

Thank you for speedy response and answer clarity.

Can confirm all is working 100%, thanks to your pointing me in the right direction and code assistance in order to achieve something tangible.

Much apprieated :-)
0
 
JimBrandleyCommented:
My pleasure. Good luck!

Jim
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now