0

How can I insert a columns data from my DataGrid into a sql table.

        private void btUpload_Click(object sender, RoutedEventArgs e)
    {

        // Configure open file dialog box 
        Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
        // Filter by Excel Worksheets
        dlg.Filter = "Excel Worksheets|*.xls";

        dlg.ShowDialog();

        // Show open file dialog box 
        Nullable<bool> result = dlg.ShowDialog();         

        // Process open file dialog box results 
        if (result == true)
        {

            // Create connection string variable. Modify the "Data Source"
            // parameter as appropriate for your environment.
            String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + dlg.FileName+ ";" +
                "Extended Properties=Excel 8.0;";               

            // Create connection object by using the preceding connection string.
            OleDbConnection objConn = new OleDbConnection(sConnectionString);

            // Open connection with the database.
            objConn.Open();

SQL SELECT command to display the data from the worksheet.

            // Create new OleDbCommand to return data from worksheet.
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [codereward$]", objConn);

            // Create new OleDbDataAdapter that is used to build a DataSet
            // based on the preceding SQL SELECT statement.
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

            // Pass the Select command to the adapter.
            objAdapter1.SelectCommand = objCmdSelect;

            // Create new DataSet to hold information from the worksheet.
            DataSet objDataset1 = new DataSet();

Fill the DataSet with the information from the worksheet.

            objAdapter1.Fill(objDataset1, "XLData");

            // Bind data to DataGrid control.
            dgCodeDisp.ItemsSource = objDataset1.Tables[0].DefaultView;

            // Clean up objects.
            objConn.Close();
        }

My Insert command, Here I want to insert into Code the data from code in DataGrid.

        sc.Open();
        cmd = new SqlCommand("Insert into RewardCodes (Code, value1, value2, ID) values('" + dgCodeDisp + "','" + ckv1.IsChecked.ToString() + "','" + ckv2.IsChecked.ToString() + "', '" + txtId.Text + "')", sc);
        cmd.ExecuteNonQuery();
    }
4
  • why don't you use sqlDataAdapter? Commented Oct 1, 2013 at 16:38
  • new to this, would you have a link to an example Commented Oct 1, 2013 at 16:39
  • sorry I did mislead you. here's an answer. Commented Oct 1, 2013 at 17:07
  • what's the problem are you facing in your code? Commented Oct 2, 2013 at 15:40

1 Answer 1

1

I think you are complicating things in your implementation. In the case you are working with SQL Server tables, Bulkcopy, is the adequate tool. it would do all the insert on your table. Here's a complete tutorial

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, but I only need one column from the excel and that is code. Bulkcopy works but copies all columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.