将数据从Excel文件上传到vb.net win app中的SQL Server表 [英] Upload data from Excel file into SQL Server table in vb.net win app

问题描述

我想将数据从excel文件上传到SQL Server 2005现有表格。

我试过在以下方式，但它没有更新。



1.将数据从Excel文件读取到数据集。

2.从Sql Server读取数据表格到数据集。

3.合并两个数据集。

4.使用数据适配器更新。



当我尝试将合并的数据集分配给网格时，我无法看到更改，但是当我看到表格数据时，我无法看到更新。

版本信息：

SQL Server 2005

Visual Studio 2005 .NET Framework 2.0



以下是代码。

Hi,

I want to upload data from an excel file to SQL Server 2005 existing table.
I tried doing in the following way but it is not updating.

1. Read data from Excel file to a dataset.
2. Read data from Sql Server table to a dataset.
3. Merge both the datasets.
4. update using data adapter.

When I try to assign the merged dataset to a grid I am not able to see the changes but when I see the table data i am not able to see the updates.
Version Info:
SQL Server 2005
Visual Studio 2005 .NET Framework 2.0

The following is the code.

UploadForecastFile()

Private Sub UploadForecastFile()

       'read data from excel file
       ReadExcelFile()
       'read data from sql server table
       GetExistingForecast()
       'set composite primary key for the data table of sql server data
       dsForecast.Tables("Forecast").PrimaryKey = New DataColumn() {dsForecast.Tables(0).Columns("Customer"), dsForecast.Tables(0).Columns("Salesman"), dsForecast.Tables(0).Columns("Product"), dsForecast.Tables(0).Columns("Year")}
       dsExcelForecast.Tables(0).PrimaryKey = New DataColumn() {dsExcelForecast.Tables(0).Columns("Customer"), dsExcelForecast.Tables(0).Columns("Salesman"), dsExcelForecast.Tables(0).Columns("Product"), dsExcelForecast.Tables(0).Columns("Year")}
       'Merge excel data with sql server data
       dsFinal.Merge(dsForecast)
       dsFinal.AcceptChanges()
       dsFinal.Merge(dsExcelForecast)
       dsFinal.AcceptChanges()
       daMain.Update(dsFinal.Tables(0))
       daMain.AcceptChanges()
       MsgBox("Forecast updated")
       dgvModifiedForecast.DataSource = dsFinal

   End Sub
Private Sub ReadExcelFile()
        Dim strConnectionString As String
        Dim FileName As String
        FileName = txtFileName.Text
        strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & FileName & ";" & "Extended Properties=Excel 12.0;"
        Dim conn As OleDbConnection = New OleDbConnection(strConnectionString)
        Dim da As OleDbDataAdapter = New OleDbDataAdapter()
        Dim cmd As OleDbCommand = New OleDbCommand()
        cmd.Connection = conn
        cmd.CommandText = "SELECT * FROM [Sheet1$]"
        da.SelectCommand = cmd
        Try
            da.Fill(dsExcelForecast, "sheet1")
            Me.dgvModifiedForecast.DataSource = dsExcelForecast
            Me.dgvModifiedForecast.DataMember = "sheet1"
        Catch e1 As Exception
            MessageBox.Show(e1.Message)
            MessageBox.Show("Import Failed, correct Column name in the sheet!")
        End Try
    End Sub

 Private Sub GetExistingForecast()
        Dim conn As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand
        conn.Open()
        cmd.CommandText = "GetCompleteForecast"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = conn
        daMain = New SqlDataAdapter(cmd)
        daMain.Fill(dsForecast, "Forecast")
       

    End Sub

推荐答案

da.SelectCommand = cmd
尝试
da.Fill（dsExcelForecast， sheet1）
我 .dgvMo difiedForecast.DataSource = dsExcelForecast
Me .dgvModifiedForecast.DataMember = sheet1
Catch e1 As 异常
MessageBox.Show（e1.Message）
MessageBox.Show（ 导入失败，正确列表中的列名！）
结束 尝试
结束 Sub

私人 Sub GetExistingForecast（）
Dim conn As 新 SqlConnection（connectionString）
Dim cmd 作为 新 SqlCommand
conn.Open（）
cmd。 CommandText = GetCompleteForecast
cmd.CommandType = CommandType.StoredProcedure
cmd。 Connection = conn
daMain = 新 SqlDataAdapter（cmd）
daMain.Fill（dsForecast， 预测）


结束 Sub

" da.SelectCommand = cmd Try da.Fill(dsExcelForecast, "sheet1") Me.dgvModifiedForecast.DataSource = dsExcelForecast Me.dgvModifiedForecast.DataMember = "sheet1" Catch e1 As Exception MessageBox.Show(e1.Message) MessageBox.Show("Import Failed, correct Column name in the sheet!") End Try End Sub Private Sub GetExistingForecast() Dim conn As New SqlConnection(connectionString) Dim cmd As New SqlCommand conn.Open() cmd.CommandText = "GetCompleteForecast" cmd.CommandType = CommandType.StoredProcedure cmd.Connection = conn daMain = New SqlDataAdapter(cmd) daMain.Fill(dsForecast, "Forecast") End Sub

参考：

1. how-to-import-exel-file- to-sql-server-using-vb.net [ ^ ]

2. 读取和 - 导入-Excel的片材进-SQL服务器的数据库功能于ASP.Net.aspx [ ^ ]

Refer:
1. how-to-import-exel-file-to-sql-server-using-vb.net[^]
2. Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx[^]

这篇关于将数据从Excel文件上传到vb.net win app中的SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！