asked on

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

tiagosalgado

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);".

Guy Hengel [angelIII / a3]

how do you use the function?
you have a datatable, and not datareader in there.

nkhelashvili

But where are you checking if the datatable is filled?

josephdaviskcrm

ASKER

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.

josephdaviskcrm

ASKER

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

nkhelashvili

Run sql profiler to check which select statement is executing

josephdaviskcrm

ASKER

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.

Guy Hengel [angelIII / a3]

>There is a DataTable object passed into the method

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

josephdaviskcrm

ASKER

what is sql profiler?

josephdaviskcrm

ASKER

>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.

Guy Hengel [angelIII / a3]

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.

nkhelashvili

> 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

ASKER CERTIFIED SOLUTION

Faizan Sarwar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial