.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> ADO.Net >> Post New QuestionBookmark and Share Subscribe to Forum

Dynamically Insert single record into MS access from SQL server

Posted By: run fast     Posted Date: July 29, 2012    Points:5   Category :ADO.Net

Hi ALL,

I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database).

My goal : when user enters a ClientId and click insert button then I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database.

Achieved: i retrived from all tables in sql server databases with client id and stored the data in Dataset.

Problem: Insert into Access tables????

i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically.

Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs. Its not a bulk insert, its single record push into multiple tables.

-------This is My code-------------------------------------------------------

private void InsertMsiClientIntoTest(string ClientId)
{
SqlConnection sqlConnection = null;
SqlDataAdapter sqlDataAdapter = null;
DataSet sqlserverDataset = new DataSet();
sqlserverDataset.Tables.Add();
sqlConnection = new SqlConnection();
sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
sqlConnection.Open();
sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection);
sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text);
sqlDataAdapter.Fill(sqlserverDataset);
GetDataFromTablesForID(sqlserverDataset);
InsertAllTableDataIntoAccess(sqlserverDataset,tableArray);
}


private DataSet GetDataFromTablesForID(DataSet dsTablesList)
{
SqlConnection sqlConnection = null;
SqlDataAdapter sqlDataAdapter = null;
string tableName = string.Empty;
string QueryText = string.Empty;
int i =0;
tableArray = new string[dsTablesList.Tables[0].Rows.Count];
DataSet sqlserverDataset = new DataSet();
sqlserverDataset.Tables.Add();
sqlConnection = new SqlConnection();
sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
sqlConnection.Open();
foreach (DataRow itemRow in dsTablesList.Tables[0].Rows)
{
tableArray[i] = itemRow[0].ToString();
i++;
}
foreach (string tableItem in tableArray)
{
tableName = tableItem;

QueryText = "select x.* from" + " " + tableName + " " +
"x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId";
}
sqlDataAdapter = new SqlDataAdapter(QueryText, sqlConnection);
sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbExceedId.Text);
sqlDataAdapter.Fill(sqlserverDataset);
}

return sqlserverDataset;

}

private void InsertAllTableDataIntoAccess(DataSet Inputset, string[] tableArray)
{
string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["mdb"].ToString();
OleDbConnection oledbConnection = null;
OleDbDataAdapter oledbDataAdapter = null;
DataSet resultSet = new DataSet();
oledbConnection = new OleDbConnection(connectionstring);
oledbConnection.Open();
foreach (string tableItem in tableArray)
{
//????????? I NEED THE FOLLOWING CODE......////////////???????
oledbDataAdapter.InsertCommand.CommandText="insert into "
oledbDataAdapter.Fill(resultSet.Tables[0]);
}
}

Thanks in Advance...

Responses

No response found. Be the first to respond this post
Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend