How to update the SQL Server DB from Excel Document using vb.net

[hf28]

Hello,
I'm new to VB.NET. Could anyone help me with my question:
"How to update SQL SERVER DB from Excel doc, using VB.Net"

Thanks a lot.

 

[DotNetDoc]

Is this a one time shot? If so then just use DTS in SQL Server.


If not then you could load the excel file into a dataset and update from there.

There are many options. Could you give more information on exactly what you are trying to do.

DotNetDoc
M.C.S.D.
---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb

-----------------------------------

If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[hf28]

Hello DotNetDoc,

appreciate your time and desire to help.

This is a goal of mine.

1. I got to read data from the SQL Server DB and represent the report in the Excel format.
To do it I wrote the following code:
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient


Namespace SQLServertoExcel
' Summary description for Form2.
Public Class Form2
Inherits System.Windows.Forms.Form
Friend WithEvents button1 As System.Windows.Forms.Button
Private components As System.ComponentModel.Container = Nothing

Public Sub New()
' Required for Windows Form Designer support
InitializeComponent()
End Sub 'New

Protected Overloads Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub 'Dispose

' <summary>
' Required method for Designer support - do not modify
' the contents of this method with the code editor.
' </summary>
Private Sub InitializeComponent()
Me.button1 = New System.Windows.Forms.Button()
Me.SuspendLayout()
'
'button1
'
Me.button1.Location = New System.Drawing.Point(32, 72)
Me.button1.Name = &quot;button1&quot;
Me.button1.Size = New System.Drawing.Size(216, 112)
Me.button1.TabIndex = 0
Me.button1.Text = &quot;Populate Excel With Customer Information From Microsoft SQL Server&quot;
'
'Form2
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 273)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.button1})
Me.Name = &quot;Form2&quot;
Me.Text = &quot;Form2&quot;
Me.ResumeLayout(False)

End Sub 'InitializeComponent

' <summary>
' The main entry point for the application.
' </summary>
<STAThread()> _
Shared Sub Main()
Application.Run(New Form2())
End Sub 'Main

Private Function GetData() As System.Data.DataTable
Dim conn As SqlConnection
Dim strConn As String
'Set the Conn String to the NSRCG DB
'Don't forget to add TRUSTED_CONNECTION = TRUE for Development
strConn = &quot;server=MPR23;database=NSRCG;TRUSTED_CONNECTION=TRUE&quot;
conn = New SqlConnection(strConn)

Dim adapter As New SqlDataAdapter(&quot;select * FROM tlkpStatus&quot;, conn)
Dim myDataSet As New DataSet()
Try
adapter.Fill(myDataSet, &quot;Status&quot
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try

Return myDataSet.Tables(0)
End Function 'GetData

Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
Dim excel As New Excel.ApplicationClass()
Dim rowIndex As Integer = 1
Dim colIndex As Integer = 0

excel.Application.Workbooks.Add(True)

Dim table As DataTable = GetData()

Dim col As DataColumn
For Each col In table.Columns
colIndex += 1
excel.Cells(1, colIndex) = col.ColumnName
Next col

Dim row As DataRow
For Each row In table.Rows
rowIndex += 1
colIndex = 0
Dim col1 As DataColumn
For Each col1 In table.Columns
colIndex += 1
excel.Cells(rowIndex, colIndex) = row(col1.ColumnName).ToString()
Next col1
Next row
excel.Visible = True
End Sub 'button1_Click

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub
End Class 'Form2
End Namespace 'SQLServertoExcel

2. When the user updates whatever fields in that Excel document and saves Excel, Im supposed to write a code to update the DB. That's what I don't know how to do.

 

[DotNetDoc]

This could theoreticly be done but unless you are tied into doing it that way I would suggest that you just show a datagrid on the form. Have them make any updates in the datagrid and then click a button to show the excel report again. (In this button click you could update the database as well.)


The excel file would need to be saved and closed to be able to read back the data anyway. Or have a special form and use VBA to capture events in the exel document and send the data back to the database.

Either way you would really be wasting your time. VB.net also has Crystal Reports in it. Crystal reports can be exported to excel as well.

Let me know what you decide to do. If you still decide to go with your first decision let me know and I will try to help you work it out.

DotNetDoc
M.C.S.D.
---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb

-----------------------------------

If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[hf28]

Hello DotNetDoc,

I'm back to you again.
They still want it to be with Excel. They even want to put the button on the Excel form, which will be clicked if some updates were made and would be responsible to point Excel doc to the certain folder and save it there. After that I'll have to use that document as my Data Source. Later on, I'll not update some certain values in the DB with that Excel data. But what I'll do instead is just after saving the existing data into a certain place, I'll delete the entire data from the table and insert the new one from my new Data Source, which will be an updated and saved Excel Doc. What do you think about it?

Again, thanks a lot for your help.

 

[DotNetDoc]

I wish I could help you talk them out of it but it sounds like your plan will work. Good luck on the project.

DotNetDoc
M.C.S.D.
---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb

-----------------------------------

If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[hf28]

Thanks.
Time to struggle. I'll try to do it. Do you know how to insert data into the SQL Server DB table from Excel, if I use it as my data source?

 

[DotNetDoc]

I think the easiest way would be to use the excel sheet as a datasource and load it into a dataset. Then you could just update it from the dataset. I think that would be much easier than reading the excel file row by row cell by cell and updating that way.

DotNetDoc
M.C.S.D.
---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb

-----------------------------------

If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[hf28]

Thanks.

Do you have a sample code of how to do it?