Link to home
Start Free TrialLog in
Avatar of south_paw
south_paw

asked on

Inserting into table using tableadapter

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

Avatar of south_paw
south_paw

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of miketayo
miketayo
Flag of Nigeria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.  
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.


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

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

MSSQL offline, MySQL online....

Cheers.
Hi,

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

it will work.
Yes, cannot get this to work though.

How do I check the results via datagrid?

i.e. myDataGrid.itemsource = tableabdapter_fill_method??
dont worry, got it sorted.