why the connection is closing?

hai all,

i have a dll component which is used to connect to the database. i am using this dll using DCOM.

In my DLL, i have taken a ADODB.Connection globally. while at the time of login i am creating a connection to the database.
after that if i open any screen in my client application, it will call that particular class in DLL. it will use the same above connection to communicate with the database.

while logging in from the client machine, the connection is successfully created. after that if i open any screen, it is giving an error "reference to a closed or invalid connection". why?

but the system is working fine, if both server and client are in the same machine.

pls helpme

1. why u r using global connection instead of connection per method invocation?
2. what technology u use (COM+, ActiveX EXE, DLL Surrogate?)

thank you deja,

i am using dll surrogate. i have created a seperate class and i am using one method in that to connect. but the connection is global variable.

i don't know why it was becomming empty?
is there any reason or any property to set?

pls help me

why don't u use COM+?
it is very much easier dealing with COM+ than DLL Surrogate

hai deja,

i have activex dll and standard exe right now. what else i need to convert it into com+.

actually i am new to com+. suggest me a good site and where the documentation will be available?

i need your help to convert it. can you do it?

u need nothing to convert.
simply, open Component Services MMC (Start->Settings->Control Panel->Administrative Tools), expand My Computer then right click on COM+ Application and select New Application.
press Create an empty application, enter the name (leave Server Application default option selected), press next, now if u have NTFS with NT authentication, specify the user/password, press next and finish.
now u can see your new application in the tree. expand the your COM+ application, than right click on Components and select New Component. press next and choose Install new components. select the DLL file from it's location, click next and finish.
tha's it. that almost all the work.
also, make sure that you check each ActiveX DLL Unattended Execution and Retain In Memory options at the Project Properties to prevent memory leak etc.
also make sure to use Binary Compatibility at the Component tab (also in Project Properties).
one more thing, to give your clients an access to your COM+ application remotely, go back to Component Services MMC, right click on your COM+ application and choose Export. press next, then specify the installation file (name and location), and select the option (at the bottom) Application proxy (this tells COM+ to configure the client that it will use COM object at the server (meant that the COM object will be instantiate the server and not at the client process)), click next and finish.
now, take the MSI file tha COM+ have created, put it on the client machine, and run it, it will do the rest of the work automatically.
there are a lot more about COM+, like Transaction, JIT Activation, Queued Componens, LCE etc.
COM+ gives u a lot of useful services but for this start it's enough.
more on COM+ here
http://msdn.microsoft.com/library/de...asp?frame=true

http://msdn.microsoft.com/library/de...asp?frame=true

hai deja,

i am really thankful to you. i have followed your steps, but the problem is while exporting, "application proxy" was disabled.
so what i have to do now?

and onemore thing is my client is in windows 95 machine and the server is in windows 2000 professional.

is there any problem with both platforms?

i think u had a mistake and instead of creating Server application u created Library application, the difference is that Library application run inside client process (for instance other COM+ application), and Server application run in it's own process. it is obvious why it doesn't give u to export Proxy from Library application - because it cannot run the application in the server (from the client side).
make sure when u create your COM+ application to specify that is a Server application. try to create the COM+ application from the beginning.

thank you deja,

i have exported the component using server application. i have taken this to another windows 200 machine and tried to install it there. but it is giving an error while installing.

actually what i did is , i have a activex dll project. i have created a dependancy file. and for my Standard EXE(client) application i have created a setup program without adding my dll file.

i take this setup and installed on the client machine, after that i am trying to install the client.msi (which was exported as application proxy) in client place.

but it was terminating after giving an error.

can you pls help me.

one more thing deja,

after getting above error (in my previous reply), if i tried to make the dll of my server application after compiling the dll finally it is giving error "permission denied".

why?

Try my DLL then... it is compatible to Access, MSSQL, Oracle and ODBC.

I assume you know how to register the dll.

In the zip file, it contains the DLL (samdb.dll) and a sample program with source code and sample DB for you to test and find out how to use it.

