472,325 Members | 1,025 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,325 software developers and data experts.

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 3743
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Carl B. Constantine | last post by:
I'm trying to move a database from one machine to another. I dumped the database and successfully loaded it on one machine, but I can't do it on...
1
by: Andre | last post by:
Hi, I have a database (or better: used to have) and backup consisting of - the initial (complete) database - all log files since then (or so I...
1
by: tgru | last post by:
I am trying to drop a database, but keep getting the following error. "cannot drop database 'blah' because it is currently being used for...
3
by: Jon Jacobs | last post by:
I attempt to back up a database on one server and restore it on my local machine. This is what the query text looks like: restore database...
7
by: aixunix | last post by:
Hi Experts, I am working on DB2 UDB on AIX and I get following OSERR message in db2diag.log when I drop database. Well, the database can be...
4
by: Michael C | last post by:
Hi All, I'm trying to drop an sqlserver database from c# but can't because it is claiming it is in use. As I don't have a connection to it it...
3
by: Okonita | last post by:
Hi all, I am having problem completing this restore operation. "db2 restore database AAMI01 from /pap/data/backups taken at 20071002130554 to...
5
by: pedalpete | last post by:
I think I somehow REALLY screwed up my database. I get no return making queries, REPAIR said it was all ok, I mysqldump was hanging, etc. etc, lots...
1
by: raaman rai | last post by:
Hi experts, i dumped my MySQL database and now when i restore it in another machine i cannot restore two of my tables. It restores upto some point...
0
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.