We help IT Professionals succeed at work.

Connection String - SQL Express 2008 R2

Hi,

Can anyone tell me the connection string that I can use in Classic ASP for SQL Express 2008 R2?

thanks
Comment
Watch Question

APD TorontoSoftware Developer

Author

Commented:
Also...
I came up with the following code, but get the following err
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/Websites/CSSN/cart/x.asp, line 11
FYI - My SQL Express is set to Windows Authentication

<%@ Language=VBScript %>
<%
strConn =	"Server=APD-DELL\SQLEXPRESS;" & _
			"Database=CSS_Carts;" & _
			"User ID=;" & _
			"Password=;" & _
			"Trusted_Connection=False;"
			
Set cnn = Server.CreateObject("ADODB.Connection")
	
cnn.Open strConn

Set rs = Server.CreateObject("ADODB.Recordset")

sql = "SELECT * FROM Admin"

With rs
	.Open sql, cnn
	do while not .EOF 
		Response.Write(.Fields("UserName") & "<br>")
		.MoveNext 
	loop
	.Close 
end with

set rs = nothing
	
cnn.Close 
set cnn = Nothing			

%>

Open in new window

Commented:
If you look at:
www.connectionstrings.com
it's the most comprehensive site for all connection strings of all types of databases :-)

/ Tobzzz
APD TorontoSoftware Developer

Author

Commented:
thats how i came up with the above.

Commented:
You should have a userid and password of somekind. When I set up my local SQL Server Express 2008 I think I had to make a user (sa) and a password. My connection string (offline) looks like this:
myConnString = "driver={SQL Server}; server=TOBZZZ-PC\SQLEXPRESS; database=DEVDB; User ID=sa; password='myChosenPass'"
APD TorontoSoftware Developer

Author

Commented:
I added driver={SQL Server};
now I get login failed, even if I type my Windows credentials, which I want to avoid doing.
When I installed SQL EXPRESS I checked off to use Windows Authentication, and then I setup my db.  At this point, how can I change that and add a seperate User ID/Password?
Commented:
OK, bear with me on this because I installed and set this up a couple of years ago and my memory is very hazy. Below are some screenshots for you of my SQL Server 2008.

(1). I believe I connected to the SQL Server so I can see my database(s) in a tree.
(2). Right click the top folder with server name and green arrow and click properties.
(3). Under 'security', chose SQL Server and Windows Auth mode and click 'OK'.
(4). On the 'Security' folder I expand it and right click 'Logins' and click 'New Login'.
(5). Create a login name as 'sa' and password as you like. Just do same as my screengrab.
(6). Now click 'permissions' tab on bottom of the list and in the lower window (see last screengrab) check that you have the 'connect SQL' line with Grantor = sa and tick the Grant box.
(7). Now go back to your website and make your connection string like mine and hopefully bobs your uncle.

Best of luck

/ Tobzzz
1.jpg
3.jpg
2.jpg
4.jpg
APD TorontoSoftware Developer

Author

Commented:
Hi,
the only thing I have different from you (I think) id that from 2.jpg I have unchecked enforce password policy, so I use a simple password for testing.
My code is:
 strConn = "Driver={SQL Server};" & _
"Server=APD-DELL\SQLEXPRESS;" & _
"Database=CSS_Carts;" & _
"User ID=sa;" & _
"Password=abcxyz;" & _
"Trusted_Connection=False;"
I get:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "CSS_Carts" requested by the login. The login failed.
Even when I try to login to SQL Express, I get:

TITLE: Connect to Server
------------------------------
Cannot connect to APD-DELL\SQLEXPRESS.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476 
------------------------------
BUTTONS:
OK
------------------------------

Under 2.jpg, i tried retyping my password, same thing.
Please help
APD TorontoSoftware Developer

Author

Commented:
Thank you so much...

After everything else...

1-I restarted my pc (logged in my server successfully with sa)
2- Removed last line of my connection.

Commented:
OK good, it's certainly not straight forward, is it! SQL Express 2005 did all the leg work, it probably took me a couple of days to work all this out. I'm pleased you've got it sorted - happy developing!

/ Tobzzz
APD TorontoSoftware Developer

Author

Commented:
thank you, i appreciate it :)