Hereunder is an example for connecting to SQL Server:

Code:

---

    Dim WithEvents x As Samdb.dbconnection 'declare object with events
    Dim rs As New ADODB.Recordset
       
  Set x = new Samdb.Dbconnection
  With x
      .DatabaseType = MSSQL
      .Path = "server1"              'your SQL server name, eg. server1
      .InitCatalog = "pubs"
      .LoginID = "sa"
      .LoginPwd = "123456"
    End With
  x.DBConnect

---

This will return you an open db connection "x.dbconn"

hai samsir,

thank you for your code. but case here is different.
i am attaching one zip file. pls solve that problem in that zip.

it has two folders. one is for server and another is for client.
create the dll for server, and exe for client.

create a dependancy file for server and package for client.
install the client in the same machine. and see it is working fine.

but if you install the client package in another machine, and using DCOM(DLL Surrogate) the connection is closing.

why? if you have any doubts pls ask me.

sorry for not replying, i wasn't connected at the weekend.
what was the error u have got (source, number, desc etc.)?

To NeedHelp:

You need to check if your component is available.

go to command prompt and use "dcomcnfg" to see if you can locate your component. If not, your component is not ready for remote call.

hai deja,

i already posted some problems and waiting for reply. these are the errors i am getting

Quote:

---

have exported the component using server application. i have taken this to another windows 200 machine and tried to install it there. but it is giving an error while installing.

---

so pls tell me what went wrong,

hai deja,

it was just unexpected error. contact your application vendor.
that's all
so i didn't get any info based on that error

what to do?

hai samsir,

is my code working fine at your place?

unexpected error doesn't help me very much to figure out what is the problem.
but, at least i can recommend u to install the latest SP for win2k (through www.microsoft.com/download or www.windowsupdate.com the latter is better beacuse it recognize your components and update them accordingly) on both computers, and to try again to create the COM+ application, create export etc.
also, try to install the proxy (the MSI) on another computer, and see if it works.

Deja,

I am giving the steps. first let me know whether i am doing the right or wrong.

1) I have one ActiveX DLL(server).
2) I have created the Dependancy file for that.
3) I have one Standard EXe(Client).
4) while creating the setup for client, i am giving the .VBR file of the server and giving the servername or IP. I am unchecking the dll file (because the dll should not be in the client place. it remains in the server).

5) After creating setup for the client. i am installing it there in the client system.

6) then i am opening the component services, added a new application (which is my dll) and then the system is automatically detecting the classes in my dlls.

7) after that i am exporting the server application and application proxy.

8) now i am taking the application proxy and installing it in the client machine where my client application is installed.

is this the right process? is there any change in that pls tell me.

thank you

hi,
first of all, no need for VBR files in the setup package (and the server name too).
the VBR is for clients that do not have proxy installation from COM+ application, in your case u need two things:
1. your client setup package
2. the proxy installation file (MSI) from the COM+.

after u install the client package, run the MSI.
u also have to consider DCOM installation on win95 or win98 systems.
theoretically, u can add DCOM installation to the setup package (of the client), but if u use package and deployment wizard, be aware that it is not much cleaver, and it override existing DCOM (if it already installed) on the client machine, even if the client have newer version.
if u r using better tool (InstallShield for instance), u can check the registry to see what version of DCOM the client have (if he have at all), and proceed accordingly.

hai deja,

before i got reply from you. i tried to install application proxy. i got the same error.

after that i opened component services, and in that if i click on "computers" then i got an error like

An error occured while processing the last operation
Error code 80040154 - Class not registered

The event log may contain additional troubleshooting information.

what is this?

reinstall Compomnent Services (through Start->Settings->Control Panel. Add/Remove Programs->Add/Remove Windows Components) and reboot the system.
one more thing, if u already register your DLL before he proxy installation, try to use "regsvr32 /u" on that DLL (that DLL is include of course in your COM+ application).
after that try to reinstall client setup (be sure to remove the VBR from the dependencies in the package installation) and reinstall also the proxy (MSI).

