SQL ADO Connection keeps dropping RRS feed

  • Question


    I have created a vsto script in vb.net 2005 which updates a sql table or creates a temp pivots data and returns the data to excel.

    If I create the script in VBA it works 100% of the time and works well.

    However if i convert the code to vb.net the connection drops after i execute the sql statement



    objcnn as new adodb connection

    objcon as new adodb command


    sql = "select * from table a into ##testtable where something = something"



    sql2= "select * from ##testtable"




    I get an error on the second execute that the server cannot be found.

    Im having to close the ObjCnn after the first sql and reopen the connection

    Has anyone got any thoughts why this is happening?

    If I run this locally I don't get this problem



    Friday, January 25, 2008 4:47 PM

All replies

  • As this is a question about data base connections, I'm going to move the thread to the ADO.NET forum.


    Friday, January 25, 2008 5:21 PM
  • Hello,


    Could you post your translation in VB.Net , please ?


    I may be wandering but i think that when you execute a command, the default of connection state is to be closed


    That's to say, when you don't pass a SqlConnection as parameter for the SqlCommand , a connection is open, the command is executed and the connection closed ( maybe i 'm telling a mistake ).


    It would be the reason of the closinf of the connection


    If you have something like :

    cmd.Commandbehaviour = CommandBehaviour.CloseConnection, you will have your connection automatically closed after the execution of the command.


    A stupid question , how have translated your vsto script in a C# or VB prog ?

    I have only VC# and VB Express Edition so no VSTO


    Have a nice day




    Friday, January 25, 2008 6:35 PM
  • Thanks for your help.


    I have checked with the software supplier and at the end of each command they do close the connection.
    I have to reopen the connection


    Thursday, January 31, 2008 9:08 AM