Pour data to mdf of sqldatasource from accessdatasource - how ? RSS

• • Reply
  

  hkbeer

  Member

  714 Points

  1477 Posts

  Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 06, 2012 03:12 AM|LINK

  I have a select database on aspx page which is accessdatasource.

  I want to pour these data from that into the mdf file on server and there is a related sqldatasource (ie insert statement) on the page already.

  What should be the code ? Pls help, thanks

   

  Thanks. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped.
  
  www.developerfusion.com/tools/convert/csharp-to-vb/
  • Edited by hkbeer on Dec 06, 2012 09:03 AM
• • Reply
  

  Mikesdotnett...

  All-Star

  154941 Points

  19870 Posts

  Moderator

  MVP

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 06, 2012 05:04 AM|LINK

  You can't transfer data from one datasource control to another. You should use the SqlDataSource control to get the data from Access in the first place.

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Edited by Mikesdotnetting on Dec 06, 2012 09:03 AM
• • Reply
  

  hkbeer

  Member

  714 Points

  1477 Posts

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 06, 2012 06:07 AM|LINK

  Thanks a lot

  "use the SqlDataSource control to get the data from Access "

  How to do this ? Pls kindly advise more details, thanks a lot indeed in advance.

   

  Thanks. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped.
  
  www.developerfusion.com/tools/convert/csharp-to-vb/
  • Edited by hkbeer on Dec 06, 2012 09:03 AM
• • Reply
  

  Mikesdotnett...

  All-Star

  154941 Points

  19870 Posts

  Moderator

  MVP

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 06, 2012 06:40 AM|LINK

  See this: http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Edited by Mikesdotnetting on Dec 06, 2012 09:03 AM
• • Reply
  

  hkbeer

  Member

  714 Points

  1477 Posts

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 06, 2012 09:02 AM|LINK

  Thanks a lot. I know how to connect and select record from access.mdb using sqldatasource but how can I insert those data into another sql mdf file is what I need to do, and I do not know for this part.... Thanks 

  Thanks. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped.
  
  www.developerfusion.com/tools/convert/csharp-to-vb/
  • Edited by hkbeer on Dec 06, 2012 09:03 AM
• • Reply
  

  Mikesdotnett...

  All-Star

  154941 Points

  19870 Posts

  Moderator

  MVP

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 06, 2012 12:53 PM|LINK

  Apologies. I misunderstood what you were trying to achieve.

  You can get access to the data that has been obtained via the AccessDataSource through its DataView object, but you have to do that in code-behind. Then you loop through each row in the DataView and insert it into the SQL Server database. You could use a SqlDataSource for that, but again, you would have to do it in code-behind.

  In your Page_Load event, you programmatically get the AccessDataSource to call its SelectCommand:

  DataView myDataView = (DataView)MyAccessDataSource.Select(DataSourceSelectArguments.Empty);

  Now you have all the data in the object called myDataView. You can get the data by looping through:

  for(int i = 0; i < myDataView.Rows.Count; i++){
      MySqlDataSource.InsertParameters("param1").DefaultValue = myDataView[i][0];
      MySqlDataSource.InsertParameters("param2").DefaultValue = myDataView[i][1];
      MySqlDataSource.InsertParameters("param3").DefaultValue = myDataView[i][2];
      //etc
      MySqlDataSource.Insert();
   }

  
  
  

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Marked as answer by hkbeer on Dec 07, 2012 02:37 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 07, 2012 01:37 AM|LINK

  hkbeer

  I know how to connect and select record from access.mdb using sqldatasource but how can I insert those data into another sql mdf file is what I need to do, and I do not know for this part.... Thanks 

  Hi again,

  In fact, besides what Mikesdotnettings's idea, I think you can also:

  Do the problem by reading all the data contents from Access, and then you can use SQLBulkCopy to copy them to the destination of your sql table, please notice that your SQL destination table should be the same destination as well as columns' types, structs……

  For more you can refer this:

  http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy

  • Marked as answer by hkbeer on Dec 07, 2012 02:37 AM
• • Reply
  

  hkbeer

  Member

  714 Points

  1477 Posts

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 09, 2012 03:17 AM|LINK

  I am first trying Mike 's solution which works great except I get error in this line:

  for(int i = 0; i < myDataView.Rows.Count; i++){ 

   

  I get: Rows is not a member of 'System.Data.DataView'

  What should I do ?

  Thanks

   

  Thanks. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped.
  
  www.developerfusion.com/tools/convert/csharp-to-vb/
  • Edited by hkbeer on Dec 09, 2012 03:18 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 09, 2012 03:33 AM|LINK

  hkbeer

  for(int i
  = 0; i
  < myDataView.Rows.Count; i++){

  This code looks right, what have you written next in "for"?

  • Marked as answer by hkbeer on Dec 09, 2012 05:43 AM
• • Reply
  

  hkbeer

  Member

  714 Points

  1477 Posts

  Re: Pour data to mdf of sqldatasource from accessdatasource - how ?

  Dec 09, 2012 03:52 AM|LINK

  My full code:

  <%@ Page Title="" Language="VB" MasterPageFile="~/Master.master" %>

  <%@ Import Namespace="System.Data" %>
  <script runat="server">

      Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
          Dim myDataView As DataView = DirectCast(AccessDataSource1.[Select](DataSourceSelectArguments.Empty), DataView)
          For i As Integer = 0 To myDataView.Rows.Count - 1
              SqlDataSource1.InsertParameters("Country").DefaultValue = myDataView(i)(0)
              SqlDataSource1.InsertParameters("Sales").DefaultValue = myDataView(i)(1)
             
              SqlDataSource1.Insert()
          Next
         
      End Sub

  Thanks. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped.
  
  www.developerfusion.com/tools/convert/csharp-to-vb/
  • Edited by hkbeer on Dec 09, 2012 03:52 AM
  • Edited by hkbeer on Dec 09, 2012 03:53 AM