CODEWITHSUNDEEP
c#sqlsql-serverdatatable
iakovl2
iakovl2

Reputation: 111

Fill DataTable from SQL Server database

This one is a mystery for me, I know the code I took it from others, in my case the datatable it returns is empty

conSTR is the connection string, set as a global string

public DataTable fillDataTable(string table)
    {
        string query = "SELECT * FROM dstut.dbo." +table;

        SqlConnection sqlConn = new SqlConnection(conSTR);
        sqlConn.Open();
        SqlCommand cmd = new SqlCommand(query, sqlConn);

        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        sqlConn.Close();
        return dt;
    }

EDIT 1
The whole point is to later show this table in a datagrid view on a tabcontrol, here is the question on that displaying multiple datatable in tabcontrol C#

Here it just show's me a blank datagridview

EDIT 2
Tried them all, when I try to display the table, the datagridview is empty, have the right amount of rows but now value

Upvotes: 9

Views: 137474

Answers (2)

Freelancer
Freelancer

Reputation: 9074

Try with following:

public DataTable fillDataTable(string table)
    {
        string query = "SELECT * FROM dstut.dbo." +table;

        SqlConnection sqlConn = new SqlConnection(conSTR);
        sqlConn.Open();
        SqlCommand cmd = new SqlCommand(query, sqlConn);
        SqlDataAdapter da=new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        sqlConn.Close();
        return dt;
    }

Hope it is helpful.

Upvotes: 3

Steve
Steve

Reputation: 216342

If the variable table contains invalid characters (like a space) you should add square brackets around the variable. 

public DataTable fillDataTable(string table)
{
    string query = "SELECT * FROM dstut.dbo.[" + table + "]";

    using(SqlConnection sqlConn = new SqlConnection(conSTR))
    using(SqlCommand cmd = new SqlCommand(query, sqlConn))
    {
        sqlConn.Open();
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        return dt;
    }
}

By the way, be very careful with this kind of code because is open to Sql Injection. I hope for you that the table name doesn't come from user input

Upvotes: 33

Related Questions