VB.net et SQL

poilusduboux
Messages postés
83
Date d'inscription
jeudi 19 mai 2005
Statut
Membre
Dernière intervention
17 juin 2011
- 8 mai 2006 à 16:39
cs_smida
Messages postés
1
Date d'inscription
jeudi 20 novembre 2008
Statut
Membre
Dernière intervention
1 décembre 2008
- 1 déc. 2008 à 16:59
Bonjour

je travail depuis peu avec Visual Studio 2005


(Je fais du VB.net)


J’ai crée une base de donnée SQL.


J’arrive bien à ouvrir ma base de données, pour visualiser son contenu.


Mais tout ce complique lorsque je veux faire un Update avec


SqlDataAdapter.
Il ne crée pas automatiquement la commande Update et la commande Delete.
Si quelqu’un peut m’aider ?



Merci d’avance.

1 réponse

cs_smida
Messages postés
1
Date d'inscription
jeudi 20 novembre 2008
Statut
Membre
Dernière intervention
1 décembre 2008

1 déc. 2008 à 16:59
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
0