VB.net et SQL

Question

Bonjour 

je travail depuis peu avec Visual Studio 2005


(Je fais du VB.net)


J&#146;ai cr&#233;e une base de donn&#233;e SQL.


J&#146;arrive bien &#224; ouvrir ma base de donn&#233;es, pour visualiser son contenu.


Mais tout ce complique lorsque je veux faire un Update avec 


SqlDataAdapter. 
Il ne cr&#233;e pas automatiquement la commande Update et la commande Delete.
Si quelqu&#146;un peut m&#146;aider ?



Merci d&#146;avance.

cs_smida · Answer

Public Class Form1
    Private _mycon As New MySqlConnection
    Private _da As New MySqlDataAdapter
    Private _dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim csb As New MySqlConnectionStringBuilder
        With csb
            .Server = "localhost"
            .Port = "3307"
            .UserID = "root"
            .Password = "1234"
            .Database = "dbemps"
        End With
        _mycon = New MySqlConnection(csb.ConnectionString)
        _mycon.Open()
        _dt = gtable("emps")
        Me.TextEdit1.DataBindings.Add(New Binding("Editvalue", _dt, "e_fname", True))
        Me.GridControl1.DataSource = _dt
    End Sub    Private Function gtable(ByVal tabname As String, Optional ByVal fields As String "*", Optional ByVal cond As String "", Optional ByVal order As String = "") As DataTable
        Dim dt As New DataTable
        Dim sql As String = "SELECT {0} FROM {1} {2} {3}"
        If Not cond = "" Then
            cond = "WHERE" & cond
        End If
        If Not order = "" Then
            order = "ORDER BY" & order
        End If
        sql = String.Format(sql, fields, tabname, cond, order)
        Dim selcm As New MySqlCommand
        selcm.Connection = _mycon
        selcm.CommandText = sql
        _da.SelectCommand = selcm
        _da.Fill(dt)
        dt.TableName = tabname
        Return dt
    End Function
    
    Private Sub SimpleButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SimpleButton1.Click
        Me.GridView1.AddNewRow()

    End Sub
    Private Sub ins(ByVal r As DataRow, ByVal tablname As String)
        Dim sql As String = "INSERT INTO " & tablname & "({0}) VALUES({1})"
        Dim sql_f As String = ""
        Dim sql_v As String = ""
        'Dim r As DataRow = 

        For Each c As DataColumn In _dt.Columns
            If Not IsDBNull(r.Item(c.ColumnName)) Then
                sql_f = "," & c.ColumnName
                If c.DataType Is GetType(String) Then
                    sql_v &= ",'" & r.Item(c.ColumnName) & "'"
               
                End If
            End If
        Next
        If sql_f.Length > 0 Then
            sql_f = sql_f.Substring(1)
            sql_v = sql_v.Substring(1)
            sql = String.Format(sql, sql_f, sql_v)
        End If
        sql = String.Format(sql, sql_f, sql_v)
        Dim cmdins As New MySqlCommand(sql, _mycon)
        cmdins.ExecuteNonQuery()

        r.Item(r.Table.Columns(0).ColumnName) = cmdins.LastInsertedId
        Me.GridControl1.RefreshDataSource()

    End Sub
    Private Sub del(ByVal tabname As String, Optional ByVal cond As String = "")
        Dim sql As String = "DELETE FROM {0} WHERE {1}"
        sql = String.Format(sql, tabname, cond)
        Dim cmddel As New MySqlCommand(sql, _mycon)
        cmddel.ExecuteNonQuery()
        Me.GridControl1.RefreshDataSource()
    End Sub
    
    Private Sub SimpleButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SimpleButton2.Click
        ins(Me.GridView1.GetFocusedDataRow, "emps")
    End Sub

    Private Sub SimpleButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SimpleButton3.Click
        del("emps", "e_id=31")
    End Sub
End Class