connecting with SQL Server

[shivkumar]

hi experts
i have a little problem for u

I have never connected my project with SQL Server
I want to use my project with multiple databases prepared in SQL Server2000. I am using the following code to connect
_______________________________________________________
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSql As String
Set cn = New ADODB.Connection

' Establish a connection to database
'-----------------------------------
With cn
.Provider = "sqloledb"

' Database connection (replace server2003 with your servername)
'-------------------------------------------------------------
.ConnectionString = "Data Source=server2003;Initial Catalog=Northwind;Integrated Security=SSPI;Persist Security Info=False;"

.Open
End With

End Sub

_______________________________________

but I am not able to connect, neither I get any error message nor I am getting connected.
My sql server is protectd by user id and password also.

Please help me.
I am waiting


shiv

[Shuja Ali]

The connectionstring for connecting to SQL Server should look like this

myConnection.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;User ID=myUserID;Password=myPassWord;Initial Catalog=myDataBaseName; Data Source=myDataSQLServerName"
myConnection.Open

Remember you need to provide the Server Name, Database Name, UserID and Password in the connection string before you can open it.

[shivkumar]

thanx shuja


with best regards
shiv

[T2T2]

We run VB6 applications connected to SQL Server 2000.
The issue I have is that we need to move the databases to a different server from time to time.
Mainly for maintenance reasons, the DBA has memory leaks and other issues forcing our application database to relocate to different servers.

Is there a way in VB code to reference a database without specifically referencing the server within the program code ( ConnectionString ).

I don't understand odbc too well, but can this be used as a pointer to the database, so the VB code always refers to the same odbc reference?

The DBA would like to have the flexibility to do house keeping without engaging programmers to modify and deploy VB applications every time.

Thanks in advance .

     cn.ConnectionString = "uid=xxxx;pwd=xxxx;driver={SQL SERVER};" _
    & "server=SCB3AWENG3;database=SCB3AWDATA;dsn=SCB3AWDATA,,connection=adConnectAsync"

[rudraksh]

The connectionstring for connecting to SQL Server should look like this

myConnection.ConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;User ID=myUserID;Password=myPassWord;Initial Catalog=myDataBaseName; Data Source=myDataSQLServerName"
myConnection.Open

Remember you need to provide the Server Name, Database Name, UserID and Password in the connection string before you can open it.

Hi Shuja Ali,
just want to make a point.When you are using password in your connection string ,I think you should set persist security info =true,otherwise it will not work at all.
Rudraksh

[pranay]

write it in one of the module and call it where ever you need a connection to be established.

Public Sub GetConnection()
Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security = SSPI; Persist Securty Info= True; Initial Catalog = databaseName; Data Source = dataservername;"
Conn.Open
End Sub

Hope it Helps,
Take Care!!

[Shuja Ali]

Hi Shuja Ali,
just want to make a point.When you are using password in your connection string ,I think you should set persist security info =true,otherwise it will not work at all.
Rudraksh

It works even when you set Persist Security Info to False. Here is what MSDN has to say about Persist Security Info

If Persist Security Info is set to False at the time the data source is initialized, the data source cannot persist sensitive authentication information. Furthermore, a call to a property that contains sensitive authentication information, such as a password, returns a default value instead of the actual password.

After the data source has been uninitialized, sensitive information that was set when the data source was initialized with the Persist Security Info property set to False still cannot be obtained from the Properties collection or by persisting the uninitialized data source object. However, new properties set after the data source object has been uninitialized can be persisted or obtained if Persist Security Info is set to True.

Before the data source is initialized for the first time, sensitive information can be obtained from the Properties collection, and can be persisted, regardless of the setting of the Persist Security Info property. Therefore, sensitive applications should avoid passing uninitialized data source objects.

What all this means is that when you set this property to true, the connection is opened and the connection object forgets about the password. If you try to print the connection string after opening the connection with Persis Security Info = False then you will not see the password in the connection string.

s there a way in VB code to reference a database without specifically referencing the server within the program code ( ConnectionString ).

You could put the server name and database name in an INI file and then if the server name/ database name changes you can modify this INI file instead of modifying your code. Take a look at this thread for how to use INI files http://www.codeguru.com/forum/showthread.php?t=333940

Also you ideally when you are connecting to the SQL Server, you should be using Windows Authentication instead of giving the UID and PAssword in the connection string.

[exterminator]

The DBA would like to have the flexibility to do house keeping without engaging programmers to modify and deploy VB applications every time.

Flexibility will come when you provide a UI for the DBAs. There should be a list of databases registered with your application (this registry could be an INI, an XML, another table in one database - depends upon your choice and convenience).

Have a screen to register databases (Form caption - Maintain Databases).. and another screen with appropriate caption, where the DBAs could choose from the registered list of databases to do whatever they want to do.. a migration for example.

From the inputs that are entered you would build the connection string and hence work with the seperate database with seperate connection objects. That would give a better flexibility.

By the way, what do you mean by this?

