locked
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

     

    ie

    objcnn as new adodb connection

    objcon as new adodb command

     

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

    ObjCnn.Execute(sql)

     

    sql2= "select * from ##testtable"

     

    ObjCnn.Execute(sql2)

     

    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

     

    Thanks

    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

    Thanks

    Thursday, January 31, 2008 9:08 AM