vb.net連接SQL数据库

'導入命名空間
Imports System.Data.SqlClient

'定義變量
Dim Sql As String 'SQL字串
Dim Sqlado As SqlConnection '连接数据库
Dim CmdVB As SqlCommand '对数据源执行SQL命令并返回数据
Dim drVB As SqlDataReader '读取和处理数据源的数据

'打开ADO連線
Public Sub OpenADO()
Sqlado = New SqlConnection("Password=密碼;Persist Security Info=True;User ID=帳號;Initial Catalog=数据表;Data Source=数据源")
If Sqlado.State = ConnectionState.Closed Then
Sqlado.Open()
End If
CmdVB = New SqlCommand(Sql, Sqlado)
drVB = CmdVB.ExecuteReader()
End Sub

'关闭ADO連線
Public Sub CloseADO()
drVB.Close()
If Sqlado.State = ConnectionState.Open Then
Sqlado.Close()
End If
End Sub

 

Private Sub A1()'查詢
Sql = ("Select A1 From Table1 ")

OpenADO() '打开ADO連線
drVB.Read()‘下一筆
TextBox1.Text = drVB.Item(0)’第一列資料

TextBox2.Text = drVB.Item(1)’第二列資料
CloseADO() ' '关闭ADO連線

End Sub

Private Sub A2()'修改
Sql = ("update Table1 set A2 = '" & TextBox2.Text & "' where (A1 = '" & TextBox1.Text & "')")

Sql = (Sql)

OpenADO() '打开ADO連線
CloseADO() ' '关闭ADO連線

End Sub

 

 Private Sub A3()'新增

Sql = ("insert into Table1 (A1,A2) values ('" & TextBox1.Text & "','" & TextBox2.Text & "')")

OpenADO() '打开ADO連線

CloseADO() ' '关闭ADO連線

End Sub

 

Private Sub A4()'刪除

Sql =("delete from Table1 where (A1 = '" & TextBox1.Text & "')")

Sql=(Sql)

OpenADO() '打开ADO連線

CloseADO() ' '关闭ADO連線

End Sub