Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Hi Buddies,
 
Since I'm new to VB.net, can anyone guide me to understand the codings for uploading excel file into sql server 2000 in VB.Net. I need brief explanation on this. Kindly response me as a procedure so that it is easy to understand and help me to enhance it.c I'm waiting for your replies. contact me @ surae31@ymail.com/8122149820
Posted 27-Nov-12 16:17pm
Edited 27-Nov-12 16:20pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

First you need to import excel to datatable and then, connect datatable in your sql sever. The pre-condition of below code is that there is a datatable already existing in your database, and the datatable has the same structure with the original Excel file.
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
      Dim workbook As Workbook = New Workbook
      workbook.LoadFromFile("..\..\DataTableSample.xls")
      Dim sheet As Worksheet = workbook.Worksheets(0)
      Dim data As DataTable = sheet.ExportDataTable
      'set the string connection to be the local databasestring
      Dim connectionStr As String = ""
      Dim conn As SqlConnection = New SqlConnection(connectionStr)
      Dim i As Integer = 0
      Do While (i < data.Rows.Count)
          Dim row As DataRow = data.Rows(i)
          Dim columnCount As Integer = data.Columns.Count
          Dim columns() As String = New String((columnCount) - 1) {}
          Dim j As Integer = 0
          Do While (j < columnCount)
              columns(j) = row(j).ToString
              j = (j + 1)
          Loop
          ' suppose there are only three columns in your datatable
          Dim command As SqlCommand = New SqlCommand(("insert into DataTableSample(column1,column2,column3) values('"  _
                          + (columns(0) + ("','"  _
                          + (columns(1) + (" ','"  _
                          + (columns(2) + "')")))))))
          command.ExecuteNonQuery
          i = (i + 1)
      Loop
      conn.Close
  End Sub
 
to perform above code, you may need download an Excel component: http://www.codeproject.com/Catalogs/3785/Spire-XLS.aspx[^]
  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.140709.1 | Last Updated 28 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid