Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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...

share|improve this question
    
Look here: forums.asp.net/t/1481028.aspx/1 –  Amiram Korach Jul 29 '12 at 22:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.