Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1283
  • Last Modified:

SQL connection error

Hi all, as the topic states, I have problem connecting the sql to display the information.
The error given was:

Error Type:
Microsoft OLE DB Service Components (0x80040E73)
Format of the initialization string does not conform to the OLE DB specification.
maintenance.asp, line 53

where line 53 is Call rs_Date.Open (sSQL,connection).

Below are the codes for display:

<html>
<head>
<title></title>
<link rel="stylesheet" type="text/css" href="Style.css">
</head>
<%      Dim DBcon
      DBCon = "Driver={SQL Server}; Server=Servername; Database=databasename"

      Set connection = Server.CreateObject( "ADODB.Connection" )
      Call connection.Open (DBCon)
      sYear = Request("Year")
      sEquipmentItemCode = Request("EquipmentItemCode")
      sItem = Request("Item")
      vesselName = Request("vesselName")
      masterID = Request("masterID")
%>
<body>


<div style="margin:10px; font-size:14px; font-weight:bold" align="center">
Maintenance - for the year of: <%=sYear%>
</div>
<form name="InputForm" style="margin-bottom:5">
  <table align="center" width="100%">
    <tr>
      <td colspan="7" align="right">
<input name="button" type="button" style="font-size:10px" onClick="javascript:window.close()" value="Close Window">
      </td>
    </tr>
    <tr>
      <td colspan="7" style="padding-top:10px; padding-bottom:10px">
        Ship: <strong><em><%=vesselName%></em></strong><br>
        Code: <strong><em><%=sEquipmentItemCode%></em></strong><br>
        Item: <strong><em><%=sItem%></em></strong><br>
        For the year of: <strong><em><%=sYear%></em></strong>
      </td>
    </tr>

    <tr>
      <td class="TblTitle" width="149">Date<br><em><font size="1">(yyyy/mm/dd)</font></em></td>
      <td class="TblTitle" width="139">Location</td>
      <td class="TblTitle" width="134">Man Hr<br><em><font size="1">(Hr)</font></em></td>
      <td class="TblTitle" width="344">Description</td>
    </tr>
   
    <%

      sSQL = "SELECT MaintenanceDate,Location,Man_Hr,Description FROM MaintenanceRec WHERE EquipmentItemCode = '"&sEquipmentItemCode&" AND masterID='"&masterID&"' AND YEAR(MaintenanceDate)= '"&sYear&"'"
      Set rs_Date = Server.CreateObject( "ADODB.Connection" )
      Call rs_Date.Open (sSQL,connection)
      if rs_Date.eof then response.write "<tr><td colspan=6 align=center>*** No maintenance found on this item ***</td></tr>"
      do while not rs_Date.eof
      %>
      <tr>
      <td><%=rs_Date("MaintenanceDate")%></td>
      <td> <%=rs_Date("Location")%></td>
      <td align="center"><%=rs_Date("Man_Hour")%></td>
      <td><%=rs_Date("Description")%></td>
    </tr>
    <%
      rs_Date.movenext      
loop
%>
      <%Call rs_Date.Close()
    Call connection.Close()%>
  </table>
</form>
</body>
</html>


Can anyone enlighten me how I can solve this problem? Is there anything wrong with my SQL statement?
0
Yannie
Asked:
Yannie
3 Solutions
 
hongjunCommented:
try this
Instead of
    Set rs_Date = Server.CreateObject( "ADODB.Connection" )
Use this
    Set rs_Date = Server.CreateObject( "ADODB.Recordset" )



hongjun
0
 
hongjunCommented:
For more connection string options, refer to
http://www.connectionstrings.com/



hongjun
0
 
hongjunCommented:
You may wish to use this sql statement instead :)

sSQL = "SELECT MaintenanceDate,Location,Man_Hr,Description FROM MaintenanceRec WHERE EquipmentItemCode = '"&sEquipmentItemCode&"' AND masterID='"&masterID&"' AND YEAR(MaintenanceDate)= "&sYear



hongjun
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
hongjunCommented:
Use single quote around values if field is a string.
Do not use single quote around values if field is an integer


hongjun
0
 
str_kaniCommented:
The first point I noted, the connection string, it should be something like...

 "Provider=SQLOLEDB; Data Source =servername; Initial Catalog =databasename; User Id = sa; Password="
0
 
neeraj523Commented:
Hello Yannie

hongjun is correct.. by mistake u r creating a connection object instead of a recordSet object..

is this sorting out ur problem ??

neeraj523
0
 
YannieAuthor Commented:
Oh yes solved but it leads to another problem when i try to read the info out, it doesnt appear right.

All as below
<td><%=rs_Date("MaintenanceDate")%></td>
<td> <%=rs_Date("Location")%></td>
<td align="center"><%=rs_Date("Man_Hour")%></td>
<td><%=rs_Date("Description")%></td>

can't be displayed even though I have the information in the database and sql statement right.

Why is it so? Anyone able to help?
0
 
YannieAuthor Commented:
To add on, when i tested without the do loop, as the record has only one relevant record, so it's suppose to retrieve it, it cant.

It shows:
Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

The statement is :
%
sSQL = "SELECT MaintenanceDate, Location, Man_Hour, Description FROM MaintenanceRec WHERE EquipmentItemCode = '"&sEquipmentItemCode&" AND masterID="&masterID&" AND YEAR(MaintenanceDate)= "&sYear&"'"
Set rs_Date = Server.CreateObject( "ADODB.Recordset" )
Call rs_Date.Open (sSQL,connection)
%>
<tr>
      <td><%=rs_Date("MaintenanceDate")%></td>
      <td> <%=rs_Date("Location")%></td>
      <td align="center"><%=rs_Date("Man_Hour")%></td>
      <td><%=rs_Date("Description")%></td>
   </tr>
          <%Call rs_Date.Close()%>


Anyone able to solve?
0
 
YannieAuthor Commented:
Ok i managed to solve it...managed to check the sql statement again as Hongjun gave me!
Thanks Hongjun!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now