Deja,

i donno what happend?. but i tried to install component services using add/remove programs(add/remove windows components). finally i installed all the components. but no way.

temporarily i am doing this com+ on another machine. i followed your steps and exported the both. now i took the application proxy and installed it in another machine. while installing it gave me another error "Error while registering the COM+ application. contact support personal".

what is this? is there any impact of users and roles while installing?

i'm sorry but i've got to go now,
i didn't encountered the problems u just mentioned, but try to lookup in support.microsoft.com.
i founded one KB (http://support.microsoft.com/default...b;en-us;308940) that maybe have something with your problem.
i try to contact with tomorrow.

See if this help a bit

http://support.microsoft.com/default...b;en-us;194636

Hai deja,

sorry for late. You have given me a good site. i have followed all the steps in that. finally i came to know that there is some registry permission problem due to crystal reports 8 installation. here in my system i have installed crystal reports 8. so it will change the registry premissions. that is the reason i am getting an error while installing com+ application.

so i have contacted crystal reports 8 support team. they have given me some instructions to run scr_webfix8.exe in my system. they said that it will change the reqistry and we can install com+ applications.

but no use. again i send a mail to crystal reports support team stating abt the problem. but they are also not able to tell the solution.

the same .msi file is installing successfully in another system which doesn't have crystal reports 8. so i am in a dilemma. what can i do at this position?

if you have any idea regarding this, pls reply me.

thota

hi,
i never worked with crystal report, but if u have legal copy of that software you have all the rights to get full support to that problem from them.
if you already use crystal in your system, u must try to get help from them, if not, try look for other options.
try also to search google groups, maybe u get some info there.

good luck

hai deja,

thank you for your support. in my system i am not able to execute the .msi file. so i am using my system as a server. i have installed client in another system.

but again i got the same problem. if i use the global connection, it is not working. everytime and in every method i need to open the connection and close it.

is there any method to use global connection in com+?. i will use open the connection once and i will close that connection only at the time of closing the application. is it possible?

in all my classes i want use the open connection only. i don't want to open the connection again. pls tell me if there is it good or not? if it good then give me some idea..

it is very bad to use global connection.
connection is very expensive thing, suppose u have client server application that each client holding connection for the entire application lifitme (as u want), now, it is only a matter of time until your DBMS will crash as soon as few dozens of concurrent users will be connected.
if your connection code is inside the client code, only the DBMS will hurt, but if u put the connection code inside the COM object that reside in the COM+ application, the COM+ server will hurt badly too (this is only true if u have persistent reference to the COM+ object in the client application - i.e. u hold global object of the COM+ object in the client).
the reason (i assume) that your connection r closing is because u don't have global object of the COM+ application at the client. u probably create an object in function or procedure scope, and when u exit the function/proc the object reference go's out of the scope, and COM+ (not immediately) remove tha object from memory, and of course the connection r closing too.
u have to understand that if u r working with COM+ as an application server, that is intend to support many clients concurrently, u must consider memory and performance issue.
today u r working in a LAN with windows forms client, tomorrow u will have to support remote clients using browsers, that working through modem or i don't know else, and performance issue will become top priority.
u must remember that the COM+ server (that acting as an application server) have limited amount of memory and resources, and must handle many users.
so i say (from many good others developers, with a lot of experience, include MSFT that also explain that in a methodology called DNA) hold connection only at function/proc scope. that's it.
i know that openning and closing a connection is also very performance hurt, but if u r working with only one connetion (i.e. the same one account in the DBMS), u will gain performance by OLEDB connection pooling, that improve performance very well.
for more info about DNA:
http://msdn.microsoft.com/library/de...asp?frame=true

hai deja

now i am converting all my code in com+. but when i observe, if two client are there for a single application. it is creating two objects of the class. i don't want like that. i want only sigle object should serve all the clients.

how to make my class like that. i mean if one object is already there in server, the second client should not create another. instead it should use the existing one.

pls help me.

first of all, of course COM+ create instance per client, it is to achieve load balancing. suppose it will create only one instane, and all the clients have to wait patiently to execute they method.
if u have heterogenous methods in your class, i mean, if u have some short operations and some long, one user can invoke a long method that takes couple of minutes, and by that blocking dozens of users from executing short method that takes only a few seconds.
in a distributed system, that must support many users concurrently, u can't do such a thing.
now, the thing that u want is called Singleton.
A singleton is an object that is the one and only one instance of its class.
i really don't figure out why do u need only one instance of your object in the server.
technically u can write such an object, that support multiple accesses to the object concurrently. it is called MTA object or Multi Thread Apartment object. i don't know how is your knowledge about COM apartment, but i can tell u one thing, u can't create MTA object with vb but only STA (Single Thread Apartment).
it mean that VB do not give the option to write multithread object.
u can do it with C++ for instance.
the bottom line is that if u need to share data between the clients (i assume that the thing that u want) u can use the SPM (Shared Property Manager).
the SPM is a COM+ mechanism, that gives u the option to store some global data and share it among multiple objects in the same process.
read more on SPM in the COM+ SDK (MSDN).

hai deja,

thank you. but let me explain my situation. i have one collection class which holds all the objects of some class. actually each object holds one record of a table. so finally my collection class contains all the records in a table.

now i want to create only one objcet for this collection. because this holds the data in a table, if one client changes any data, the other clients should be able to see the changes.

if it creates one instance for one client, if one client changes the data in one object, the other object contains the old data. is it?

so how to solve this problem?

one more doubt deja,

i have used withevents for my objects to catch the events from the dll. but when i try to run the form which has a declaration like

dim withevents obj as someclass.

it is giving an error like 'run time error 0'. and it is giving this error in windows 95 system only. in other windows 2000 machines, i am not getting this error.

why?

i'm not sure about the technology you r working with, but if u work with real DBMS like Oracle, SQL, DB2 etc. they already have such mechanism to deal with data integrity, data consistency etc.
it seems to me that u r trying to invent the wheel from the start, i'm sure u heard and used with ADO, because it gives u exactly these things u asked for.
ADO support various of cursor types and lock types, but it depend on the provider weather it support those method or not.
u can check with the provider documentation what e allows u to do.
for example, u can set the CursorType property to adOpenDynamic to achieve full transparency of the data in a table, i.e. all data manipulation (insert, delete, update etc.) are visible to all users.
Jet (access engine) support this kind of cursor, but Oracle, for instance, doesn't.
but, again, holding an open cursor against the DBMS for each user is a BAD thing when we talking about ditributed application, that working with many users simultaneously.
i don't know about your knowledge with computers, but u must read some books about ditributed computing or COM+ to have a solid understanding about such issues.
the reason i told to move to COM+ is to avoid such thing as opening cursor from client.
when moving to write application with layers concept, the middle tier that represent the bussiness logic, also contain data access code, so the user will never be aware of data access, by that u r saving extra installation at the client station of unnecessary drivers, and can use OLEDB connection pooling at the server. also if u will move tomorrow to new DBMS, u will not have to update the clients, but only the data services DLL that reside in the COM+ application.
i would give the users read only data grid, i mean, they cannot change the data straight in the grid, but they will must double cliked on some row, and then they will see a free form like screen, where there they could edit the record.
that because i don't wont to use an open cursor.
data integrirty is also a issue to deal with.
suppose two users can see the data of some table.
now, one user decide to delete a record, few second after that, the other user want to change something in that record, when u will try to issue an update statement of that row with the key that u have, u will get an error "the record doesn't exist".
now what u have to do?
u have a few options, like, to check if the record being update already exist, and if not, to supply the user some friendly message, but i am not intent to guide u how to build a system from scratch, but only to give u some tips, and to solve some problems.
i encourage u to start with a good book or some course on those issues, then writing the application.
if u need more help i'm here.

hai deja,

thank you. really you have lot of patience. its my luck. i will try like that.

i got one more problem. i have used withevents in my dll to raise events at the client place. but after installation. while running the forms it is giving runtime error 0.

why?

hai deja,

after carefully observing your points i got a doubt that am i doing in a

right way or not. so i am sending zip file. if you have time then pls check

it and tell me is it a right way or not?

main points to check are

1) class contains the methods to save, update and delete a particular record.
2) collection holds the all the records. i am loading all the records by

