469,934 Members | 1,824 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

How to execute sql command just like "Drop DATABASE " and "Restore DATABASE "?

Hi,all,

I want to execute SQL command " DROP DATABASE mydb" and "Restore DATABASE
....." in vb.net 2003. But it always shows error. If any body can tell me how
to execute sql command as above? Thanks a lot.
Best regard.
Risen

----
see my code below:

Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = ConnStr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.StoredProcedure
'change CommandType to CommandType.Text show error too.

selectCMD.CommandText = "DROP DATABASE RMS" 'how to execute
current sql command?
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("successful")
Catch ex As Exception
MessageBox.Show("error!")
Finally
conn.Close()
End Try
Nov 21 '05 #1
7 3641
You need to run it from a stored procedure. Create one that has the drop
procedure command and call it from your CommandText.

"Risen" <ri*****@21cn.com> wrote in message
news:Om**************@TK2MSFTNGP10.phx.gbl...
Hi,all,

I want to execute SQL command " DROP DATABASE mydb" and "Restore DATABASE
...." in vb.net 2003. But it always shows error. If any body can tell me how to execute sql command as above? Thanks a lot.
Best regard.
Risen

----
see my code below:

Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = ConnStr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.StoredProcedure
'change CommandType to CommandType.Text show error too.

selectCMD.CommandText = "DROP DATABASE RMS" 'how to execute
current sql command?
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("successful")
Catch ex As Exception
MessageBox.Show("error!")
Finally
conn.Close()
End Try

Nov 21 '05 #2
Risen,

Beside that crazy thing that you tell that it is a stored procedure I (if I
not oversee something) do it the same as you,

The simplest you can change to see what is going wrong is.
Catch ex As Exception
MessageBox.Show("error!")
Finally


MessageBox.Show(ex.ToString())

I hope this helps,

Cor
Nov 21 '05 #3
SQL doesn't like dropping databases when you have open connections to it.
Are you trying to drop a database you are currently connected to ? If so try
connecting to another database on the same SQL instance and (assuming you
have the permissions) drop the database with the sql script.

Gerry
"Cor Ligthert" wrote:
Risen,

Beside that crazy thing that you tell that it is a stored procedure I (if I
not oversee something) do it the same as you,

The simplest you can change to see what is going wrong is.
Catch ex As Exception
MessageBox.Show("error!")
Finally


MessageBox.Show(ex.ToString())

I hope this helps,

Cor

Nov 21 '05 #4
Gerry,

I use exactly the same, only in my connection string I don't of course not
tell to use a database.

Cor
Nov 21 '05 #5
Thanks,Gerry,
Thanks,all,

The problem has been resolved. It must change database ,and the best is
"master" database. And the database being droped or restored is not used.

Risen
My code is below:

Dim conn As New SqlClient.SqlConnection
Dim tmpstr As String
tmpstr = "workstation id=" & ReadStrfromReg("Wkst_Id") + ";" & _
"packet size=" & ReadStrfromReg("pkt_size") + ";" & _
"user id=" & ReadStrfromReg("SQL_User") + ";" & _
"data source=" & ReadStrfromReg("SQLServerName") + ";" & _
"persist security info=" & ReadStrfromReg("ps_info") + ";"
& _
"initial catalog=master;" & _
"password=" & ReadStrfromReg("SQL_PSW")
conn.ConnectionString = tmpstr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.Text
selectCMD.CommandText = "DROP DATABASE MyDB"
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("Drop Successful!")
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
conn.Close()
End Try

"Shawn" <sh**********@ccci.org> 写入消息新闻:%2***************@TK2MSFTNGP15.phx.gb l...
You need to run it from a stored procedure. Create one that has the drop
procedure command and call it from your CommandText.

"Risen" <ri*****@21cn.com> wrote in message
news:Om**************@TK2MSFTNGP10.phx.gbl...
Hi,all,

I want to execute SQL command " DROP DATABASE mydb" and "Restore DATABASE
...." in vb.net 2003. But it always shows error. If any body can tell me

how
to execute sql command as above? Thanks a lot.
Best regard.
Risen

----
see my code below:

Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = ConnStr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.StoredProcedure
'change CommandType to CommandType.Text show error too.

selectCMD.CommandText = "DROP DATABASE RMS" 'how to execute
current sql command?
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("successful")
Catch ex As Exception
MessageBox.Show("error!")
Finally
conn.Close()
End Try


Nov 21 '05 #6
Thanks,Gerry,
Thanks,all,

The problem has been resolved. It must change database ,and the best is
"master" database. And the database being droped or restored is not used.

Risen
My code is below:

Dim conn As New SqlClient.SqlConnection
Dim tmpstr As String
tmpstr = "workstation id=" & ReadStrfromReg("Wkst_Id") + ";" & _
"packet size=" & ReadStrfromReg("pkt_size") + ";" & _
"user id=" & ReadStrfromReg("SQL_User") + ";" & _
"data source=" & ReadStrfromReg("SQLServerName") + ";" & _
"persist security info=" & ReadStrfromReg("ps_info") + ";"
& _
"initial catalog=master;" & _
"password=" & ReadStrfromReg("SQL_PSW")
conn.ConnectionString = tmpstr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.Text
selectCMD.CommandText = "DROP DATABASE MyDB"
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("Drop Successful!")
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
conn.Close()
End Try

"Shawn" <sh**********@ccci.org> 写入消息新闻:%2***************@TK2MSFTNGP15.phx.gb l...
You need to run it from a stored procedure. Create one that has the drop
procedure command and call it from your CommandText.

"Risen" <ri*****@21cn.com> wrote in message
news:Om**************@TK2MSFTNGP10.phx.gbl...
Hi,all,

I want to execute SQL command " DROP DATABASE mydb" and "Restore DATABASE
...." in vb.net 2003. But it always shows error. If any body can tell me

how
to execute sql command as above? Thanks a lot.
Best regard.
Risen

----
see my code below:

Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = ConnStr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.StoredProcedure
'change CommandType to CommandType.Text show error too.

selectCMD.CommandText = "DROP DATABASE RMS" 'how to execute
current sql command?
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("successful")
Catch ex As Exception
MessageBox.Show("error!")
Finally
conn.Close()
End Try



Nov 21 '05 #7
Risen,

I told you already 2 days ago that you should *not* use a database for this
kind of operations. In other words the master database.

Strange that you did not see that

Cor
Nov 21 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Carl B. Constantine | last post: by
1 post views Thread by Andre | last post: by
1 post views Thread by tgru | last post: by
3 posts views Thread by Jon Jacobs | last post: by
7 posts views Thread by aixunix | last post: by
4 posts views Thread by Michael C | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.