hi! im not sure if im posting at the right forum but here is my problem..

in my program i need to insert multiple columns to a single row.. the multiple column is in a different database (remote) and the single column is in my local. how could i do this?

thanks!

where are you inserting these columns? in a datatable? Datagridview?

for what you are describing, you would need to fill a dataset using the DataAdapter (SQL or OleDb) with the records, as it retrieves everything (All records and its columns) and then insert your data into that/modify it then finally update the database using the Update(theDataSet) of te DataAdapter

you need to explain more so we can help you :-)

here is the full background to my problem....

i have two database.. SQL2005 and Oracle. The program will get from oracle the grades of the student and insert it into a table in sql. i would like to insert it into a datatable. my main problem is in oracle the results will be in columns and it would be inserted in a row in SQL. for example the student has 5 subjects, in oracle that would be seen as 5 rows it then would be inserted to sql in a single row. it would depend on the student id.

i only could insert one result because when i try to insert the next result it ends up in the next row..

hope you could help me.. thanks!

you need to pretty much retrieve all the data/columns from the first database. Then you need to generate an INSERT statement into SQL for example (your destination database). These syntax below will work on SQL:

INSERT INTO tableName (field1, field2) VALUES (@p1, @p2)

of course replace the tablename/fieldnames etc... and add the number of fields to add data for 1 record appropriately. Currently it only has 2 fields.

Here is an example of inserting data into a database in SQL using 2 fields with given values. Be sure to import the System.Data.SqlClient namespace:

SqlCommand theSQLCommand = new SqlCommand("INSERT INTO tableName (field1, field2) VALUES (@p1, @p2)"); theSqlCommand.Connection = new SqlConnection(connectionString);   SqlParameter p1 = new SqlParameter("@p1", SqlDbType.Int); p1.Value = "1"; SqlParameter p2 = new SqlParameter("@p2", SqlDbType.NVarChar, 50); p2.Value = "hello";   theSqlCommand.Parameters.Add(p1); theSqlCommand.Parameters.Add(p2);   theSqlCommand.Connection.Open(); theSqlCommand.ExecuteNonQuery(); theSqlCommand.Connection.Close();

this simply creates 2 parameters, an Insert statement to insert the values for 1 record and executes the command. Its best practice to use Stored Procedures for Sql since its safer and securer but this is something for another day for you to learn perhaps, the above was a simple quick example.

As for creating a dataTable for example, similar thing would apply EXCEPT you need to again, get the columns you need, their values, and create a datatable with the amount of columns you like:

DataTable theDataTable = new DataTable();

theDataTable.Columns.Add("columnName");

theDataTable.Columns.Add("columnName2");

//Add a record:

object[] theRecord = new object[] {"Field1Value", "Field2Value"};

theDataTable.Rows.Add(theRecord);

this will create the columns in the datatable, and add a record of data, with the column values. You can also "strongly type" the datatable, meaning, each column must be of a specific data type, like string, or int etc... Example:

theDataTable.Columns.Add("columnName", System.Type.GetType("System.String"));

Does this help at all?

the insert into etc... is for SQL. The DataTable has no relevance between SQL and oracle... its just an in memory storage to store your data from one database perhaps into the datatable.

I have never used oracle but can give you some logic in what to do to retrieve records and insert into SQL. Basically you stated that (please correct me)oracle gets a record information in row(view) correct? So what you have to do is to retrieve those "rows" as 1 record, perhaps with the aid of maybe a dataAdapter some datareader for oracle.

I may move this thread to the appropriate forum if you need to know about how to retrieve data from oracle then apply my solution once you have retrieved data, and once the solution I have given has been applied, you will be inserting the record(s) into SQL

I am actually new to oracle myself... its okay if you will move this thread.. thanks for the help! i really appreciated it!