using getlist method in collection.

3) comming to the form, i am calling getlist in form_load. and doing all

operations on that collection and class.

pls check the saverecords, cmddel_click. so pls tell me is this the right way

to handle database projects?

thank you.

in addition to my previous comment, there r few things to concern.
i still don't figure out why don't u create a separate class for data access.
you have also some business logic mixed with the UI code (like in the sub cmdSave_Click).
another bad habit is to check connection state, the close it before open it. if u already said that u r working with global connection why r closing and opening it all from the start? it is a waste of time.
i still recommend u not using global connection, it is real bad.
at least, u can move all the SQL's to some module, then when updating, inserting, deleting etc. u just use the proper method (from the data service class that u will create of course) to issue that SQL.
what i mean is, create data service class with, let's say method called RunSQL(ByVal strSQL As String) that take a string parameter strSQL represent the SQL statement, in that method, declare local connection object, then open it and issue the SQL parameter, then close the connection and that's it.
consider the following code:
DLL name: <my app name>_DB (for instance: Agent_DB)
Class DBHelper

Public Sub RunSQL(ByVal strSQL As String)

On Error GoTo errorHandler

Dim cn As ADODB.Connection
Set cn = New ADODB.Connetion

cn.ConnetionString = <your connection string>
cn.Open
cn.Execute(strSQL)
cn.Close
set cn = Nothing
ObjectContext.SetComplete

