Connecting to SQL Server 2008 database using asp

Question

I tried a lot but I am not able to deal with this problem. I wasted last 2 days without any significant result. Hope I will get some help here.

I wanted to connect to a SQL Server 2008 database using ASP. I installed SQL Server 2008, created a database, but I am not able to connect to that database using the asp code. I can see the database in Visual Web Developer, I can also connect it through asp.net using the add connection wizard of visual web developer but I don't want to use add connection wizard. I want to write my asp code to connect to SQL Server database in notepad. My IIS is working and I am able to run asp code to access other database like ms access database but I can't access SQL Server db.

The object explorer of SQL Server Management Studio shows:

localhost\SQLSERVER3(SQL Server 10.0.1600-RAJ-PC\raj)

Databases

examples
  tables
   System Tables
      dbo.cars
      columns
          id(PK,int,not null)
          name(varchar(50),null)

You can see the SQL Server and its databases in the attached jpg image. I want to connect to example database and then want to access cars table.

Please help me.

UPDATED

here is my code :

<html>
<head>
<title>Guestbook</title>
</head>

<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsGuestbook         'Holds the recordset for the records in the database
Dim strSQL          'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "ODBC;Driver={SQL Native Client};" & _
           "Server=localhost\SQLSERVER3;" & _
           "Database=examples;" & _
           "Uid=raj;" & _
           "Pwd=love1987"

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT name FROM dbo.cars;"

'Open the recordset with the SQL query 
rsGuestbook.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGuestbook.EOF
    'Write the HTML to display the current record in the recordset
    Response.Write ("<br>")
    Response.Write (rsGuestbook("Name"))
    'Response.Write ("<br>")
    'Response.Write (rsGuestbook("Comments"))
    'Response.Write ("<br>")

    'Move to the next record in the recordset
    rsGuestbook.MoveNext
Loop

'Reset server objects
rsGuestbook.Close

Set rsGuestbook = Nothing
Set adoCon = Nothing
%>

</body>
</html>

2 things. 1. Are you looking for help with ASP (classic), or ASP.NET. 2. Do you have a sample of the code you're using to try to connect to the database to show what you've tried? — Doozer Blake, Sep 29 '11 at 20:58
@Alan Moore : I am using VB. I thing i am not able to specify the connection string properly. Here is part of my code. adoCon.Open "ODBC;Driver={SQL Native Client};" & _ "Server=localhost\SQLSERVER3;" & _ "Database=examples;" & _ "Uid=raj;" & _ "Pwd=love1987" — Raj Gupta, Sep 29 '11 at 21:10
@blake i was looking for help with asp but it would be really of great help if you explain the connectivity with asp.net since it is better than classic asp. — Raj Gupta, Sep 29 '11 at 21:24
@AmitApollo I have attached my code. Please have a look and figure out what's wrong with it. — Raj Gupta, Sep 29 '11 at 21:25
There is an answer here that may be of help: http://stackoverflow.com/questions/1359579/classic-asp-sql-server-2008-connection-string-using-windows-authentication — Dale C. Anderson, Sep 30 '11 at 08:50

score 2 · Accepted Answer · answered Sep 29 '11 at 21:24

2

I am able to connect using the following connection string on my local dev machine (with SQL 2008 R2 Express):

Driver={SQL Server}; Server=hostname\instancename; Database=dbname; Uid=user; Pwd=password

One thing I noticed in your code: you are trying to establish a DSN-less connection, then you run a query on it without USE dbname or anything. That may be the issue, or a least an issue.

answered Sep 29 '11 at 21:24

bfavaretto

71,580
16
111
150

thanks for your reply. So i should use Driver={SQL Server}; Server=localhost\SQLSERVER3; Database=examples; Uid=raj; Pwd=xxxxxx..........i have used windows authentication option while connecting to my sql server database. So shall i use windows username and password in uid and pwd field or use the normal username 'sa' and corresponding password to connect..? – Raj Gupta Sep 29 '11 at 21:31
thanks a lot buddy....you solved my problem..i am able to connect now...thank you very much...just one more help. Does the same connection string work with asp.net too? If not plz specify the connection string used in asp.net. – Raj Gupta Sep 29 '11 at 21:40
Just checked on as ASP.net project, and I'm using a very similar connetion string, just without the `Driver={SQL Server};` part. However, I'm not 'manually' connecting to the server, my CMS (DotNetNuke) is doing that for me, so I'm not sure. – bfavaretto Sep 29 '11 at 21:47
hi, is there any way to add the value of server attribute at runtime in connection string you specified. That means i am providing the user with a text box in which he enters the server name and i want to user that server name in my connection string. please help. – Raj Gupta Sep 29 '11 at 22:37
Sure, it's a string after all! In classic ASP (vbscript) you could do this: server=request("server_name") [line break] connstr = "Driver={SQL Server}; Server=" & server & "; Database=dbname; Uid=user; Pwd=password" [line break] adoCon.Open connstr. HOWEVER: what you are trying to do sounds dangerous in terms of server security, be careful on what you'll allow your web users to do! – bfavaretto Sep 29 '11 at 22:43
Thanks of the advice, but the page i am creating is for only single user i.e. administrator so only he can use it. One last question: Does adoCon.open return any value. Actually what i want to do is to redirect the user to a page if connection was successfully established. If not i want to show the error message. So i was thinking to user if adoCon then... else ..... end if. Thank you for your consistent help. – Raj Gupta Sep 30 '11 at 14:53
No, you have to check Connection.State. See [here](http://www.w3schools.com/ado/ado_ref_connection.asp) – bfavaretto Sep 30 '11 at 17:53

score 1 · Answer 2 · answered Sep 29 '11 at 21:26

1

Try creating a DSN, and then refer to it by name in your connection string.

Open the ODBC icon in your Control Panel.
Choose the System DSN tab.
Click on Add in the System DSN tab.
Select the Microsoft Access Driver. Click Finish.
In the next screen, click Select to locate the database.
Give the database a Data Source Name (DSN).
Click OK.

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "northwind"

http://www.w3schools.com/ado/ado_connect.asp

answered Sep 29 '11 at 21:26

Bob Johnson

91
1
6

hi, thanks for your answer but i am trying to connect to mssql server database. Can we do that using microsoft access driver? Your post is really very informative but i need to create a dsn-less connection. – Raj Gupta Sep 29 '11 at 21:35
Yeah, sorry I just copy and pasted that text, but a DSN can be created for SQL Server instead of Access. Just thinking you could try the DSN approach for troubleshooting purposes, or just to get you over this hurdle for now so you can focus on other parts of the project. If the problem is unique to your dev environment, then it will not matter when you move to prod. – Bob Johnson Sep 29 '11 at 21:54
but the DSN will also only exist on the machine its created on, so if you move the asp site, you'll need to make a new DSN on the machine you move it to. This is good if you need to reuse the code but have to reconfigure for different databases. – BerggreenDK May 11 '12 at 09:49

Connecting to SQL Server 2008 database using asp

2 Answers2

Linked