Click here to Skip to main content
11,429,154 members (54,359 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET ms-sql-server
Hi guys! Can somebody help me please because I currently have this code for importing Excel files to SQL Server database which work perfectly:

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myTextBox.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        Dim expr As String = "SELECT * FROM [Sheet1$]"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader
        Dim SQLconn As New SqlConnection()
 
 ExcelConnection.Open()
 
                    SQLconn.ConnectionString = conString
                    SQLconn.Open()
 
                    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
                        bulkCopy.DestinationTableName = "myTable"
 
                        Try
                            objDR = objCmdSelect.ExecuteReader
                            bulkCopy.WriteToServer(objDR)
                            objDR.Close()
 
                            SQLconn.Close()
 
                            MsgBox("Uploaded.", MsgBoxStyle.Information)
 
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
 
                    End Using

The output table in SQL database will be:

EMP. NO. / NAME / DEPT.
1234 / John Adams / NULL
4567 / Ryan King / NULL

Since in the uploaded table the DEPT. is NULL, what I need to do now is to import an Excel File with 2 columns only (EMP. NO. / DEPT.) where the existing EMP. NO. is defined already (1234), and the DEPT. will be inputted (say Accounting) which will be inserted in the table which MATCHES the employee numbers.

EMP. NO. / NAME / DEPT.
1234 / John Adams / Accounting
4567 / Ryan King / NULL

Can somebody help me how to revise my code for this? Thanks very much in advance! Smile | :)
Posted 22-Jan-13 23:38pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Steps to do:
1) get the data from Excel into DataTable object (SELECT * FROM [Sheet1$] WHERE NOT [Dept.] IS NULL).
2) update data (you've got 2 ways)
a) (NOT recommended) go through the collection of DataTable.Rows and build command-string to update your data, for example:
"UPDATE [YourTable] SET [Dept] = '" & DataTable.Rows(i).Item("Dept.").Value.ToString() & "'" & vbCr & _
"WHERE [Emp No.] = " & DataTable.Rows(i).Item("Emp No.").Value.ToString
b) (recomended) create a stored procedure to update data, using Dept. and Emp No. as an input parameters, for example:
CREATE PROCEDURE UpdateDept
    @DeptName NVARCHAR(30),
    @EmpNo INT
AS
    UPDATE [YourTable] SET [Dept.] = @DeptName
    WHERE [Emp No.] = @EmpNo
GO
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150428.2 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100