L
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
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