Solved

Linking to a SQL server via Access- "ODBC--connection to 'SQL Server###.##.###.#' failed."

Posted on 2004-08-06
3
226 Views
Last Modified: 2008-03-06
I get the code below to work when entering a regular Access table, but can't get it to work with a linked table (dbo_regIDLookup). Is it the "ID" that is giving me the above error? I CAN connect to the linked table via Access.

<%@ Language=VBScript %>
<%
dim userid
userid = Session("userid")
if userid ="" then
        Response.Redirect("exam_login.asp")
   end if    
   
'''''''''''''''''''''''''''''''''''''''''''''''''''  
 %> <!-- #INCLUDE FILE = "connect_ama.asp" -->
<style type="text/css">
<!--
body
{font-family:arial;
font-size: x-small
}
.style1 {font-size: x-small}
.style2 {font-size: medium}
  .submitLink {
   color: #00f;
   background-color: transparent;
   text-decoration: underline;
   border: none;

   cursor: hand;
  }
-->
</style>
<body bgcolor="#FFFFFF">
<table cellPadding="0" cellSpacing="0"          border="1" width="85%" align="center">
<tr><td bgColor="#ffffff"><table  bordercolorlight="#999999" bordercolordark="#999999"  align="center" width="100%"><tr><td bgColor=gold><div align="center"><font face="arial" size="3">Testing Center</font></div></td></tr></table></td></tr><tr><td align="center">

<TABLE width="100%"  border=1 align="center" cellPadding=0 cellSpacing=0>
 
  <TR>
    <TD style="background-color:#cccccc" width="50%" align="center"><form STYLE="MARGIN:O method="post" action ='student_profile.asp' id=form2 name=form2>
        <input name="B1" type='submit' class='submitLink' value="View Profile and Scores"></TD></form>
    <TD style="background-color:#cccccc" width="50%" align="center"><form  method="post" action ='exam_login.asp'  name=form1>
        <input name="logout" type="submit" class='submitLink' value="Logout"> &nbsp;&nbsp;<input name="logout" type="submit" class='submitLink' value="Login">
      
      </TD></form></TR></TABLE>
<table  width=85% align="center">
  <tr><TD>

<P class="style1" align=left>
<strong>Exam Instructions:</strong><BR>
Please&nbsp;make a note of the
following&nbsp;before choosing your exam.</p><OL class="style1" style="MARGIN-TOP: 0in" type=1>
 
      <LI> Questions
        are multiple choice type. I.E., only one answer is correct. The answers are represented by four radio buttons "<INPUT id=radio2
        style="LEFT: 127px; TOP: 271px" type=radio name=radio2>        ".
           
 
      <LI>Questions appear one at a time. Confirm your answer before hitting
  the Next button. You can  come back and change the answer.
  <LI>
              For&nbsp;the entire exam&nbsp;you are
  given some limited time (The timer is also displayed on the status bar of the
  browser). </LI>
  <LI>The exam will be removed from your list of available exams upon successful completion. </LI>
  <LI>Students failing a test twice will not receive credit for the Course.</LI>
</OL>
  </P>

</tr></TD></table>
<%
u_search=request.form("u_search")
u_exp=request.form("u_exp")
u_id=request.querystring("u_id")
'Checks to see if the user has submitted a search or
'clicked a hyperlink
if u_search <> "" or u_id <> "" then
accessdb="../db/bank_ama"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")
'Determines if the user submitted a search or clicked a link
if u_id <> "" then
sql= "select * from dbo_regIDLookup where NO_SCH=" & u_id
else
sql = "select * from dbo_regIDLookup where "& u_exp &" like '%%" &u_search &"%%' " 
end if
rs.Open sql, cn
%>
<%'if there are no observationb found display no record found
if rs.eof then %>
No selections could be found
<% ' if observations are found display them
else %>
<table align="center" width="85%">
<tr>
<td colspan="2" align="center"><u>Results</u></td>

</tr>
<%
rs.movefirst
variable = Replace(Request.Form("ExamName") , " ", "_")
do while not rs.eof%>



<tr>
<td align="center"><form style'margin:0' method='POST'action ='../user/exam_random.asp'><input name=start type='submit' class='submitLink' value='<%= rs("ExamName") %>'>
</td><td align="left"><font size="2"><%= rs("Description") %></font></a>
</td>

</tr>
<%
rs.MoveNext
loop%>
</table>

<%
' end check for no obs
end if
' end check for no input
end if %>
<hr><table>
<tr><td>
<form action="<%=request.servervariables("script_name") %>" method="post">
Enter the Course name or Class ID here: <input type="text" name="u_search" value="<%= u_search %>">
&nbsp;
<select size="2" name="u_exp">
<option selected value="ExamName">Name of Course</option>
<option value="Description">Class ID</option>
</select>
&nbsp;
<input type="submit" value="Search">
</form>
</td>
</tr>
</table>
0
Comment
Question by:stephenrex
3 Comments
 
LVL 3

Accepted Solution

by:
Fairco earned 250 total points
ID: 11736289
Try using Jet instead of ODBC and see if the problem still appears. Using Jet I can access the linked tables as well.
Set the connection string this way, everything else can remain the same:
cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\project\myDatabase.mdb"
0
 
LVL 6

Expert Comment

by:masirof
ID: 12708702
No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: Fairco

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

masirof
EE Cleanup Volunteer
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Update field in order 21 148
UTC (timezone) without using an API 16 50
Query not working correctly? (or how i want it to... 6 52
wordpress display sub menu only when click 12 50
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question