Connecting to sql server 2008 db

neronikita · Nov 2, 2009

Hello. I have used ASP before but I never set up my own database connection in it. Would someone please look at this and tell me why the page will not display... it works for the first section of asp, but when I add in the second part with the connection to the database. I am new to sql server 2008, but my DSN connection works when tested. I've also tried non-dsn connection and couldn't get that to work. Can anyone tell me what I'm doing wrong? I have replaced the pw and ID with generics, but nothing else has changed in this code.

Thanks

Di

CODE

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"

http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>My First ASP Page</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim strMessage
'Place the value Hello World into the variable strMessage
strMessage = "Hello World"
'Write the contents of the variable strMessage to the web page
Response.Write (strMessage)
'Write line break into the web page
Response.Write ("<br>")

'Write the server time on the web page using the VBScript Time() function
Response.Write ("The time on the server is: " & Time())

'Close the server script
%>

<%
'declare the variables
Dim Connection
Dim Recordset
Dim SQL

'declare the SQL statement that will query the database
SQL = "SELECT * FROM emptype"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'open the connection to the database
Connection.Open "DSN=ESI;UID=id;PWD=password;Database=ESI"

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("ID")
Response.write Recordset("Description")
Response.write "<br>"
Recordset.MoveNext
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>
</body>
</html>

vicvirk · Nov 9, 2009

try creating an access database with a single table for testing purposes and see if you can connect to it.

It may eleminate some other issues and help narrow down the problem...

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

neronikita · Nov 9, 2009

I don't have access on this computer, I'll have to try it on my laptop tonight. Thanks for the advice.

Di

wvdba · Nov 10, 2009

i think all the postings from vivrick and turkbear should have done the trick. here's a snippet of code that i use for oracle that works. sql server might be similar.

Code:

strCon = "Driver={Microsoft ODBC for Oracle}; " & _
             "CONNECTSTRING=(DESCRIPTION=" & _
             "(ADDRESS=(PROTOCOL=TCP)" & _
             "(HOST=xx.xxx.xxx.xx)(PORT=1521))" & _
             "(CONNECT_DATA=(SERVICE_NAME=xxxx))); uid=xxxxxx;pwd=xxxxxx;"

    Dim oCon 
    Set oCon = CreateObject("ADODB.Connection")
    Dim oRs 
    Set oRs  = CreateObject("ADODB.Recordset")
    oCon.Open strCon

neronikita · Nov 10, 2009

WVDBA,

It does connect to the db, but it won't let me in, so to speak. It seems to not like my login info, and I used the same login that I use when I'm logging into the database directly (which works there). So I think the code is correct, it's just something with the connection that is blocking it somehow. I tried setting up a new connection to ensure the pw is correct (case and all that) but it still didn't work. The only thing I can think of is that when I set up the database I did something wrong and now it isn't connecting because of that.

Thanks for your post,

Di

Turkbear · Nov 10, 2009

Hi,
Check the file system permissions of the location where the database is located and be sure sufficient rights are granted allow your asp app to access the files.

To Paraphrase:"The Help you get is proportional to the Help you give.."

neronikita · Nov 10, 2009

Do you mean permissions from within SQL Server? i just set everything to grant to see if that would work. It didn't. Still getting
Microsoft SQL Server Native Client 10.0 error '80040e4d'

Login failed for user 'e092599'.

/dispatch/test.asp, line 36

Is there anywhere else that I would have to set permissions?

Thanks,

Di

Turkbear · Nov 10, 2009

Hi,
When you say you verified the user/pass combo, do you mean that you can connect to the SqlServer data directly just not from this asp page?

To Paraphrase:"The Help you get is proportional to the Help you give.."

neronikita · Nov 10, 2009

Correct. I can log into sql server and see my data and work with it. When I set up a DSN and clicked on the test connection button, the connection worked fine. It just won't let me login from this .asp page using the same password.

Di

madanthrax · Nov 14, 2009

I have SQL 2005 and IIS6 running on my laptop as I write this.

I use DSNless connections since 8 years, for simplicity.

Here is a demo of how I get a recordset, adapt as you need.

This code I have in "connection.asp" file:

Code:

<% Dim conn_string
conn_string = "Provider=SQLOLEDB;Server=NB34880\NSserver;Database=NSweb;UID=sa;PWD=xxxxxxxxx" %>

In "NB34880\NSserver" first part is the Windows name of my notebook\ second part is the name of my SQL Server instance.
Database name is obvious. UID "sa" is the SQL administrator account which you made a password for during installation, password is the one you entered.
NOTE: Using the sa account for anything other than internal testing on your notebook would be suicidal, but as thats what you say you are doing then it will blow away any authentication problems and let you get on with developing.

In the page needing the connection I have:

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/connections/connection.asp" -->
<%
dim Standards, Standards_numRows

Set Standards = Server.CreateObject("ADODB.Recordset")
Standards.ActiveConnection = conn_string
Standards.Source = "SELECT PubId, Title, Issued  FROM tbl_Documents  ORDER BY Issued desc"
Standards.CursorType = 0
Standards.CursorLocation = 2
Standards.LockType = 1
Standards.Open()

Standards_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
etc etc.

Hope this helps.

[sub]"Nothing is impossible until proven otherwise"[/sub]

neronikita · Nov 17, 2009

Thanks, I will try this.

Di

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Connecting to sql server 2008 db

neronikita

Technical User

vicvirk

Programmer

neronikita

Technical User

wvdba

IS-IT--Management

neronikita

Technical User

Turkbear

Technical User

neronikita

Technical User

Turkbear

Technical User

neronikita

Technical User

madanthrax

IS-IT--Management

neronikita

Technical User

Similar threads

Part and Inventory Search

Sponsor