How to populate Datatable with InnerJoin Sql data

Question

Im trying to populate the table with the following code. but i am not sure of the right way to do it.

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.KanbanDatabase))
            {
                DataTable dat = new DataTable();
                dat.Columns.AddRange(new DataColumn[13] { new DataColumn("O_Date"), new DataColumn("O_User"), new DataColumn("O_Material"), new DataColumn("O_Description"), new DataColumn("O_Sloc"), new DataColumn("O_Supplier"), new DataColumn("O_StandardPack"), new DataColumn("O_KanbanSize"), new DataColumn("O_Qty"), new DataColumn("Entry Date"), new DataColumn("PO"), new DataColumn("O_Urgency"), new DataColumn("Quantity") });
                string SqlQry = "SELECT Kanban_Order.[O_Date], Kanban_Order.[O_User], Kanban_Order.[O_Material], Kanban_Order.[O_Description], Kanban_Order.[O_Sloc], Kanban_Order.[O_Supplier], Kanban_Order.[O_StandardPack], Kanban_Order.[O_KanbanSize], Kanban_Order.[O_Qty], Kanban_GR101.[Entry Date], Kanban_GR101.[PO], Kanban_Order.[O_Urgency], Kanban_GR101.[Quantity]";
                SqlQry = SqlQry + "FROM Kanban_Order, Kanban_GR101";
                SqlQry = SqlQry + "WHERE Kanban_Order.[O_Material] = Kanban_GR101.[Material] AND O_Date BETWEEN @dateFrom AND  @dateTo";
                using (SqlCommand cmd = new SqlCommand(SqlQry, conn))
                {
                    cmd.Parameters.AddWithValue("@dateFrom", dateFrom);
                    cmd.Parameters.AddWithValue("@dateTo", dateTo);
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    conn.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds,dat);
                    Status1.DataSource = dat;
                    Status1.DataBind();

This code doesn't work. What else can i do?

What do you mean the code doesn't work? Can you be more specific as to what your case is? Are you encountering any errors? If yes, please let us know. — Rahul Sharma, Jun 20 '19 at 11:44
Your `Select` statement needs spaces between each line. If you put that string together end to end there would be no spacing. ie.. `" From` and `" Where`. Use `INNER JOIN` statement instead of the old style joins. — SS_DBA, Jun 20 '19 at 11:47
hope this link will help you. https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp — Sandy N, Jun 20 '19 at 11:49
No one should be [joining via the where clause](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). Don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) — SMor, Jun 20 '19 at 12:03

score 1 · Answer 1 · answered Jun 20 '19 at 12:38

try this way

private DataTable GetDataTable()
    {
        try
        {
            string strQuery = "Your SQL Query";
            string strConnectionString ="Your Connection String";
            using (SqlConnection Connection = new SqlConnection(strConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(strQuery))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = Connection;
                        da.SelectCommand = cmd;
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }
        catch (Exception ex)
        {

            throw ex;
        }

    }

USE of GetDataTable function

Datatable dt = GetDataTable();

JcMey3r · Answer 2 · 2019-06-20T12:33:41.367

Always have spaces in there. The best way to make sure it works is to debug and check the sql generated and run in SQL server to test if unsure. You will pick it up quickly.

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.KanbanDatabase))
            {
                DataTable dat = new DataTable();
                dat.Columns.AddRange(new DataColumn[13] { new DataColumn("O_Date"), new DataColumn("O_User"), new DataColumn("O_Material"), new DataColumn("O_Description"), new DataColumn("O_Sloc"), new DataColumn("O_Supplier"), new DataColumn("O_StandardPack"), new DataColumn("O_KanbanSize"), new DataColumn("O_Qty"), new DataColumn("Entry Date"), new DataColumn("PO"), new DataColumn("O_Urgency"), new DataColumn("Quantity") });
                StringBuilder _sqlQuery = new StringBuilder("SELECT Kanban_Order.[O_Date], Kanban_Order.[O_User], Kanban_Order.[O_Material], Kanban_Order.[O_Description], Kanban_Order.[O_Sloc], Kanban_Order.[O_Supplier], Kanban_Order.[O_StandardPack], Kanban_Order.[O_KanbanSize], Kanban_Order.[O_Qty], Kanban_GR101.[Entry Date], Kanban_GR101.[PO], Kanban_Order.[O_Urgency], Kanban_GR101.[Quantity]");
                _sqlQuery.Append(" FROM Kanban_Order, Kanban_GR101");
                _sqlQuery.Append($" WHERE Kanban_Order.[O_Material] = Kanban_GR101.[Material] AND O_Date BETWEEN '{dateFrom}' AND  '{dateTo}'");
                using (SqlCommand cmd = new SqlCommand(_sqlQuery.ToString() , conn))
                {
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    conn.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds,dat);
                    Status1.DataSource = dat;
                    Status1.DataBind();

`da.Fill(ds,dat);` Argument 2: cannot convert from 'System.Data.DataTable' to 'string' — Harvint Raj, Jun 21 '19 at 00:15

How to populate Datatable with InnerJoin Sql data

2 Answers2