Application to run on server is giving an ODBC related error

[Coolboylp]

Dear,

I've developed a small HR program that connects to the SQL database which is on the local PC. I've created an ODBC connection to connect to this server and the program works fine. I've used VB 2008 Express edition to develop.

My concern is now I want to install this EXE on a server. ON the server, I've created the same databses and successfully created an ODBC connection on the server as well. Also created one shared folder. I copied all my EXEs and DLLS on to this folder. On the server I was able to execute the program without any problem.

From a remote client, I opened this shared folder and try to execute and it was giving me an ODBC related error, basically it was not able to detect the SQL connection.

What could be the problem? Is it anything do with my program or do I need to setup my server or the shared folder.

What I want to achieve is to have one EXE. The program did not throw any other errors.

PLEASE HELP. THANKS IN ADVANCE

[Shuja Ali]

First things first, when you are connecting to SQL server you do not use ODBC, rather you should use the classes present in the System.Data.SQLClient namespace to connect to SQL server. This is a better and efficient way of connecting to the SQL Server databases.

Second thing is that when you use System.Data.SQLClient.SQLConnection class to connect to the database server, you also need to provide a connection string which you can provide through an App.config file.

All this can be read from MSDN and they have nice samples too that explain how to do all this.

Coming to your question of why the error is shown, the error is shown because the ODBC connection that you have created is on the server and not on the system from which you are running the exe. In order to have the exe run properly, you will have to create the ODBC connection on that system too. But as I said, this is not the right way of doing this.

[sotoasty]

If you are using an ODBC connection, that will need to be installed on any Client PC that runs the EXE.

The EXE is being run from whatever computer it is double-clicked from. You application knows nothing about the ODBC connections you placed on the server.

If the SQL DB is SQLServer, I suggest you skip the ODBC connection and use a direct connection to the DB. This way, you can put your connection in an app.config file. This file will always reside with the Application so it would not matter where the application was run from, you would always have it point to the DB server.

ETA: What Shuja Ali said. I must be getting slow in my old age.

[Coolboylp]

Thanks Shuja and sotoasty,
Very much appreciate the advise...
In fact I created a separate ID for this database on the SQL server. I was able to create the ODBC conection on the server using this ID and was also able to login to SQL Management tool.

But when I tried to create an ODBC connection using the same ID, on a remote PC, it was not getting connected to the server for some reason. I checked the firewalls and so on, but no luck.

Therefore I thought if I can map a shared folder and create the ODBC on the server and also have the EXE on the server, the remote users would be able to run the application.

I do not know what could be the reason that I cannot connect to SQL DB from a remote PC. I checked for TCP/IP enabled options on the server and all looks fine.

If I can only get it connected, then I can have my program connect to the SQL directly using the ID and password as suggested by you.

I'm not familiar with app.config ???? Where/How ???

THANKS AGAIN for any ideas.. Perhaps the ID I created is having some issues...

[sotoasty]

What version of the DB are you using? How is security setup? Is it Windows authentication or user authentication? Can you connect to the DB server using the Server Management tool on your development PC?

You might have to check out the DB forum to see about getting help there.

[Shuja Ali]

If you are using SQL 2005 or above, by default it does not allow remote connections. So may be you would want to check on the SQL server whether remote connections are available.

Anyways, what is the exact error that you are getting. An error message is always helpful to identify what might be causing the problem.