RE: Novice: How to modify rows (not add) using a script component

Hi Sharkir,
Thanks for your response.

Because of a bulk update, I recommend that you use a select command to
query the data source and fill the data into a dataset or datatable and
then loop fetch each row from the datatable and then use a update command
to update the row to the destination. I can give you some general
suggestions, but please understand that I'm not an expert on ADO.NET.
Here is a simple example (C#):
DataSet ds = new DataSet();
SqlDataAdapter adpSrc = new SqlDataAdapter();
adpSrc.SelectCommand = new SqlCommand("select * from
...",connection_source);
adpSrc.Fill(ds);

SqlDataAdapter adpDest = new SqlDataAdapter();
SqlCommand updateCmd = new SqlCommand("update ... set field1=@field1,
field2=@field2 where ID=@ID",connection_destination);
adpDest.UpdateCommand = updateCmd;
updateCmd.Parameters.Add("field1","SqlDbType.varchar",20,"@field1");
updateCmd.Parameters.Add("field2","SqlDbType.int",8,"@field2");
updateCmd.Parameters.Add("ID","SqlDbType.Int",8,"@ID");

If(ds.Tables.Count>0)
{
ds.Tables[0].Name = "database table name";
adpDest.Update(ds.Tables[0]);
}

In fact, there are also many other ways to access SQL Server by ADO.NET.
You can also loop update the rows by the query result set. I recommend that
you borrow a book regarding ADO.NET. It will lead you to master this
technic quickly.


Charles Wang
Microsoft Online Community Support


.

Relevant Pages

  • Parameter Queries
    ... There is a form called Report Date Range. ... table query to create a table called Merge Data (this table will be ... used as a data source for a merge letter) based on a date range. ... The problem is when I click this command button the default ...
    (microsoft.public.access.queries)
  • RE: Send a record from a Form to a Table.
    ... Use a query as the reports record source ... records to False for the next report run. ... append query from the form with a command button that would add the current ... recurd to a table that would be the data source for the report. ...
    (microsoft.public.access.forms)
  • Re: Dialog Box Input Mask
    ... data source for a report that will utimately be emailed. ... If you are actually opening the query (not using the query as record ... Add a command button to the form ...
    (microsoft.public.access.queries)
  • No data in query
    ... I want to open a form that has a query as it's data source, but if no data, ... a command button should show. ... Craig ...
    (microsoft.public.access.forms)
  • Re: Counting Concurrent timed events
    ... When you make a command button, Access gives it an ambiguous name like "Command2" ... ... then compile your code ... Exit Sub ... what you need to be able to do is replace the SQL using a different date before you run the query that uses it. ...
    (microsoft.public.access.queries)