1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO.Directory
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Public Class Form1
Dim sda As SqlDataAdapter
Dim ds As DataSet
Private Sub btnBrowse_Click(sender As System.Object, e As System.EventArgs) Handles btnBrowse.Click
Dim dataAdapter As New SqlClient.SqlDataAdapter()
Dim dataSet As New DataSet
Dim command As New SqlClient.SqlCommand
Dim datatableMain As New System.Data.DataTable()
Dim connection As New SqlClient.SqlConnection
connection.ConnectionString = "My Connection String"
command.Connection = connection
command.CommandType = CommandType.Text
command.CommandText = "Select * from Statistiques"
dataAdapter.SelectCommand = command
Dim f As FolderBrowserDialog = New FolderBrowserDialog
Try
If f.ShowDialog() = DialogResult.OK Then
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add(Type.Missing)
oSheet = oBook.Worksheets(1)
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
connection.Open()
dataAdapter.Fill(datatableMain)
connection.Close()
For Each dc In datatableMain.Columns
colIndex = colIndex + 1
oSheet.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In datatableMain.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In datatableMain.Columns
colIndex = colIndex + 1
oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
Dim fileName As String = "\ExportedStatistiques" + ".xls"
Dim finalPath = f.SelectedPath + fileName
txtPath.Text = finalPath
oSheet.Columns.AutoFit()
oBook.SaveAs(finalPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
ReleaseObject(oSheet)
oBook.Close(False, Type.Missing, Type.Missing)
ReleaseObject(oBook)
oExcel.Quit()
ReleaseObject(oExcel)
GC.Collect()
MessageBox.Show("Fichier généré!")
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
End Try
End Sub
enter code here
Private Sub ReleaseObject(ByVal o As Object)
Try
While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
End While
Catch
Finally
o = Nothing
End Try
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.StatistiquesTableAdapter.Fill(Me.Statistiques._statistiques)
Try
Dim cn As New SqlConnection("My Connection String")
cn.Open()
Dim updcmd As SqlCommand
updcmd = New SqlCommand("Select Login from Statistiques", cn)
sda = New SqlDataAdapter(updcmd)
ds = New DataSet("Statistiques")
sda.Fill(ds, "Statistiques")
ComboBox1.DataSource = ds.Tables(0)
ComboBox1.DisplayMember = "Statistiques.Login"
ComboBox1.ValueMember = "Login"
ComboBox1.SelectedIndex = -1
Catch EX As Exception
MsgBox(EX.ToString)
End Try
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If MsgBox("Voulez vous vraiment quitter l'application ?", vbYesNo, "Quitter") = vbYes Then
End
End If
End Sub
Private Sub txtPath_TextChanged(sender As Object, e As EventArgs) Handles txtPath.TextChanged
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Me.Validate()
sda.Update(ds, "Statistiques")
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
Dim str As String = "My Connection String"
Dim con As New SqlConnection(str)
Dim com As String = "Select * from Statistiques"
Dim Adpt As New SqlDataAdapter(com, con)
Dim ds As New DataSet()
Adpt.Fill(ds, "Statistiques")
DataGridView1.DataSource = ds.Tables(0)
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Try
Dim cn As New SqlConnection("Data Source=PRSAISIE38\ABDELAZIZ;Initial Catalog=user;Persist Security Info=True;User ID=sa;Password=P@ssw0rD")
cn.Open()
Dim updcmd As SqlCommand = New SqlCommand("Select * from Statistiques where Login = '" + ComboBox1.Text + "' ", cn)
sda = New SqlDataAdapter(updcmd)
Dim ds As New DataSet
sda.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Label2_Click(sender As Object, e As EventArgs) Handles Label2.Click
End Sub
End Class |
Mettre à jour la Base de données en modifiant DataGridView
Répondre avec citation
Partager