Inserting into table using tableadapter

south_paw
south_paw used Ask the Experts™
on
Hello,

I have a bit of a tricky one.  Using C#, I am trying to insert data into an online (MySQL) database where rows are > a certain date found in an offline MSSQL database.  I have connection to both databases in Visual Studio and can query both fine.

I have tried the below but get a fatal error on the last line:

String olddate;
olddate = myAdapter.query_to_get_olddate().ToString();
 
messagebox.show(olddate);  //returns the variable fine
 
//Then the query in the table adapter is as follows:
 
Insert into mysql_table (idnum, name, olddate) select idnum, name, newdate from mssql_table where newdate > @olddate
 
//then I run
 
myAdapter.aboveInsertQuery() // <--- here is where I get the fatal error

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I guess the other alternative is to fill a datagrid using:

select idnum, name, newdate from mssql_table where newdate > @olddate

then upload the datagrid into the mysql table.

Thoughts?
Commented:
hi

i will suggect that you use a datareader to the information from the offline database and then populate the the a datasource eg dataset already bind to the live db)

read the datareader and add the values to the datatable in the dataset

then close the datareader and disconnect the db

update the dataset and accept changes

i think this might work

Commented:
Insert into mysql_table (idnum, name, olddate) select idnum, name, newdate from mssql_table where newdate > @olddate


query is wrong . b'coz mysql_table is in select as well as insert statement . I understand you want to transfer offline record to online. but this is not query for that.  
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
For that you have to create Two Table Adapter . First one is from where you want to fetch data and second one is for where you want to upload data .?

Local Data  to  TableAdapter to DataTable.

Generate Query

Execute query to Another database.


Author

Commented:
Hi,

for the datareader approach, can I just declare a sqldatareader, open a new connection, then just loop through the reader such as this?


while (myReader.read())
{
        string first = (string)myReader["FirstName];
	string last = (string)myReader["LastName"];
	string idnum    = (string)myReader["IdNum"];
 
         myAdapter.aboveInsertQuery(first, last, idnum)
}
 
??

Open in new window

Commented:
Yes you can do that thing too .  You are using SQL Server or MySQL ? which one ?

Author

Commented:
Both.

MSSQL offline, MySQL online....

Cheers.

Commented:
Hi,

as i said before just populate your datareader and add use it to populate your datatable in dataset or dataadapter.

it will work.

Author

Commented:
Yes, cannot get this to work though.

How do I check the results via datagrid?

i.e. myDataGrid.itemsource = tableabdapter_fill_method??

Author

Commented:
dont worry, got it sorted.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial