Buggy .NET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a simple stored proc to ATTACH / DETACH a DB from SQL, but it
fails to work if I use ASP.NET to do any other DB stuff on the table.
ASP.NET establishes a connection with SQL server and WILL NOT release it,
and of course I can't detach the DB because ASP.NET is still using it. It
appears closing the objects and setting them to NOTHING doesn't really set
them to nothing. SQL still says in Process info:: .NET SqlClient Data
Provider is still active.. What gives???
Code Snippets:
Imports System.Data.SqlClient
Dim dReader As System.Data.SqlClient.SqlDataReader
tmpSQL.SQLDB.ConnectionString =
"database=Event;server=myserver;user=sa;pwd=xxx;"
tmpSQL.SQLDB.Open()
tmpSQL.SQLcmd.Connection = tmpSQL.SQLDB
tmpSQL.SQLcmd.CommandText = "select * from myTable"
dReader = tmpSQL.SQLcmd.ExecuteReader()
cmbSelect.DataSource = dReader
cmbSelect.DataTextField = "Type"
cmbSelect.DataBind()
dReader.Close()
dReader = Nothing
tmpSQL.SQLDB.Close()

tmpSQL.SQLDB = Nothing
tmpSQL.SQLcmd.Connection = Nothing
tmpSQL.SQLcmd = nothing
tmpSQL = Nothing
*****EVEN TRIED THIS*****
tmpSQL.SQLDB.Open()
tmpSQL.SQLcmd.Connection = tmpSQL.SQLDB
tmpSQL.SQLcmd.CommandType = CommandType.StoredProcedure
tmpSQL.SQLcmd.CommandText = "dotnet_DetachDB"
tmpSQL.SQLcmd.ExecuteNonQuery()
tmpSQL.SQLDB.Close()
tmpSQL.SQLDB = Nothing
tmpSQL.SQLcmd.Connection = Nothing
tmpSQL.SQLcmd = nothing
tmpSQL = Nothing
EVEN ADDED THIS LINE FOR WHAT"S ITS WORTH
tmpsql.dispose

Any Ideas.. Thanks
 
Its difficult, but i suppose the connection to the database is still
open.

You will have to close this connection and open another to say the
master database. Then run the stored proc. If its (the stored proc) in
your database, you will have to build a command text with the same
contents as the stored proc, or add it to master.

Of course, the user the connection is openning under will need the
appropiate permissions to dettach/attach a database and open master.

Regards
Michael
 
Back
Top