Connecting works, but Disconnecting and reconnecting fails on application role.

With the VB 2005 code below I can connect to one or more databases.  However if I connect to a database then connect to a different database, I can't reconnect to the original database.

            Con1.ConnectionString = ConnectionString
            cmd1.Connection = Con1
            Con1.Open()
            cmd1.Parameters.Clear()
            cmd1.CommandText = "sp_setapprole"
            cmd1.CommandType = CommandType.StoredProcedure
            cmd1.Parameters.AddWithValue("@rolename", "xx")
            cmd1.Parameters.AddWithValue("@password", "xx")
            cmd1.ExecuteNonQuery()

The connection string is like this.

Data source=IPAddress;Initial Catalog=DbName;Integrated Security=SSPI;Persist Security Info=FALSE;Network Library=DBMSSOCN
AlHal2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk HaestProject managerCommented:
Are you sure that you closed your connection before trying to connect/reconnet to another database ?

con.State == ConnectionState.Open


if con.State == ConnectionState.Open then
   con.Close()
end if
 
Con1.ConnectionString = ConnectionString
cmd1.Connection = Con1
Con1.Open()
cmd1.Parameters.Clear()
cmd1.CommandText = "sp_setapprole"
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.AddWithValue("@rolename", "xx")
cmd1.Parameters.AddWithValue("@password", "xx")
cmd1.ExecuteNonQuery()

Open in new window

0
AlHal2Author Commented:
I did close the connection.  Why did you have 2 equal signs for VB?  Was it a typo?
0
Dirk HaestProject managerCommented:
>> Was it a typo? -> yes, because I was thinking in c# while typing :)

Do you get any errormessage ?
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

AlHal2Author Commented:
Here is the message.
A severe error occured on the current command.  The results, if any should be discarded.
0
AlHal2Author Commented:
The error occured on the executenon query line.
0
Dirk HaestProject managerCommented:
Try to recreate your command-object. Perhaps that will solve the issue (new sqlcommand or oledbcommand, ...)
Con1 = New SqlConnection
cmd1 = New SqlCommand
Con1.ConnectionString = ConnectionString
cmd1.Connection = Con1
Con1.Open()
cmd1.Parameters.Clear()
cmd1.CommandText = "sp_setapprole"
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.AddWithValue("@rolename", "xx")
cmd1.Parameters.AddWithValue("@password", "xx")
cmd1.ExecuteNonQuery()

Open in new window

0
Dirk HaestProject managerCommented:
>> A severe error occured on the current command.  The results, if any should be discarded.

Can you check if the stored procedure you want to execute (sp_setapprole) is on the database you open !
0
AlHal2Author Commented:
The procedure runs when I connect to the database the first time, but not the second.  Therefore it's on there.
0
Dirk HaestProject managerCommented:
I think that your connection is successfull, but that there is a problem with your stored procedure.

Can you try this to see if your connection failed or that your stored procedure failed (for the second time).

Can you post the message you get when the error occured...
Con1.ConnectionString = ConnectionString
cmd1.Connection = Con1
try
  Con1.Open()
Catch ex as exception
  Messagebox.show ("Connection Failed with following exception: " & ex.tostring())
End Try
cmd1.Parameters.Clear()
cmd1.CommandText = "sp_setapprole"
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.AddWithValue("@rolename", "xx")
cmd1.Parameters.AddWithValue("@password", "xx")
try
  cmd1.ExecuteNonQuery()
Catch ex as exception
  Messagebox.show ("Stored procedure Failed with following exception: " & ex.tostring())
End Try

Open in new window

0
AlHal2Author Commented:
Error message attached.
ErrMsg.doc
0
Dirk HaestProject managerCommented:
Can you post the stored procedure. Apparantly it goes wrong in your stored procedure and not in your connection to the database !
0
AlHal2Author Commented:
Attached.  By the way it is OK with this VB6 code.

    Set oConn = New Adodb.Connection
    With oConn
        .Provider = "SQLOLEDB"
        .Properties("Integrated Security").Value = "SSPI"
        .Properties("Persist Security Info") = False
        .Properties("Data Source").Value = DataSource
        .Properties("Network Library").Value = "DBMSSOCN" '  
        .Properties("Initial Catalog").Value = Dbase
        .Open
    End With
oConn.Execute "sp_setapprole 'tunaTrawler', 'tun4Tr4wler'"

sp.doc
0
Dirk HaestProject managerCommented:
If it works with vb6, then we can be sure that the procedure is also correct (although we already knew that because the first time it's executed it goes correctly).

If you run the stored procedure directly on your database, does it give an error if you execute it multiple time behind each other (same parameters, ...)
0
AlHal2Author Commented:
When I ran it the first time like this it was fine.
exec sys.sp_setapprole @rolename='xx',@Password='xx'

When I ran it the second time I got this message.

Msg 2762, Level 16, State 1, Procedure sp_setapprole, Line 46
sp_setapprole was not invoked correctly. Refer to the documentation for more information.

Looks like it's to do with encryption or cookies.
0
Dirk HaestProject managerCommented:
It seems that way. When you tried to perform that in vb6, did you catch the error or did you have somewhere "on error resume next". That can explain why you didn't get any error in vb6.
In .net all the errors will popup.
You'll need to see what's wrong in your sp and alter it... Normally you should be possible to execute it twice on the database server. If that doesn't succeed, then it also won't work from .NET.
0
AlHal2Author Commented:
I found the answer on http://support.microsoft.com/kb/q229564/
I've added "Pooling=Fasle" to the connection string.

Could you explain in plain english what's going on?
0
Dirk HaestProject managerCommented:
Here is complete description of connection pooling
Connection Pooling for the .NET Framework Data Provider for SQL Server
http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx

In short words: by setting the pooling=false, it will always recreate the connection and never use an existing connection. The connections aren't removed directly on the sqlserver
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlHal2Author Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.