[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

C# - cannot get data from a database into a DataTable object

What is wrong with this code?  I have verified that the connection string is correct and that the sql I pass actually returns data because I am able to get data back from the database into an sqlDataReader.  I thought this data_table method of mine worked, but I'm not getting back any data in the datatable.

Can anybody see a problem with the way I have this written?


public static void Data_Table(string strSQL, ref DataTable dt)
        {
            String strConn = @"Data Source=ARIZONA\SQLEXPRESS;Initial Catalog=Finances;Integrated Security=True";
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
 
            try
            {
                conn = new SqlConnection(strConn);
                cmd = new SqlCommand(strSQL, conn);
                da = new SqlDataAdapter();
                dt = new DataTable();
                cmd.Connection.Open();
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Dispose();
            }
        }

Open in new window

0
josephdaviskcrm
Asked:
josephdaviskcrm
  • 5
  • 3
  • 3
  • +2
1 Solution
 
tiagosalgadoCommented:
Change the "public static void Data_Table(string strSQL, ref DataTable dt)" to "public static DataTable Data_Table(string strSQL, ref DataTable dt)" and add a "return dt" after "da.Fill(dt);".

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how do you use the function?
you have a datatable, and not datareader in there.
0
 
nkhelashviliCommented:
But where are you checking if the datatable is filled?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
josephdaviskcrmAuthor Commented:
There is a DataTable object passed into the method in the parameter list.  The problem exists inside the method itself.  I've been stepping through the code and when I run da.Fill(dt) I'm not getting any data inside the dt object.
0
 
josephdaviskcrmAuthor Commented:
Here is an example of how I am calling the method...

dt = new DataTable();
            Data_Table("SELECT Date, Category, SUM(Amount) AS Amount FROM (SELECT  CAST(MONTH(DATE) AS VARCHAR) + '-' + RIGHT(CAST(YEAR(DATE) AS VARCHAR), 2) AS [Date], Amount, Category FROM Transactions WITH(NOLOCK) WHERE Account IN ('Spending') AND Category IS NOT NULL) a GROUP BY Date, Category ORDER BY Date, Category", ref dt);
0
 
nkhelashviliCommented:
Run sql profiler to check which select statement is executing
0
 
josephdaviskcrmAuthor Commented:
So the DataTable exists outside of the method and is passed in by reference.  So if the DataTable gets filled with Data I will have the data outside of the method also.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>There is a DataTable object passed into the method

if that is really the case, remove this line from the procedure:
dt = new DataTable();
0
 
josephdaviskcrmAuthor Commented:
what is sql profiler?
0
 
josephdaviskcrmAuthor Commented:
>There is a DataTable object passed into the method

>if that is really the case, remove this line from the procedure:
>dt = new DataTable();

I added that line after it wasn't working to see if it would fix anything, but of course it didn't.  I've taken it out now.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
again, how do you CALL the procedure, please?

also, note that your function is eating up any exception, so you won't see if there is an error occurring!!

please check if your function code actually runs into an error.
0
 
nkhelashviliCommented:
>  also, note that your function is eating up any exception

yes,  add the line in the catch block



MessageBox.Show(ex.Message.ToString());

Open in new window

0
 
sm394Commented:
Hi guys
below is a tested and working example
you can tweak according to your needs ie. passing query as param

usage is
DataTable dt = GetDataTable();
 public static DataTable GetDataTable()
        {
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from applications where id=@ID";
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 8)).Value = 2;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            try
            {
                con.Open();
                sda.Fill(dt);
 
            }
            catch (SqlException err)
            {
               throw new ApplicationException("Data error." + err);
            }
            finally
            {
                con.Close();
                con.Dispose();
                
            }
            return dt;
 
        }

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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