Exit Sub

errorHandler:
ObjectContext.SetAbort
' do some error handling
End Sub

DLL name: <my app name>_BUS (for instance: Agent_BUS)
Class Agent

Sub Update(ByVal strFieldValue As String, ByVal lngKey As Long)

On Error GoTo errorHandler

Dim strSQL as String
Dim objDBHelper As Agent_DB.DBHelper
Set objDBHelper = New Agent_DB.DBHelper

'check validation of data
'if it's fine,
strSQL = "update my_table set my_field = " & strFieldValue & " where my_key = " & lngKey
objDBHelper.RunSQL strSQL
Set objDBHelper = Nothing

errorHandler:
' do some error handling
End Sub

that code above, is realy far from good application design, but it gives u part of the big picture how to separate you application into logical layers.
if u realy want to leran how to do things correctly read the sample application called Fitch & Mather Stocks 2000 from microsoft. it represent the DNA concept in details. go for it.

hai deja,

actually my doubt is.. is it good to hold the references of the objects of a class in a collection? each class holds the data of one record in the datatbase table.

and one more doubt is that can we use recordsets for passing the information from server to client?

thank you

the answer to the first is: NO. it is very bad to hold each record in a separate object. it is waste of memory and resources.
i can't figure out why ADO.Recordset isn't enough for u to hold your table population.

the latter answer is, of course it can.
u have a few options to transfer data between layers in MSFT platform like, Recordset, XML Stream, Arrays, Strings etc.

the best depend on the data being transfered.
in the most cases, Recordset will be the better choice to move bulks of data.

hav a good day.

ok deja,

thank you for your valuable information. i am facing another problem.

actually i have a public variable in a class. i am calling one function in that class from UI. while in the function i am assinging a value to that public variable depending on some condition. after executing the funtion i am trying to access the public variable. it does not containt anything. the code is like below.

public ID as integer

public function sum() as boolean
on error goto errh


id = <some value>


sum = true

exit function
ErrHandle:
sum = false
end function

from UI

dim cls as theaboveclass

if cls.sum then
msgbox id
end if

but it is not showing any thing. why? might be all these problems are very small to you. but for me it is very big as i am new to com+. pls be patient with me.

thank you