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:
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 Jesup on Nov 30, 2011 03:09 PM
Jesup
Member
19 Points
10 Posts
save from MS Access to SQL Server
Nov 27, 2011 07:19 PM|LINK
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);
}
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: save from MS Access to SQL Server
Nov 29, 2011 12:51 AM|LINK
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。
Jesup
Member
19 Points
10 Posts
Re: save from MS Access to SQL Server
Nov 30, 2011 03:29 PM|LINK
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>)