locked
save from MS Access to SQL Server RRS feed

  • Question

  • User1065460264 posted

    I need a recommendation on how to save MS Access data to SQL Server.

    I am creating a process to load data from a query in an mdb file:

      1. display in a dataGridView (successful)

      2. allow the user to create an output csv file (successful)

      3. save the data to a SQL Server table and add extra columns for future QC work (not successful, this is where I need help)

    Question - How to save the dataGridView or dataset and add extra columns, like the current datetime, into SQL Server?  

    I get some limited success trying to use OPENROWSET('Microsoft.Jet.OLEDB.4.0'...  but can't seem to define the location of the mdb file as being on the local workstation and not on the server where SQL Server resides.

    This is what works for dataGridView and creating the csv file:

            private void Form1_Load(object sender, EventArgs e)

            {

                this.ResultTableAdapter.Fill(this.dataSetOutputMDB.Result);

                string w = dataGridViewResults.Rows[0].Cells[17].Value.ToString();

                workorder = w.Remove(7, 3);

                setdate = DateTime.Now;

     

            }

            private void buttonTransfer_Click(object sender, EventArgs e)

            {

                CreateResultsCSV();

                Application.Exit();

            }

            private void CreateResultsCSV()

            {

                string resultsPath = @"\\Server1\Transfer\" + wrk + ".csv";

                TextWriter tw = new StreamWriter(resultsPath);

                for (int x = 0; x < dataGridViewResults.Columns.Count; x++)

                {

                    tw.Write(dataGridViewResults.Columns[x].HeaderText);

                    if (x != dataGridViewResults.Columns.Count - 1)

                    {

                        tw.Write(",");

                    }

                }

                tw.Write("\n");

                for (int x = 0; x < dataGridViewResults.Rows.Count - 1; x++)

                {

                    for (int y = 0; y < dataGridViewResults.Columns.Count; y++)

                    {

                        tw.Write(dataGridViewResults.Rows[x].Cells[y].Value.ToString());

                        if (y != dataGridViewResults.Columns.Count - 1)

                        {

                            tw.Write(",");

                        }

                    }

                    tw.WriteLine();

                }

                tw.Close();

                FilePath = resultsPath;

                MessageBox.Show("Files Saved To:  \n" + FilePath);

            }

     

    Sunday, November 27, 2011 3:19 PM

Answers

  • User3866881 posted

    Question - How to save the dataGridView or dataset and add extra columns, like the current datetime, into SQL Server?  

    Your question can be divided into two parts:

    1)Adding an extra column——That's easy, what you do is just call "DataTable.Columns.Add("ColumnName",typeof(ColumnName)) and assign values in the "for-each" body。

    2)Saving into db——It's an extra column, no real mapping columns in the db,So I'm afraid you cannot do that。

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2011 8:51 PM
  • User1065460264 posted

    Thanks for your recommendations.

    I was able to reach a solution in a new method that worked for me.

    Since the dataGridViewResults was loaded into memory from the Page Load, it was always available and I didn't need to refer to MS Access again.

    1. Created a stored procedure in the SQL database to insert rows.

    2. Added a Linq to SQL class

    3. Populated the context with the SQL table and a SQL Insert stored procedure

    4. Looped through each row of the dataGridViewResults

    Assigned variables for the values I wanted to add, that were not in dataGridViewResults

    Assigned variables for each cell value from dataGridViewResults

    Called the context.<insert stored procedure>(<listing all the parameters>)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 30, 2011 11:29 AM

All replies

  • User3866881 posted

    Question - How to save the dataGridView or dataset and add extra columns, like the current datetime, into SQL Server?  

    Your question can be divided into two parts:

    1)Adding an extra column——That's easy, what you do is just call "DataTable.Columns.Add("ColumnName",typeof(ColumnName)) and assign values in the "for-each" body。

    2)Saving into db——It's an extra column, no real mapping columns in the db,So I'm afraid you cannot do that。

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2011 8:51 PM
  • User1065460264 posted

    Thanks for your recommendations.

    I was able to reach a solution in a new method that worked for me.

    Since the dataGridViewResults was loaded into memory from the Page Load, it was always available and I didn't need to refer to MS Access again.

    1. Created a stored procedure in the SQL database to insert rows.

    2. Added a Linq to SQL class

    3. Populated the context with the SQL table and a SQL Insert stored procedure

    4. Looped through each row of the dataGridViewResults

    Assigned variables for the values I wanted to add, that were not in dataGridViewResults

    Assigned variables for each cell value from dataGridViewResults

    Called the context.<insert stored procedure>(<listing all the parameters>)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 30, 2011 11:29 AM