VB.NET SQL ALL功能


Imports System.Data
Imports System.Data.SqlClient
Public Class Form1

Inherits System.Windows.Forms.Form

Public Function ShowPerson()
Dim connStr, selectCmd As String
connStr = "server=127.0.0.1; database=DB; uid=***; pwd=***;"
selectCmd = "Select * From PERSON Order By id_no DESC"
Dim conn As SqlConnection, myAdapter As SqlDataAdapter
Dim myDataSet As New DataSet()
conn = New SqlConnection(connStr)
conn.Open()
myAdapter = New SqlDataAdapter(selectCmd, conn)
myAdapter.Fill(myDataSet, "PERSON")
DataGrid1.DataSource = myDataSet.Tables("PERSON")
End Function

Public Function GetSqlStr(ByVal str As String) As String
Return Replace(str, "'", "''")
End Function

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ShowPerson()
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim connStr, insertCmd As String
connStr = "server=127.0.0.1; database=DB; uid=***; pwd=***;"
insertCmd = "Insert Into PERSON(name, id_position, tel, salary) Values('" & _
GetSqlStr(txtName.Text) & "','" & GetSqlStr(txtPosition.Text) & _
"','" & GetSqlStr(txtTel.Text) & "'," & Val(txtSalary.Text) & ")"
Dim conn As SqlConnection, cmd As SqlCommand
conn = New SqlConnection(connStr)
conn.Open()
cmd = New SqlCommand(insertCmd, conn)
cmd.ExecuteNonQuery()
ShowPerson()
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim connStr, updateCmd As String
connStr = "server=127.0.0.1; database=DB; uid=***; pwd=***;"
updateCmd = "update PERSON Set id_position = '" & GetSqlStr(txtPosition.Text) & _
"',tel = '" & GetSqlStr(txtTel.Text) & "', salary = " & _
Val(txtSalary.Text) & " Where name = '" & GetSqlStr(txtName.Text) & "'"
Dim conn As SqlConnection, cmd As SqlCommand
conn = New SqlConnection(connStr)
conn.Open()
cmd = New SqlCommand(updateCmd, conn)
cmd.ExecuteNonQuery()
ShowPerson()
End Sub

Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
Dim connStr, delCmd As String
connStr = "server=127.0.0.1; database=DB; uid=***; pwd=***;"
delCmd = "Delete From PERSON Where name = '" & GetSqlStr(txtName.Text) & "'"
Dim conn As SqlConnection, cmd As SqlCommand
conn = New SqlConnection(connStr)
conn.Open()
cmd = New SqlCommand(delCmd, conn)
cmd.ExecuteNonQuery()
ShowPerson()
End Sub
End Class







Imports System.Data
Imports System.Data.SqlClient

Dim Bm As BindingManagerBase

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim selectCmd As String = "select id_no as 書號, name as 書名, price as 單價, qty as 數量 From BOOK"
Dim connStr As String = "Server=localhost; database=BOOKDB; uid=sa; pwd=;"
Dim conn As SqlConnection, myAdapter As SqlDataAdapter
Dim myDataSet As DataSet = New DataSet()
conn = New SqlConnection(connStr)
conn.Open()
myAdapter = New SqlDataAdapter(selectCmd, conn)
myAdapter.Fill(myDataSet, "BOOK")
Dim bindId As Binding = New Binding("Text", myDataSet, "BOOK.書號")
Dim bindName As Binding = New Binding("Text", myDataSet, "BOOK.書名")
Dim bindPrice As Binding = New Binding("Text", myDataSet, "BOOK.單價")
Dim bindQty As Binding = New Binding("Text", myDataSet, "BOOK.數量")
txtId.DataBindings.Add(bindId)
txtName.DataBindings.Add(bindName)
txtPrice.DataBindings.Add(bindPrice)
txtQty.DataBindings.Add(bindQty)
Bm = Me.BindingContext(myDataSet, "BOOK")
CheckBm()
End Sub

Private Function CheckBm()
If Bm.Position = 0 Then
btnFirst.Enabled = False
btnPrev.Enabled = False
btnNext.Enabled = True
btnLast.Enabled = True
ElseIf Bm.Position = Bm.Count - 1 Then
btnFirst.Enabled = True
btnPrev.Enabled = True
btnNext.Enabled = False
btnLast.Enabled = False
Else
btnFirst.Enabled = True
btnPrev.Enabled = True
btnNext.Enabled = True
btnLast.Enabled = True
End If
lblShow.Text = (Bm.Position + 1) & "/" & Bm.Count
End Function

Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
Bm.Position = 0
CheckBm()
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If Bm.Position < Bm.Count - 1 Then
Bm.Position += 1
End If
CheckBm()
End Sub

Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
If Bm.Position > 0 Then
Bm.Position -= 1
End If
CheckBm()
End Sub

Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
Bm.Position = Bm.Count - 1
CheckBm()
End Sub




Select Case TabControl1.SelectedTab.ToString
Case "TabPage: {發送簡訊}"
........................
Case "XXXX"