(vb+sql) and dsn question... 1

[sayeed]

Hi all...

this is my first question in this forum...

I have created a vb+sql combination application.. sql is running in one server and i am connecting through my workstation using a odbc (dsn)... the total thing is working for the particular workstation that i am working on... now i have created a package usign the wizard.. installed the package on another workstation in the same network... But as u guessed the odbc connectivity is not working with the new workstation.... it just cant connect to the server... now my questions are..

1. what should i do to create a single dsn and use it to connect my server in the same network?

2. if i want to make a package so that the app can runin other netwok.. what things should i need to do.. i mean... how should i configure the server part in the new network and the client part for the workstations?

3. what about oracle regarding the above scenario?

i know this could be a silly problem for u tech guys but this is a big problem for me... would ne one pls let me know the facts in detail about this explaining the practical scenario or provide me links where i can get informations on that....

thanks in advance..
waiting Desperately for a positive reply...

Sayeed

 

[Tranman]

This is not going to be much help (I cannot remember the particulars), but here goes.

About 4 years ago, I ran into the same problem. I looked into creating a "File DSN" on the server and using it, but after further investigation, I ended up putting logic into my program to interrogate the list of DSN's on the PC, and if the one I wanted was not present, I created it on the fly. I don't work the same place I did when I wrote that code, and do not have a sample, but I did find it on the web, and I remember that it was REALLY simple. Seems like it only amounted to 20 or 30 lines of code, (registry stuff), and included RegCreateKeyExA and something about an "ini" file.

You might try The Scarms:

http://www.thescarms.com

They have zillions of lines of sample code.

Good luck,
Paul

 

[BeggJ]

I would suggest scapping using ODBC connections to the SQL server and instead start using ADO (ActiveX Data objects). When you connect to the SQL server - use the OLEDB provider for SQL. This way ODBC connections don't need to be set up for the individual workstation.

 

[sayeed]

Beggj.. m8...

would u pls explain it a lil bit more...
i am using ado control in my app....
but would u pls tell me how can i avoid the setup for the individual workstation?

thanks
Sayeed

 

[Tranman]

Sayeed,
I think what Begg was talking about was using the OLEDB provider for SQL *instead* of ODBC. It goes like this:
If you are using the ADODC, click on the control, and in the properties window, click on the elipses in the "Connection String" property. When the "Property Pages" window opens up, where you have selected "Use ODBC.."
, choose "Use Connection String" instead. Click on the "Build" button, and just go through the tabs--Provider = OLE DB Provider for SQL Server, Connection = Database ID; etc. There is even a test connection button to verify the connection.
If you are not using the ADODC, go through the above steps anyway, and when you have created a good connection string, cut and paste it into the connection argument of your ADO Connection Object.
This method circumvents the need to use DSN's, and is really the preferred method for connecting to data providers.
Begg really does have a better idea.
Paul

 

[sayeed]

Tranman...

thanks.. file dsn is the answer that worked for me...
and beggJ thank u too...
u guys are realy helpful..

SaYeed

 

[MDA]

Hey Paul,

I am interested in why using ADO is the "preferred" method? I am by no means an expert but am curious of other advantages to using a method as you described, i.e. are there performance issues using DSN?

The projects I work on usually require the user to select their database,username and passowrd for a SQL DB. These companies often have several databases and the end users would only know the DB name by the DSN that was created for them by the IS department.

The DB cannot be "hard coded" into the vb app, as it would very by client and user. How would you get around the problem of having some sort of way a user could select a database to use?

Thanks for your input...

Best regards,

MDA

 

[Tranman]

Hi MDA,
I guess I was not too clear about what I meant when I said "preferred". What I meant was that the ADO is preferred to the ADODC--mostly because of performance considerations. The environment where I work consists primarily of 4 Oracle databases, each one with 18-25 tables, and the smallest table in any of the 4 is about 450K
rows. Everything you do to these monstrosities, that involves selecting data is slow, and the ADODC is slower.

The second consideration is ODBC vs OLE DB layer. The ADO and the ADODC can both connect to a data provider through either ODBC or OLE DB. I have tried both, and in this environment, OLE DB is faster, and provides the added advantage of being able to code SQL in the "native" syntax of the database.

I actually have an app that uses OLE DB, and lets me select which database to connect to. Of course, I know the name of the database, but it could be an alias that was interpreted in code. All I do is to build an appropriate connection string in code and use it when I open the ADO connection. I suppose you could do this with the ADODC, too, by setting the Connection String property, but I only use ADODC for "quick and dirty" stuff.

It sounds like you have to deal with a lot more variety in your data environment than I do. I have been trying to envision a group of users that pick from DSN's to choose databases. Are you front-ending SQL with Access or something?

Anyway, thanks for writing back. I really enjoy being able to correspond with other technical people, and never fail to learn something new when I visit this site. Thinking about your message gave me an idea of how to consolidate the functionality of two of my programs into one.

Paul (Tranman)

 

[MDA]

Excellent Paul,

Thank you for your reply!

As you have experienced, I learn something new every time I scan the messages here. It seems that I can always depend on this site for answers to my most puzzling questions.


Regards,

MDA