CODEWITHSUNDEEP
sql-serversql-server-2005asp-classicvbscriptado
vince
vince

Reputation: 11

connection string

can anyone show me a sample connection string in asp page with sql server 2005 on vista?

Or any solution to this problem:

Dim cnn As ADODB.Connection

throws an error http 500.

I suppose ado is not correctly installed?

Any ideas?

Upvotes: 1

Views: 542

Answers (5)

jbmintjb
jbmintjb

Reputation: 133

Easy way to create a connection string is to make it using a UDL file.

  1. Open up file explorer, and anywhere you fancy, create a new text file but change the extension to .udl instead of .txt.
  2. Double click to open that new .udl file and you will see the below;
  3. The provider tab lets you choose what data source type you want to connect to, go ahead and select Microsoft OLE DB Provider for SQL Server.
  4. On the connection tab, you can click on the drop down for the server name, and it will show a list of SQL server’s it has found that you can connect to. In the past I have seen this not work so well, so you can manually type in your server name. You can then specify the login type, which will either be Windows Authentication (use the currently logged in user details to log into SQL Server) or provide the user name and password.
  5. You will notice on the option for specific user name and password that there is 2 tick boxes. If the login you want to use does not need a password, make sure you check the blank password box as simply leaving the password textbox blank will not work. The allow saving password will save the password value as a raw text value in the connection string (i.e, easily readable). You can encrypt connection strings in your application config file which I will explain later, I always tick this box but I will leave that up to you.
  6. You are now ready to select the database you want to connect to on the server, clicking on the dropdown arrow on the database on the server dropdown will display all the database’s you are able to connect to with the login details you just provided.
  7. With all that done, click on Test Connection and it should work. If you get any failure then message box will offer some help but it will most likely be the wrong login details or you simply cannot access the database.
  8. Now close this dialog by clicking OK, any other way of closing this dialog will lose your work. In file explorer, right click on your .udl file, select open with and choose NotePad (or any other text editor).
  9. Now in notepad, you have your connection string! You just want to copy the parts that start after Provider=SQLOLEDB.1;.

I've written about it on my blog too here.

Upvotes: 0

Alos
Alos

Reputation: 2715

Here is an example of what you would use: PROVIDER=SQLOLEDB;SERVER=your_server;UID=your_user_name;PWD=your_password;DATABASE=your_server

Upvotes: 0

Richard Szalay
Richard Szalay

Reputation: 84744

connectionstrings.com is your friend:

Connection strings for SQL Server 2005

Edit: The Dim var As Type syntax is not valid in VBScript (only VB). You need to use Server.CreateObject:

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")

For more information, see ADO Code Examples VBScript on MSDN (which, granted, are pretty horrible samples)

Upvotes: 5

Timothy S. Van Haren
Timothy S. Van Haren

Reputation: 8966

ConnectionStrings.com has a list of SQL Server 2005 connection strings in various formats that you can start with.

Upvotes: 1

Jason Kealey
Jason Kealey

Reputation: 7986

Connection strings: http://connectionstrings.com/sql-server-2005

Upvotes: 2

Related Questions