to huangjianyou(小健) 这些语句我几年前就接触了。我不知道在vb里面该怎么控制这些语句。用open?还是用execute?(试过好像都不行哦)抑或用其他的语句?
你要用引入DAO,用它就可以操作了
Option ExplicitPrivate Sub ADO_Data() On Error GoTo errHandlerr Dim cSql As String Dim adoCnnn As New ADODB.Connection With adoCnnn .ConnectionString = "Provider=SQLOLEDB.1; Data Source=LIBINGAO; Initial Catalog=ResearchData; UID=sa;PWD=;" 'Integrated Security=SSPI;Persist Security Info=False;" .Open
If .State = adStateOpen Then '以正确连接上服务器 LIBINGAO 上的数据库 ResearchData
.IsolationLevel = adXactReadCommitted .BeginTrans
cSql = "INSERT INTO AA (ID,Name) VALUES (10, '北方岛')" .Execute cSql, , adCmdText cSql = "UPDATE AA SET Name='华仙子' WHERE ID=10" .Execute cSql, , adCmdText cSql = "DELETE FROM AA WHERE ID=10" .Execute cSql, , adCmdText
.CommitTrans Else MsgBox "SQL Server 数据库连接失败 !", vbCritical, MsgBoxTitle Exit Sub End If End With Exit Sub errHandlerr: adoCnnn.RollbackTrans MsgBox Err.Description + ":" + CStr(Err.Number), vbCritical, "有错误发生 ..."End Sub
为什么都喜欢用Connection 对象,这些操作应该是command对象,我写了个执行这类语句的过程,自己测试好用 Public Sub ExSql(sql As String) '执行sql语句 dim cn as string Dim com As ADODB.Command Set com = New ADODB.Command cn="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=oa" com.ActiveConnection = cn com.CommandText = sql com.Execute End Sub
connection对象的execute我用过,但是程序总是报错。 你们上面的有没有经过调试?
Option ExplicitPrivate Sub ADO_Data() On Error GoTo errHandlerr Dim cSql As String Dim adoCnnn As New ADODB.Connection With adoCnnn .ConnectionString = "Provider=SQLOLEDB.1;Data Source=LIBINGAO;Initial Catalog=ResearchData; UID=sa;PWD=;" .Open
If .State = adStateOpen Then '以正确连接上服务器 LIBINGAO 上的数据库 ResearchData
.IsolationLevel = adXactReadCommitted .BeginTrans
cSql = "CREATE TABLE AA (ID int,Name varchar(20),Age float)" .Execute cSql, , adCmdText cSql = "INSERT INTO AA (ID,Name,Age) VALUES (10, '北方岛',20)" .Execute cSql, , adCmdText
cSql = "UPDATE AA SET Name='华仙子' WHERE ID=10" .Execute cSql, , adCmdText
cSql = "DELETE FROM AA WHERE ID=10" .Execute cSql, , adCmdText
cSql = "ALTER TABLE AA ADD TEL varchar(20)" .Execute cSql, , adCmdText
cSql = "ALTER TABLE AA DROP COLUMN TEL" .Execute cSql, , adCmdText
cSql = "DROP TABLE AA " .Execute cSql, , adCmdText
.CommitTrans .Close Else MsgBox "SQL Server 数据库连接失败 !", vbCritical, MsgBoxTitle Exit Sub End If End With Exit Sub errHandlerr: If adoCnn.State = adStateOpen Then adoCnnn.RollbackTrans adoCnn.Close End If MsgBox Err.Description + ":" + CStr(Err.Number), vbCritical, "有错误发生 ..."End Sub 以上语句 VB6.0 + SQL Server2000 测试通过! 不过,在你实际测试中,应该将建表、添加记录、更新记录、删除记录、添加字段、删除字段和删除表放在不同的事务中,这样你可以在 SQL Server 中看到实际的运行结果!否则,程序运行时虽不报错,当由于在同一个事务中包含了上面的所有操作,等到事务结束时,所做的全部操作由于后面的 “删除字段和删除表” 操作导致全被删除! 且记!
新建表用: Create table Tablename ...
删除一张表用:DROP TABLE Tablename
修改表结构用: ALTER TABLE Tablename ...
这些语句我几年前就接触了。我不知道在vb里面该怎么控制这些语句。用open?还是用execute?(试过好像都不行哦)抑或用其他的语句?
On Error GoTo errHandlerr
Dim cSql As String
Dim adoCnnn As New ADODB.Connection
With adoCnnn
.ConnectionString = "Provider=SQLOLEDB.1; Data Source=LIBINGAO; Initial Catalog=ResearchData; UID=sa;PWD=;" 'Integrated Security=SSPI;Persist Security Info=False;"
.Open
If .State = adStateOpen Then '以正确连接上服务器 LIBINGAO 上的数据库 ResearchData
.IsolationLevel = adXactReadCommitted
.BeginTrans
cSql = "INSERT INTO AA (ID,Name) VALUES (10, '北方岛')"
.Execute cSql, , adCmdText
cSql = "UPDATE AA SET Name='华仙子' WHERE ID=10"
.Execute cSql, , adCmdText
cSql = "DELETE FROM AA WHERE ID=10"
.Execute cSql, , adCmdText
.CommitTrans
Else
MsgBox "SQL Server 数据库连接失败 !", vbCritical, MsgBoxTitle
Exit Sub
End If
End With
Exit Sub
errHandlerr:
adoCnnn.RollbackTrans
MsgBox Err.Description + ":" + CStr(Err.Number), vbCritical, "有错误发生 ..."End Sub
我要求的是如何在程序中执行ddl语言,如建表(create),删表(drop),修改(alter)表结构等。
sFileName & ";Persist Security Info=False"cn.Execute "CREATE TABLE Employees " _
& "(FirstName TEXT, LastName TEXT);"cn.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary CURRENCY;"cn.Execute "DROP TABLE Employees;"cn.Close
et cn = Nothing
Public Sub ExSql(sql As String)
'执行sql语句
dim cn as string
Dim com As ADODB.Command
Set com = New ADODB.Command
cn="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=oa"
com.ActiveConnection = cn
com.CommandText = sql
com.Execute
End Sub
你们上面的有没有经过调试?
On Error GoTo errHandlerr
Dim cSql As String
Dim adoCnnn As New ADODB.Connection
With adoCnnn
.ConnectionString = "Provider=SQLOLEDB.1;Data Source=LIBINGAO;Initial Catalog=ResearchData; UID=sa;PWD=;"
.Open
If .State = adStateOpen Then '以正确连接上服务器 LIBINGAO 上的数据库 ResearchData
.IsolationLevel = adXactReadCommitted
.BeginTrans
cSql = "CREATE TABLE AA (ID int,Name varchar(20),Age float)"
.Execute cSql, , adCmdText cSql = "INSERT INTO AA (ID,Name,Age) VALUES (10, '北方岛',20)"
.Execute cSql, , adCmdText
cSql = "UPDATE AA SET Name='华仙子' WHERE ID=10"
.Execute cSql, , adCmdText
cSql = "DELETE FROM AA WHERE ID=10"
.Execute cSql, , adCmdText
cSql = "ALTER TABLE AA ADD TEL varchar(20)"
.Execute cSql, , adCmdText
cSql = "ALTER TABLE AA DROP COLUMN TEL"
.Execute cSql, , adCmdText
cSql = "DROP TABLE AA "
.Execute cSql, , adCmdText
.CommitTrans
.Close
Else
MsgBox "SQL Server 数据库连接失败 !", vbCritical, MsgBoxTitle
Exit Sub
End If
End With
Exit Sub
errHandlerr:
If adoCnn.State = adStateOpen Then
adoCnnn.RollbackTrans
adoCnn.Close
End If
MsgBox Err.Description + ":" + CStr(Err.Number), vbCritical, "有错误发生 ..."End Sub
以上语句 VB6.0 + SQL Server2000 测试通过!
不过,在你实际测试中,应该将建表、添加记录、更新记录、删除记录、添加字段、删除字段和删除表放在不同的事务中,这样你可以在 SQL Server 中看到实际的运行结果!否则,程序运行时虽不报错,当由于在同一个事务中包含了上面的所有操作,等到事务结束时,所做的全部操作由于后面的 “删除字段和删除表” 操作导致全被删除!
且记!
只需将连接字符串改一下即可
请楼主试试!