programmer un dataset sur mysql

**philo_neo** · 20/08/2014, 18h33

bonjour,
Je cherche a utiliser un objet dataset sur une base de donnée Mysql pour gagner du temps sur le développement.
J'arrive a faire des UPDATE et des INSERT, Mais au SELECT ce dernier ne remplis pas le datagridview.
Je voudrais pouvoir utiliser un objet dataset afin de faire le moins possibles de requètes SQL.

voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports MySql.Data
Imports MySql.Data.MySqlClient
 
 
Public Class client
    Inherits System.Windows.Forms.Form
    ' Initialize constants for connecting to the database
    ' and displaying a connection error to the user.
    ' "Server=127.0.0.1;Database=retd;User ID=administrator;Password=ipupp8v;Trusted_Connection=True;"
    Protected Const SQL_CONNECTION_STRING As String = "Data Source=192.168.0.13;Initial Catalog=db_plan_production; User ID=root;Password=toor;"
 
    Protected Const MSDE_CONNECTION_STRING As String = _
        "Server=(192.168.0.13)\NetSDK;" & _
        "DataBase=dblabservice;" & _
        "Integrated Security=SSPI"
 
    Protected Const CONNECTION_ERROR_MSG As String = _
        "To run this sample, you must have SQL " & _
        "or MSDE with the Northwind database installed.  For " & _
        "instructions on installing MSDE, view the ReadMe file."
 
    Protected bolDidPreviouslyConnect As Boolean = False
    Protected bolDidCreateTable As Boolean = False
    Protected connectionString As String = SQL_CONNECTION_STRING
    Public Shared cmd As MySqlCommand
    Public Shared da As MySqlDataAdapter = New MySqlDataAdapter(cmd)
    ' Public Shared ds_client As New DataSet
    Public Shared ds_client As New db_plan_productionDataSet
    Public Shared i As Integer
    Public Shared max_i As Integer
    'Public connection As New MySqlConnection(connStr)
    Public Shared PatTableAdapter = New db_plan_productionDataSetTableAdapters.tbl_clientTableAdapter
    'Public connection As New MySqlConnection(connStr)
 
 
    Private Sub client_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Db_plan_productionDataSet.tbl_client' table. You can move, or remove it, as needed.
        'Me.Tbl_clientTableAdapter.Fill(Me.Db_plan_productionDataSet.tbl_client)
        TextBox1.Text = "%"
        'ComboBox1.Text = "code_cl"
        ComboBox1.SelectedText = "code_cl"
        Me.ComboBox1.Items.Add("code_cl")
        Me.ComboBox1.Items.Add("raison_sociale")
        Me.ComboBox1.Items.Add("code_postal")
        Me.ComboBox1.Items.Add("ville")
        Me.ComboBox1.Items.Add("immat_tva")
        Me.ComboBox1.Items.Add("regime_tva")
        Me.ComboBox1.Items.Add("date_creation")
        Me.ComboBox1.Items.Add("date_modif")
        Me.ComboBox1.Items.Add("tiers_mi_sommeil")
        Me.ComboBox1.Items.Add("pays")
        Me.ComboBox1.Items.Add("adresse1")
        Me.ComboBox1.Items.Add("adresse2")
        Me.ComboBox1.Items.Add("adresse3")
        Me.ComboBox1.Items.Add("text_lib_tiers1")
        Me.ComboBox1.Items.Add("text_lib_tiers2")
        Me.ComboBox1.Items.Add("text_lib_tiers3")
        Me.ComboBox1.Items.Add("tiers_bloque")
 
        TextBox2.Text = "%"
        'ComboBox2.Text = "code_cl"
        ComboBox2.SelectedText = "code_cl"
        Me.ComboBox2.Items.Add("code_cl")
        Me.ComboBox2.Items.Add("raison_sociale")
        Me.ComboBox2.Items.Add("code_postal")
        Me.ComboBox2.Items.Add("ville")
        Me.ComboBox2.Items.Add("immat_tva")
        Me.ComboBox2.Items.Add("regime_tva")
        Me.ComboBox2.Items.Add("date_creation")
        Me.ComboBox2.Items.Add("date_modif")
        Me.ComboBox2.Items.Add("tiers_mi_sommeil")
        Me.ComboBox2.Items.Add("pays")
        Me.ComboBox2.Items.Add("adresse2")
        Me.ComboBox2.Items.Add("adresse2")
        Me.ComboBox2.Items.Add("adresse3")
        Me.ComboBox2.Items.Add("text_lib_tiers2")
        Me.ComboBox2.Items.Add("text_lib_tiers2")
        Me.ComboBox2.Items.Add("text_lib_tiers3")
        Me.ComboBox2.Items.Add("tiers_bloque")
    End Sub
 
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
 
 
        TblclientBindingSource.EndEdit()
 
        'Vérifiez que des modifications ont eu lieu
 
        If Db_plan_productionDataSet.HasChanges Then
 
            'Appliquer les changements dans la base de données
 
            PatTableAdapter.Update(Db_plan_productionDataSet.tbl_client)
 
        End If
    End Sub
 
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Me.Tbl_clientTableAdapter.Fill(Me.Db_plan_productionDataSet.tbl_client)
    End Sub
 
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
 
 
        TblclientBindingSource.RemoveCurrent()
 
        'Vérifiez que des modifications ont eu lieu
 
        'If Db_plan_productionDataSet.HasChanges Then
 
        'Appliquer les changements dans la base de données
 
        PatTableAdapter.Update(Db_plan_productionDataSet.tbl_client)
 
        '  End If
    End Sub
 
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        ''' ***** afichage Select **********
        'Dim northwindConnection As New SqlConnection(connectionString)
        Dim northwindConnection As New MySqlConnection(SQL_CONNECTION_STRING)
 
        'select * from tbl_client where Code_cl like 'b%';select * from tbl_client where code_cl like  'm%' and raison_sociale like 's%'
 
        Dim strSQL As String = "select * from tbl_client where " + Me.ComboBox1.SelectedItem + " like '" + Trim(Me.TextBox1.Text) + "'" + " and " + Me.ComboBox2.SelectedItem + " like '" + Trim(Me.TextBox2.Text) + "'"
        Me.TextBox3.Text = strSQL
        ' A SqlCommand object is used to execute the SQL commands.
        cmd = New MySqlCommand(strSQL, northwindConnection)
 
 
        northwindConnection.Open()
        cmd.ExecuteNonQuery()
 
        '--------------
        ' da.SelectCommand = cmd
        'dsSeafood.Clear()
        'da.Fill(dsSeafood, "Seafood")
        ' dgSeafood.DataSource = dsSeafood.Tables(0)
 
        da.SelectCommand = cmd
        ds_client.Clear()
        ' da.Fill(ds_client, 0)
        da.Fill(ds_client, 0)
        DataGridView1.DataSource = ds_client.Tables(0)
 
 
        With DataGridView1
            '.CaptionText = "Client"
            .Visible = True
 
        End With
 
 
        northwindConnection.Close()
 
    End Sub
End Class

il faudrais que je puisse coder cette partie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 da.SelectCommand = cmd
        ds_client.Clear()
        ' da.Fill(ds_client, 0)
        da.Fill(ds_client, 0)
        DataGridView1.DataSource = ds_client.Tables(0)

Cdlt,
Philippe

**mactwist69** · 21/08/2014, 09h07

JE n'ai pas les objets MySQL sous la main, mais d'habitude, on ne fait pas les choses dans cet ordre.
Ca ressemble normalement plutôt à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Dim conn As New MySqlConnection(connection)
Dim adapter As New MySqlDataAdapter()
adapter.SelectCommand = new MySqlCommand(query, conn)
adapter.Fill(dataset)

**philo_neo** · 23/08/2014, 00h15

bonsoir,
j'ai suivis tes explications, mais hélas sans résultat :
voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports MySql.Data
Imports MySql.Data.MySqlClient
 
Public Class Form1
    Protected Const SQL_CONNECTION_STRING As String = "Data Source=192.168.0.13;Initial Catalog=db_plan_production; User ID=root;Password=toor;"
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Db_plan_productionDataSet.tbl_client' table. You can move, or remove it, as needed.
        'Me.Tbl_clientTableAdapter.Fill(Me.Db_plan_productionDataSet.tbl_client)
 
    End Sub
 
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
 
        Dim conn As New MySqlConnection(SQL_CONNECTION_STRING)
        Dim adapter As New MySqlDataAdapter()
 
        Dim query As String = "select * from tbl_client"
 
        adapter.SelectCommand = New MySqlCommand(query, conn)
        adapter.Fill(Db_plan_productionDataSet)
        DataGridView1.DataSource = Db_plan_productionDataSet.Tables(0)
 
    End Sub
End Class

Cdlt
Philippe

programmer un dataset sur mysql

VB.NET

Vue hybride

Discussions similaires

Partager

Partager