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:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Local Data to TableAdapter to DataTable.
Generate Query
Execute query to Another database.
ASKER
Hi,
for the datareader approach, can I just declare a sqldatareader, open a new connection, then just loop through the reader such as this?
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)
}
??
Yes you can do that thing too . You are using SQL Server or MySQL ? which one ?
ASKER
Both.
MSSQL offline, MySQL online....
Cheers.
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.
as i said before just populate your datareader and add use it to populate your datatable in dataset or dataadapter.
it will work.
ASKER
Yes, cannot get this to work though.
How do I check the results via datagrid?
i.e. myDataGrid.itemsource = tableabdapter_fill_method? ?
How do I check the results via datagrid?
i.e. myDataGrid.itemsource = tableabdapter_fill_method?
ASKER
dont worry, got it sorted.
ASKER
select idnum, name, newdate from mssql_table where newdate > @olddate
then upload the datagrid into the mysql table.
Thoughts?