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

[Risen]

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

[Shawn]

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

[Cor Ligthert]

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

[dotnetnewbie]

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

[Cor Ligthert]

Gerry,

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

Cor

[Risen]

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

[Risen]

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

[Cor Ligthert]

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