CODEWITHSUNDEEP
c#sqlinsertsql-update
lemunk
lemunk

Reputation: 2636

Update sql table from datatable

Using C# .net 4.0 and visual studio 2010.

I need to update a table on my server with a table that is generated in my code.

I'm having difficulty figuring out what I need to do.

Do I need to update or do I need to create a temp table with and insert? or something totally different.

public void SaveMyWorkI()
{
    DataGridViewRowCollection coll = ParetoGrid.Rows;

    saveTable.Columns.Add("Part", typeof(string));
    saveTable.Columns.Add("Pareto", typeof(string));

    foreach (DataGridViewRow item in coll)
    {
        saveTable.Rows.Add(item.Cells[2].Value, item.Cells[5].Value);
        //saveTable.Rows.Add(item.Cells[5].Value);
    }

    MyErrorGrid.DataSource = saveTable;

    //SqlCommand pgINSERT = new SqlCommand("INSERT or UPDATE", conn);

}

The update would inner join on part.

As you can see I just did this and now my mind has gone blank. Not only that but this is the last major part to the small program.

Here's what i have so far:

foreach (DataGridViewRow item in coll)
        {
            saveTable.Rows.Add(item.Cells[2].Value, item.Cells[5].Value);

            part = item.Cells[2].Value.ToString();
            pareto = item.Cells[5].Value.ToString();

            SqlCommand pgINSERT = new SqlCommand("UPDATE dbo.OldParetoAnalysis SET part = " + part + " , pareto = " + pareto + "", conn);
            pgINSERT.ExecuteReader();
            //saveTable.Rows.Add(item.Cells[5].Value);
        }

Error I'm getting is "Invalid column name 'BK177'", BK177 is my data from my 1st row 1st column.

Upvotes: 0

Views: 3620

Answers (1)

Diego
Diego

Reputation: 36176

so basically you have a DataTable with data and you want to insert all its content on the DB?

You didn't say which version of SQL Server you are using but if you are using 2008, there is a table valued parameter type that you can use to send a whole table to a procedure for example. Then you could read this table on the proc and do whatever you need to do with it.

References: http://msdn.microsoft.com/en-us/library/bb510489.aspx

http://msdn.microsoft.com/en-us/library/bb675163.aspx

http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

Upvotes: 1

Related Questions