Private Sub mnuReCreateDatabase_Click()'为主程序一个菜单项,重建数据库Set con = Nothing'为当前正在使用的ADO连接对象设置为空
Set com = Nothing'为当前正在使用的ADO命令对象设置为空
Set Rs = Nothing'为当前正在使用的ADO记录集对象设置为空Call initialize'用来完成初始化建表建存储过程的字符串数组
Call createDatabase'建库操作,过程见下disenableMenuAndToolbar'重新初始化主程序的菜单栏和工具栏
frmMian.StatusBar1.Panels("strMsg").Text = "当前操作员: "
Call connectionSQLServer'重新连接数据库服务器
If Rs.State = adStateOpen Then Rs.Close'以下代码用于设置登陆命令是否可用
Rs.CursorLocation = adUseClient
Rs.Open "select * from Operator", con, adOpenDynamic, adLockOptimistic
If Rs.RecordCount <= 0 Then
frmMian.Toolbar1.Buttons(1).Enabled = False
frmMian.mnuOperatorLogin.Enabled = False
End IfEnd SubPrivate Sub createDatabase()'建库操作
Dim i As Integer
Dim cn As ADODB.Connection
Call getSQLServerName
Dim strCreateDatabae, strDropDatabase As String
strDropDatabase = "if exists (select 1 from sysdatabases where name='onlinebankingdb')" & _
"drop database onlinebankingdb"
strCreateDatabae = "CREATE DATABASE onlinebankingdb " & _
"ON (NAME = onlinebankingdb_dat," & _
"FILENAME = '" & App.Path & "\onlinebankingdb.mdf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )" & _
"LOG ON (NAME = 'onlinebankingdb_log'," & _
"FILENAME = '" & App.Path & "\onlinebankingdblog.ldf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )"
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=SQLOLEDB;Data Source=" & strServerName & ";User Id=sa;Password=;Initial Catalog=Master"
.Open
.Execute strDropDatabase'程序执行到这时就不行了
.Execute strCreateDatabae
.Execute "use onlinebankingdb"
For i = 1 To 10
.Execute createTableScript(i)
Next
For i = 1 To 10
.Execute createProcedure(i)
Next
End With
Set cn = Nothing
End Sub现在的问题是我删除不了当前的数据库,提示当前数据库正在使用,怎么办,求解决方案
Set com = Nothing'为当前正在使用的ADO命令对象设置为空
Set Rs = Nothing'为当前正在使用的ADO记录集对象设置为空Call initialize'用来完成初始化建表建存储过程的字符串数组
Call createDatabase'建库操作,过程见下disenableMenuAndToolbar'重新初始化主程序的菜单栏和工具栏
frmMian.StatusBar1.Panels("strMsg").Text = "当前操作员: "
Call connectionSQLServer'重新连接数据库服务器
If Rs.State = adStateOpen Then Rs.Close'以下代码用于设置登陆命令是否可用
Rs.CursorLocation = adUseClient
Rs.Open "select * from Operator", con, adOpenDynamic, adLockOptimistic
If Rs.RecordCount <= 0 Then
frmMian.Toolbar1.Buttons(1).Enabled = False
frmMian.mnuOperatorLogin.Enabled = False
End IfEnd SubPrivate Sub createDatabase()'建库操作
Dim i As Integer
Dim cn As ADODB.Connection
Call getSQLServerName
Dim strCreateDatabae, strDropDatabase As String
strDropDatabase = "if exists (select 1 from sysdatabases where name='onlinebankingdb')" & _
"drop database onlinebankingdb"
strCreateDatabae = "CREATE DATABASE onlinebankingdb " & _
"ON (NAME = onlinebankingdb_dat," & _
"FILENAME = '" & App.Path & "\onlinebankingdb.mdf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )" & _
"LOG ON (NAME = 'onlinebankingdb_log'," & _
"FILENAME = '" & App.Path & "\onlinebankingdblog.ldf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )"
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=SQLOLEDB;Data Source=" & strServerName & ";User Id=sa;Password=;Initial Catalog=Master"
.Open
.Execute strDropDatabase'程序执行到这时就不行了
.Execute strCreateDatabae
.Execute "use onlinebankingdb"
For i = 1 To 10
.Execute createTableScript(i)
Next
For i = 1 To 10
.Execute createProcedure(i)
Next
End With
Set cn = Nothing
End Sub现在的问题是我删除不了当前的数据库,提示当前数据库正在使用,怎么办,求解决方案
解决方案 »
- 关于打包安装时注册DATAGRID控件的问题,高分求教
- 如何使用filesystemobject?
- 求助!!请问怎么回答才能蒙混过关(面试VB初中级程序员)
- 请教VS2008如何设置Execl单元的对齐方式
- SQL_SERVER 数据库恢复
- 高手瞬间就可以搞定的问题
- 为什么我用VB打包后安装,出现"未知错误,ADODC未找到"的错误呢?急急急急不可待!!!
- VB有什么函数直接可以获取某一字串中某一字符的个数?
- 用VB开发数据库请求指点!!!!!!(大家介绍一下经验好吗)
- 有关读取一格式化文本文件内容到数据库中的速度问题:(请大伙帮帮忙,谢谢!)
- 简单问题?在线等
- 怎么让shape放到picturebox的上层?
cn.close
set cn=nothing
在进行删除
1:你的连接必须是打开另一数据库,推荐用master数据库
2:必须关掉所有其它找开该数据库的连接
可用如下方式关闭use master
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status =0
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end --用法
use master
exec killspid '数据库名'
3:然后就可以删除了。
iSql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
While iRe.EOF = False
iSql = "kill " & iRe(0)
iDb.Execute iSql
iRe.MoveNext
Wend
iRe.Close
Private Sub createDatabase() '建库操作
Dim i As Integer
Dim cn As ADODB.Connection
Call getSQLServerName
Dim strCreateDatabae, strDropDatabase As String Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=SQLOLEDB;Data Source=" & strServerName & ";User Id=sa;Password=;Initial Catalog=Master"
.open
End With
'=============================================================================
'**
'** 关闭用户进程的处理
Dim iSql$, iRe As ADODB.Recordset
iSql = "select spid from master..sysprocesses where dbid=db_id('onlinebankingdb')"
Set iRe = New ADODB.Recordset
iRe.open iSql, cn, adOpenKeyset, adLockReadOnly
While iRe.EOF = False
iSql = "kill " & iRe(0)
cn.Execute iSql
iRe.MoveNext
Wend
iRe.Close
'**
'=============================================================================
strDropDatabase = "if exists (select 1 from sysdatabases where name='onlinebankingdb')" & _
"drop database onlinebankingdb"
strCreateDatabae = "CREATE DATABASE onlinebankingdb " & _
"ON (NAME = onlinebankingdb_dat," & _
"FILENAME = '" & App.Path & "\onlinebankingdb.mdf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )" & _
"LOG ON (NAME = 'onlinebankingdb_log'," & _
"FILENAME = '" & App.Path & "\onlinebankingdblog.ldf'," & _
"SIZE = 1MB,FILEGROWTH = 10% )"
With cn
.Execute strDropDatabase '程序执行到这时就不行了
.Execute strCreateDatabae
.Execute "use onlinebankingdb"
For i = 1 To 10
.Execute createTableScript(i)
Next
For i = 1 To 10
.Execute createProcedure(i)
Next
End With
Set cn = Nothing
End Sub
'保证其它的连接也关闭
strdbname=cn.DefaultDatabase
cn.execute "use master"
cn.execute "drop database " & strdbname
1、这个存储过程是建在自己这个数据库吗?
2、是否可以把use master 放到存储过程里面去?
3、db_id('''+@dbname+''')' 中有引号是怎么回事? 是不是先一个单引号后一个双引号,最后语句完成时又加一个单引号,形成一条完整的SQL语句?我是想把这个过程弄弄清楚,谢谢你们的热心相助use master
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status =0
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
最好建在master数据库中
2、是否可以把use master 放到存储过程里面去?
当然可以,但你的存储过程是建在master数据库中的,要去那里打开或运行
3、db_id('''+@dbname+''')' 中有引号是怎么回事? 是不是先一个单引号后一个双引号,最后语句完成时又加一个单引号,形成一条完整的SQL语句?
全是单引号,在单引号中的字符如果是单引号,需要用两个单引号来代替
eg: select 'abc''abc'的输出结果为 abc'abc