C# DataTable SQL query not inserting data

Question

I am relatively new to SQL and this is my first time trying to use it in C# and can't understand why this isn't working, I know the Column I am trying to copy across does have values in it but when I try and display the 'test' DataTable in a DataGridViewer there are no rows at all

The data is from a database on a server and displays fine when setting the DataGridViewer.DataSource to thisDataSet.USERS

This is my code:

        DataTable test = new DataTable();
        test.Columns.Add("UserID");

        SqlCommand command = new SqlCommand();
        command.CommandText = "INSERT INTO test (UserID) SELECT USER_ID FROM thisDataSet.USERS";
        command.ExecuteNonQuery();

        dgvDataViewer.DataSource = test;

I think you're expecting SQL commands to act on local C# variables, which they don't - they work on server data. Do you have a SQL database somewhere or are you just trying to use SQL syntax? — D Stanley
– D Stanley, Commented Feb 27, 2014 at 13:58
and i want to insert the user_id from a database on a server to the 'test' datatable — bolt19
– bolt19, Commented Feb 27, 2014 at 14:02
first of all you are missing connection string to your database. And also sqlcommand does not know about test datatable. — sunk
– sunk, Commented Feb 27, 2014 at 14:07

Tsukasa · Accepted Answer · 2014-02-27 14:14:40Z

Looks like you are forgetting a few things or didn't supply all your code. If you can supply more code I can likely give you a better answer. I'm guessing you don't actually have a database called thisDataSet and a table called Users? You might but just an odd way to name it if you do.

Also would like to point out that i'm not closing my DataAdapter or SQL connection because that are IDisposable and contained in a using statement so it's done for you.

DataTable test = new DataTable();
        using (SqlConnection conn = new SqlConnection("CONN STRING HERE"))
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "do sql stuff";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "select * from test";
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(test);
                    dgvDataViewer.DataSource = test;
                }
            }
        }

sunk · Accepted Answer · 2014-02-27 14:22:56Z

0

I created a new method. You can simply call that method to populate grid

public DataTable GetData(SqlConnection Cn)
{
    try
    {
        DataTable dt = null;
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = Cn;
            cmd.CommandText = "select * from Table";
            using (var sda = new SqlDataAdapter(cmd))
            {
                dt  = new DataTable();
                sda.Fill(dt);
            }
        }
        return contNames;
    }
    catch (Exception err)
    {
        return null;
    }
}

And populate your datagrid:-

 var con = new SqlConnection("connectionstring");
dataGridView1.DataSource = GetData(connection);

edited Feb 27, 2014 at 14:22

answered Feb 27, 2014 at 14:17

sunk

3201 gold badge3 silver badges9 bronze badges

Comments

ovaltein · Accepted Answer · 2014-02-27 14:12:32Z

0

Try something like this. You forgot a few things from your code - connection string, opening connection, etc...

SqlDataReader Reader = null;
SqlConnection Connection = null;
SqlCommand Command = null;
string ConnectionString = "YourConnectionString";
string CommandText = "SELECT USER_ID  AS [UserID] FROM MySQLtable";

Connection = new SqlConnection(ConnectionString);
Connection.Open();
Command = new SqlCommand(CommandText, Connection);
Reader = Command.ExecuteReader();

DataTable test= new DataTable();
test.Load(Reader);

Reader.Close();
Command.Dispose();
Connection.Close();

answered Feb 27, 2014 at 14:12

ovaltein

1,2252 gold badges13 silver badges35 bronze badges

Collectives™ on Stack Overflow

C# DataTable SQL query not inserting data

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related