By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,928 Members | 1,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,928 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
7 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.