datatable won't load in data

Question

I'm trying to use the datable object to load the results of a sql query. Some queries work and others don't. I have put a trace on the database and I can see the right sql gets through but my aspx page doesn't run properly. As I'm using sharepoint 2010 to run the page the error message I get is very unhelpful. I suspect it is some kind of datatype the dataTable doesn't like (as I can't think what else it could be) but I don't know what it may be. Can anyone help please?

<%@ page language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>
<script runat="server">
//Create string objects


//Create a data table object
DataTable dataTable = new DataTable();

//On page load get the parameters from the URL and assign them to the objects
protected void page_load (Object s, EventArgs e)
    {

    }

//Create new class 
public class ParamDemo
    {

//Create new method to getData    
    public static DataTable GetData()
      {     
            // create connection and reader variables
            SqlConnection conn = null;
            SqlDataReader reader = null;

            //Create a data table object
            DataTable dataTable = new DataTable();
            //String cmdString = new String();
            try
            {

                  // instantiate and open connection
                  conn =  new SqlConnection("Server=myserver;Database=myDB;User Id=UserId;Password=Password");
                  conn.Open();
                  // 1. declare required command object
                  String cmdString = "";

                  cmdString = "SELECT TOP 1 DMA FROM [myDb].[dbo].[dev_table]";
                  SqlCommand cmd = new SqlCommand(
                  cmdString, conn);

                  //get data stream
                  reader = cmd.ExecuteReader();

                  //Record datastream in datatable
                  dataTable.Load(reader);

            }


            finally
            {
                  // close reader
                  if (reader != null)
                  {
                        reader.Close();
                  }

                  // close connection
                  if (conn != null)
                  {
                        conn.Close();
                  }
            }
            return dataTable;
      }
}

Answer 1

The reason you're not seeing any error is because you're swallowing the error

try
{
  // data access
}
finally
{
   // clean up
}

At very least change it to

try
{
  // data access
}
catch(Exception ex)
{
  // handle the error
}
finally
{
   // clean up
}

You can log the error in the catch block, but at very least you can stick a breakpoint there and investigate what is going wrong

Answer 2

Doh,

Excuse my noviceness. My query accessed a different db to which I hadn't provided access to UserId. Now I've given access it works fine.