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
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
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
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
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
