form1.frm

VB操作SQL实例,快来下载吧,对初学者很有帮助的

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   3885
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   7890
   LinkTopic       =   "Form1"
   ScaleHeight     =   3885
   ScaleWidth      =   7890
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton Command4 
      Caption         =   "列出数据"
      Height          =   495
      Left            =   1080
      TabIndex        =   10
      Top             =   120
      Width           =   1335
   End
   Begin VB.TextBox Text3 
      Height          =   375
      Left            =   2160
      TabIndex        =   8
      Top             =   2760
      Width           =   1455
   End
   Begin VB.TextBox Text2 
      Height          =   375
      Left            =   2160
      TabIndex        =   6
      Top             =   2040
      Width           =   1455
   End
   Begin VB.ListBox List1 
      Height          =   3660
      Left            =   3720
      TabIndex        =   4
      Top             =   120
      Width           =   4095
   End
   Begin VB.CommandButton Command3 
      Caption         =   "删除"
      Height          =   495
      Left            =   240
      TabIndex        =   3
      Top             =   2760
      Width           =   1335
   End
   Begin VB.CommandButton Command2 
      Caption         =   "添加"
      Height          =   495
      Left            =   240
      TabIndex        =   2
      Top             =   1920
      Width           =   1335
   End
   Begin VB.TextBox Text1 
      Height          =   375
      Left            =   2160
      TabIndex        =   1
      Top             =   1080
      Width           =   1455
   End
   Begin VB.CommandButton Command1 
      Caption         =   "查询"
      Height          =   495
      Left            =   240
      TabIndex        =   0
      Top             =   1080
      Width           =   1335
   End
   Begin VB.Label Label3 
      Caption         =   "姓名"
      Height          =   255
      Left            =   1680
      TabIndex        =   9
      Top             =   2880
      Width           =   375
   End
   Begin VB.Label Label2 
      Caption         =   "姓名"
      Height          =   255
      Left            =   1680
      TabIndex        =   7
      Top             =   2160
      Width           =   375
   End
   Begin VB.Label Label1 
      Caption         =   "姓名"
      Height          =   255
      Left            =   1680
      TabIndex        =   5
      Top             =   1200
      Width           =   375
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public conn As ADODB.Connection
Public rs As ADODB.Recordset

Private Sub Command1_Click()
Dim sqlstr As String
sqlstr = "select  * from stu where name='" + Text1.Text + "'"
 rs.Open sqlstr, conn
 If Not rs.EOF Then
   MsgBox "姓名：" + rs.Fields("name") + vbCr + "性别：" + rs.Fields("sex") + vbCr + "班级：" + rs.Fields("class")
 Else
   MsgBox "没有查询到姓名为" + Text1.Text + "的数据"
 End If
rs.Close
End Sub

Private Sub Command2_Click()
Dim sqlstr As String
'sqlstr = "insert into stu (name,sex,class)values('" + Text2.Text + "','男','会计一班')"
sqlstr = "insert into stu (name,sex,class)values('张三','男','会计一班')"
rs.Open sqlstr, conn
Call ListData
MsgBox "添加成功"
End Sub

Private Sub Command3_Click()
Dim sqlstr As String
sqlstr = "delete  from stu where name='" + Text3.Text + "'"
rs.Open sqlstr, conn
Call ListData
MsgBox "删除成功"
End Sub

Private Sub Command4_Click()
Call ListData
End Sub

Private Sub Form_Load()
  Set conn = New ADODB.Connection '定义对象
  Set rs = New ADODB.Recordset    '定义对象
  'conn.ConnectionString = "Driver={sql server};server=ssy-pc;uid=sa;pwd=sa;database=test" '数据库连接串 连接SQL数据库
  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\db1.mdb" '连接ACCESS数据库
  conn.ConnectionTimeout = 10 '连接超时
  conn.Open '打开数据库
End Sub


Sub ListData()
Dim sqlstr As String
sqlstr = "select * from stu"
rs.Open sqlstr, conn, adOpenStatic, adLockReadOnly, adCmdText 'adOpenDynamic adOpenStatic
List1.Clear
' List1.AddItem ("序号" & "    " & "姓名" + "    " + "性别" + "    " + "班级")
For i = 0 To rs.RecordCount - 1
  List1.AddItem (CStr(i) + "       " + Trim(rs.Fields("name")) + "     " + Trim(rs.Fields("sex")) + "    " + Trim(rs.Fields("class")))
  rs.MoveNext
Next
rs.Close
End Sub