exporting an excel file from database; making changes to excel file and updating the database by imp

Discussion in 'ASP .Net' started by Luis Esteban Valencia, Jan 11, 2005.

  1. Hello Everyone,

    Iam an intermediate ASP.Net programmer and iam facing a challenging task.

    I have a table in MS-SQL server database called 'Members'. The table has
    following fields...

    mem_id integer primary key
    lastname nvarchar(30)
    firstname nvarchar(30)
    class nvarchar(20)
    score integer

    At first, the table has some rows in it and the column 'score' has NULL
    values for all the rows. So, i export the table and save it as an Excel
    file. Then, i enter the data for column 'score' for all the rows in the
    excel file and import it back to the database so that it updates the actual
    table 'Members'.

    The problem iam facing is that my code is not really updating the table
    'Members' in the database, rather, it is appending the same rows to the
    table 'Members' with values for the column 'score'. So at the end, i have
    the top half rows in the table with null values for the column 'score' and
    the bottom half of the rows with the values that i put in the Excel file
    after i exported it and imported it back to the sever.
    Please help me out guys :-(

    'code for updating the database with the file that i import

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnImport.Click

    Dim i, j, n1 As Integer
    Dim dtTempTable As New DataTable
    Dim ds As New DataSet
    Dim myConnection As SqlConnection = GuestBookAppDB.Connection() '
    this user-defined function returns a sql conneciton
    Dim strSQL As String = "SELECT lastname, firstname, class, score
    FROM members"
    dtTempTable = readExcelSheet("C:\DataFiles\Members\DataFile.xls",
    "SELECT * FROM [Sheet1$]")
    Dim drTempRow As DataRow
    Dim myAdapter As New SqlDataAdapter(strSQL, myConnection)
    Dim myCB As New SqlCommandBuilder(myAdapter)
    myAdapter.SelectCommand.CommandType = CommandType.Text
    myCB.RefreshSchema()
    myAdapter.Fill(ds, "members")

    For Each drTempRow In dtTempTable.Rows()
    Dim dr As DataRow = ds.Tables("members").NewRow()
    dr("firstname") = drTempRow("firstname")
    dr("lastname") = drTempRow("lastname")
    dr("class") = drTempRow("class")
    dr("score") = drTempRow("score")
    ds.Tables("members").Rows.Add(dr)
    Next

    Dim ds1 As DataSet

    If ds.HasChanges() Then
    ds1 = ds.GetChanges()
    myAdapter.Update(ds1, "members")
    End If
    myConnection.Close()
    End Sub


    'code for reading the excel file and returning a datatable
    Public Function readExcelSheet(ByVal strExcelFileName As String, ByVal
    strQuery As String) As DataTable
    Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=""" & strExcelFileName & """;Extended Properties=""Excel
    8.0;HDR=YES;"""
    Dim Adapter As New OleDbDataAdapter(strQuery, strCon)
    Dim dt As New DataTable
    Adapter.Fill(dt)
    Adapter.Dispose()
    Return dt
    End Function

    --
    LUIS ESTEBAN VALENCIA
    MICROSOFT DCE 3.
    MIEMBRO ACTIVO DE ALIANZADEV
    http://spaces.msn.com/members/extremed/
     
    Luis Esteban Valencia, Jan 11, 2005
    #1
    1. Advertising

  2. [Follow-up set to microsoft.public.dotnet.framework.adonet]

    Luis Esteban Valencia wrote:
    > 'code for updating the database with the file that i import
    >


    > For Each drTempRow In dtTempTable.Rows()
    > Dim dr As DataRow = ds.Tables("members").NewRow()
    > dr("firstname") = drTempRow("firstname")
    > dr("lastname") = drTempRow("lastname")
    > dr("class") = drTempRow("class")
    > dr("score") = drTempRow("score")
    > ds.Tables("members").Rows.Add(dr)
    > Next
    >


    Don't use NewRow and Rows.Add unless you want to insert new rows.

    After getting the above result set, you want to find the corresponding row
    and update the score column. Then call the DataTable's Update method.
    Since the row was updated, the Update method will call the CommandUpdate,
    which you will need to create.
    http://msdn.microsoft.com/library/e...ientsqldataadapterclassupdatecommandtopic.asp

    --

    Thanks,
    Carl Prothman
    Microsoft ASP.NET MVP
     
    Carl Prothman [MVP], Jan 12, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?R2VpciBTYW5uZQ==?=

    Updating database with changes

    =?Utf-8?B?R2VpciBTYW5uZQ==?=, May 5, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    337
    Alec MacLean
    May 5, 2004
  2. news.rcn.com
    Replies:
    2
    Views:
    1,136
    Roedy Green
    Dec 10, 2007
  3. Doogie
    Replies:
    1
    Views:
    458
    sloan
    Nov 19, 2008
  4. garima puri
    Replies:
    0
    Views:
    89
    garima puri
    Sep 29, 2005
  5. garima puri
    Replies:
    6
    Views:
    241
    Erwin Moller
    Sep 30, 2005
Loading...

Share This Page