locked
Pour data to mdf of sqldatasource from accessdatasource - how ? RRS feed

  • Question

  • User-775831949 posted

    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

     

    Wednesday, December 5, 2012 11:12 PM

Answers

  • User-821857111 posted

    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();
     }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 6, 2012 8:53 AM
  • User3866881 posted

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 6, 2012 9:37 PM
  • User3866881 posted

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

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 8, 2012 11:33 PM
  • User3866881 posted

    Hello again,

    If your For cannot be entered, please check whether your Rows has really rows.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 9, 2012 12:07 AM
  • User-775831949 posted

    Thanks so much Decker I checked there is data in accessdatasource. Ok I changed that line to

     For i As Integer = 0 To myDataView.Table.Rows.Count - 1

    and it is working now, thanks a lot to both of you

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 9, 2012 12:25 AM

All replies

  • User-821857111 posted

    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.

    Thursday, December 6, 2012 1:04 AM
  • User-775831949 posted

    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.

     

    Thursday, December 6, 2012 2:07 AM
  • User-775831949 posted

    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 

    Thursday, December 6, 2012 5:02 AM
  • User-821857111 posted

    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();
     }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 6, 2012 8:53 AM
  • User3866881 posted

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 6, 2012 9:37 PM
  • User-775831949 posted

    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

     

    Saturday, December 8, 2012 11:17 PM
  • User3866881 posted

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

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 8, 2012 11:33 PM
  • User-775831949 posted

    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

    Saturday, December 8, 2012 11:52 PM
  • User3866881 posted

    Hello again,

    If your For cannot be entered, please check whether your Rows has really rows.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 9, 2012 12:07 AM
  • User-775831949 posted

    Thanks so much Decker I checked there is data in accessdatasource. Ok I changed that line to

     For i As Integer = 0 To myDataView.Table.Rows.Count - 1

    and it is working now, thanks a lot to both of you

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 9, 2012 12:25 AM