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

[Luis Esteban Valencia]

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

 

[Carl Prothman [MVP]]

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

'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