vb 求教连接读写sql server 2012数据库,要源码

Option Explicit

链接数据库的字符串

Public Const Conn As String = "Provider=SQLOLEDB.1Persist Security Info=False" &_

"User ID=saPWD=123Initial Catalog=aaaData Source=127.0.0.1"

Private IsConnect As Boolean 判读数据库是否链接

Private cnn As ADODB.Connection链接数据库的connection对象

Private rs As ADODB.Recordset 保存结果集的recordset对象

数据库链接程序

Public Sub Db_Connect()

如果链接标记为真 返回

If IsConnect = True Then

Exit Sub

End If

关键字New用来创建新对象cnn

Set cnn = New ADODB.Connection

设置cnn的链接字符串的connectstring属性

cnn.ConnectionString = Conn

打开数据库

cnn.Open

判断链接状态

If cnn.State <>adStateOpen Then

如果不成功 显示提示 退出程序

MsgBox "链接数据库失败"

End

End If

设置链接标记 表示已经链接到数据库

IsConnect = True

End Sub

断开数据库的链接程序

Public Sub Db_Disconnect()

如果链接标记为假，表明已经断开 直接返回

If IsConnect = False Then

Exit Sub

End If

关闭链接

cnn.Close

设置链接标记，表明已经断开链接

IsConnect = False

End Sub

'只用来执行Insert，update，delete语句

Public Sub sqlExt(tmpSql As String)

创建command对象cmd

Dim cmd As New ADODB.Command

连接到数据库

DbConnect

设置cmd的ActiveConnection属性，指定其关联的数据库链接

Set cmd.ActiveConnection = cnn

设置要执行的命令文本

cmd.CommandText = tmpSql

执行命令

cmd.Execute

清空cmd对象

Set cmd = noting

断开与数据库的连接

Db_Disconnect

End Sub

执行数据库查询程函数

Public Function QueryExt(tmpSql As String) As ADODB.Recordset

创建recordset对象rst

Dim rst As New ADODB.Recordset

链接到数据库

Db_Connect

设置rst的ActiveConnection属性，指定与其关联的数据库连接

Set rst.ActiveConnection = cnn

设置游标类型

rst.CursorType = adOpenDynamic

设置锁定类型

rst.LockType = adLockOptimistic

打开记录集

rst.Open tmpSql

返回记录集

Set QueryExt = rst

End Function

定义一个数据库连接

Dim

con

As

New

SqlConnection("workstation

id=计算机名packet

size=4096user

id=sadata

source=.persist

security

info=Falseinitial

catalog=数据库名")

对数据库插入

Dim

incom

As

New

SqlCommand("insert

into

表名values(@id,@name,@password,@depart,@class)",

con)

incom.Parameters.Add("@id",

Me.idtb.Text)

incom.Parameters.Add("@name",

Me.nametb.Text)

incom.Parameters.Add("@password",

Me.passwordtb.Text)

incom.Parameters.Add("@depart",

Me.departtb.Text)

incom.Parameters.Add("@class",

Me.classtb.Text)

con.Open()

incom.ExecuteNonQuery()

con.Close()

MsgBox("insert

good")

Me.DataGrid1.Refresh()

删除数据库项

Dim

deleteEMP

As

SqlClient.SqlCommand

=

New

SqlClient.SqlCommand("delete

from

表名

where

列名

=

'"

&

checkcb.Text

&

"'",

conn)

result

=

deleteEMP.ExecuteNonQuery()

conn.Close()

Me.DataGrid1.Refresh()

MsgBox("delete

good")

对数据库更新

Dim

upcom

As

New

SqlCommand("update

表明set

Stu_name=@name,Stu_password=@password,Stu_depart=@depart,Stu_class=@class

where

Stu_id=@id",

con)

upcom.Parameters.Add("@id",

Me.idtb.Text)

upcom.Parameters.Add("@name",

Me.nametb.Text)

upcom.Parameters.Add("@password",

Me.passwordtb.Text)

upcom.Parameters.Add("@depart",

Me.departtb.Text)

upcom.Parameters.Add("@class",

Me.classtb.Text)

con.Open()

upcom.ExecuteNonQuery()

con.Close()

MsgBox("update

good")

conStr = "Provider=Microsoft.Jet.OLEDB.4.0"DataSource = "D:\Access2003\Access2003\db1.mdb"这2行不能分开写，不然你的conStr中找不到数据库。。。改为：conStr = "Provider=Microsoft.Jet.OLEDB.4.0DataSource = D:\Access2003\Access2003\db1.mdb"

---