...the DBA has memory leaks ...

Regards..

[rudraksh]

It works even when you set Persist Security Info to False. Here is what MSDN has to say about Persist Security Info

What all this means is that when you set this property to true, the connection is opened and the connection object forgets about the password. If you try to print the connection string after opening the connection with Persis Security Info = False then you will not see the password in the connection string.

You could put the server name and database name in an INI file and then if the server name/ database name changes you can modify this INI file instead of modifying your code. Take a look at this thread for how to use INI files http://www.codeguru.com/forum/showthread.php?t=333940

Also you ideally when you are connecting to the SQL Server, you should be using Windows Authentication instead of giving the UID and PAssword in the connection string.

Hi Shuja,

I think arguing is good at certain time but you should give it another thought.What you would do while connecting in mixed mode?
And generally we do it in mixed mode.I read the above quote but it only tells to use the persist security info =true while using password.I think you already have got it.
Best of Luck
Rudraksh

[T2T2]

Exterminator,

our dba talks of "memory leaks" with respect to SQL Server applications.
I don't know what this means myself, but I assume that he has statistical diagnostics which indicate that things are not healthy on the server running these applications.

I was hopeing that I could use the provider property to refer to an odbc Data Source, so when our database relocates we just have to update the odbc Data Source settings.

Is this feasible?

I will have to investigate the ini file approach!

[Shuja Ali]

Hi Shuja,

I think arguing is good at certain time but you should give it another thought.What you would do while connecting in mixed mode?
And generally we do it in mixed mode.I read the above quote but it only tells to use the persist security info =true while using password.I think you already have got it.
Best of Luck
Rudraksh

I have been using Persist Security Infor = False for ages now and have never had any issues with it. .

I think you should read it again. It never says that you should set Persist Security Info to true.

[zmyint]

Hi All,

I was hopeing that I could use the provider property to refer to an odbc Data Source, so when our database relocates we just have to update the odbc Data Source settings.

I have never connected my project with SQL Server
I want to use my project with multiple databases prepared in SQL Server2000.

I don't understand odbc too well, but can this be used as a pointer to the database, so the VB code always refers to the same odbc reference?

We all know why Microsoft provided ADO connection. If we use ADO we dont need to use ODBC.

We run VB6 applications connected to SQL Server 2000.
The issue I have is that we need to move the databases to a different server from time to time.
Mainly for maintenance reasons, the DBA has memory leaks and other issues forcing our application database to relocate to different servers.

Is there a way in VB code to reference a database without specifically referencing the server within the program code ( ConnectionString ).

ok I think we all know how to write connection with SQL Server.
If you want to connect SQL with your VB Program , you just create two text boxs and one combobox. USER NAME , PASSWORD and ComboBox is for All the DB in the SQL. So when you connect to SQL with your program you call all the DB from SQL and put into the ComboBox.
After entering all fields when you click the button. You bring all the fields into your connection statement. So you dont need to worry for changing username password or dabase name as well. You also can bring you project to every customer site.
I hope you all will clear.

[T2T2]

Thanks Zmyint,

if I understand your approach, you are suggesting that the user enters the server and sql server log in details to build the connectionstring statement at run time.

This isn't practical for our project, as the users have no clue about these details. In fact they don't even know what sql server is.

The person who will be reassinging the database server location is the systems administrator/dba.
The users click their desktop icon and fire up the application to commence entering business transactions.

In my readings about using ODBC, I believe ADO functionality may be compromised, in other words it's not the thing to do.
Hence some of the comments in this thread about odbc.

I still need to look into the ini option mentioned, that could be the go.
Otherwise I will suggest to the dba that each time a server change is required a new deployment cannot be avoid to maintain the current integrity of our project...... I hate giving bad news.

[Shuja Ali]

Thanks Zmyint,

if I understand your approach, you are suggesting that the user enters the server and sql server log in details to build the connectionstring statement at run time.

This isn't practical for our project, as the users have no clue about these details. In fact they don't even know what sql server is.

The person who will be reassinging the database server location is the systems administrator/dba.
The users click their desktop icon and fire up the application to commence entering business transactions.

In my readings about using ODBC, I believe ADO functionality may be compromised, in other words it's not the thing to do.
Hence some of the comments in this thread about odbc.

I still need to look into the ini option mentioned, that could be the go.
Otherwise I will suggest to the dba that each time a server change is required a new deployment cannot be avoid to maintain the current integrity of our project...... I hate giving bad news.

The best way to go about it is to have an INI file. That is the easiest way out.

You should also consider having your SQL Server set for Windows Authentication. That way you do not have to worry about the UserID and password.

[T2T2]

Thanks Shuja,

I downloaded the INIReader.zip file from the other thread.

I believe I do have windows authentication set, but have not updated my connectionstring? I'll check it out at work tomorrow.

One question, when I build this ini file I assume it will be processed by the VB6 deployment process.
Is there anything I need to do during deployment?

where will the ini file reside, under the applications programs folder?