Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error 0x80020009 trying to read from MySQL database

Posted on 2007-08-07
5
Medium Priority
?
738 Views
Last Modified: 2013-11-05
I have a MySQL database.  Every field in it is of type 'text' except the primary key, imaginatively named PrimKey, which is an integer.  I write to this database and then try to read back the records using a report.  The report allows you to pick an individual record by the PrimKey.  You click on the link and then it should open up read_only.asp with the primary key in question and show a table with those details.  Code listed below:

<%@ Language = "VBScript" %>
<% SetLocale("en-gb") %>
<%

%>
<%

'Dimension variables
Dim adoCon                   'Holds the Database Connection Object
Dim rsOutOfOffice                  'Holds the recordset for the record to be updated
Dim strSQL                  'Holds the SQL query for the database
Dim lngRecordNo                  'Holds the record number to be updated
Dim connstring
Dim URL

connstring="Driver={MySQL ODBC 3.51 Driver};Server=192.168.1.254;Port=3306;Database=Performance;User=david;Password=david;Option=3;"

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("PrimKey"))

'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  (connstring)

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

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM details WHERE PrimKey=" & lngRecordNo &";"

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

%>
<html>
<head>
<title>Performance Appraisal System Questionnaire</title>
</head>
<body bgcolor="white">

<TABLE BORDER=1>
<TR>
      <TD WIDTH=100>Record No :  </TD>
      <TD WIDTH=900><% Response.Write(rsOutOfOffice("PrimKey")) %></TD>
</TR>
<TR>
      <TD>Department :  </TD>
      <TD><% Response.Write(rsOutOfOffice("Department")) %></TD>
</TR>
<TR>
      <TD>Age Bracket : </TD>
      <TD><% Response.Write(rsOutOfOffice("Age_Bracket")) %></TD>
</TR>
<TR>
      <TD>Were you given adequate time to complete the form? : </TD>
      <TD><% Response.Write(rsOutOfOffice("Adequate_time")) %></TD>
</TR>
<TR>
      <TD>Did you understand all the questions asked on the form? : </TD>
      <TD><% Response.Write(rsOutOfOffice("Understand_questions")) %></TD>
</TR>
<TR>
      <TD>If not, which questions did you not understand? : </TD>
      <TD>COME BACK TO THIS ONE </TD>
</TR>
<TR>
      <TD>Do you feel that the questions asked were relevant to you? : </TD>
      <TD><% Response.Write(rsOutOfOffice("relevant_questions")) %></TD>
</TR>
<TR>
      <TD>If not, which questions were irrelevant and why? : </TD>
      <TD><% Response.Write(rsOutOfOffice("irrelevant")) %></TD>
</TR>
<TR>
      <TD>Are you comfortable with filling out this type of form (That is, assessing your own performance)? : </TD>
      <TD><% Response.Write(rsOutOfOffice("Comfortable")) %></TD>
</TR>
<TR>
      <TD>Can you think of a better way for your performance to be assessed? : </TD>
      <TD><% Response.Write(rsOutOfOffice("better_way")) %></TD>
</TR>
<TR>
      <TD>What do you think of the reviews being held in December? : </TD>
      <TD><% Response.Write(rsOutOfOffice("Timing")) %></TD>
</TR>
<TR>
      <TD>Is one review per year adequate? : </TD>
      <TD>COME BACK TO THIS ONE</TD>
</TR>
<TR>
      <TD>(text)</TD>
      <TD><% Response.Write(rsOutOfOffice("Is_one_adequate2")) %></TD>
</TR>
<TR>
      <TD>Who carried out your review? : </TD>
      <TD><% Response.Write(rsOutOfOffice("how_many_reviewers")) %></TD>
</TR>
<TR>
      <TD>If not, who should have carried out your review? : </TD>
      <TD>COME BACK TO THIS ONE</TD>
</TR>
<TR>
      <TD>Other, please specify : </TD>
      <TD><% Response.Write(rsOutOfOffice("Why_other_reviewer")) %></TD>
</TR>
<TR>
      <TD>Why? : </TD>
      <TD>COME BACK TO THIS ONE</TD>
</TR>
<TR>
      <TD>Were you given ample opportunity to discuss any concerns you may have had in relation to your performance? : </TD>
      <TD><% Response.Write(rsOutOfOffice("discuss_concerns")) %></TD>
</TR>
<TR>
      <TD>Were you given any feedback in relation to your past performance? : </TD>
      <TD><% Response.Write(rsOutOfOffice("performance_feedback")) %></TD>
</TR>
<TR>
      <TD>Were you happy with this feedback? : </TD>
      <TD><% Response.Write(rsOutOfOffice("happy_feedback")) %></TD>
</TR>
<TR>
      <TD>Was the review worthwhile? : </TD>
      <TD><% Response.Write(rsOutOfOffice("Review_worthwhile")) %></TD>
</TR>
<TR>
      <TD>Accompanying text : </TD>
      <TD><% Response.Write(rsOutOfOffice("review_worthwhile_text")) %></TD>
</TR>
<TR>
      <TD>Do you know exactly what is required of you to fulfil the requirments of your job? : </TD>
      <TD><% Response.Write(rsOutOfOffice("know_requirements")) %></TD>
</TR>
<TR>
      <TD>Do you feel that the Performance Appraisal adequately captures how you meet the requirements of your job? : </TD>
      <TD>COME BACK TO THIS ONE</TD>
</TR>
<TR>
      <TD>Have you any opinions or ideas on how the Performance Appraisal System could be improved? : </TD>
      <TD>COME BACK TO THIS ONE</TD>
</TR>
<TR>
      <TD>Do you feel that the Performance Appraisal System is a fair means of assessing bonus and pay reviews? : </TD>
      <TD></TD>
</TR>
<TR>
      <TD>What are the benefits of the Performance Appraisal System? : </TD>
      <TD></TD>
</TR>
<TR>
      <TD>What are the weaknesses of the Performance Appraisal system? : </TD>
      <TD></TD>
</TR>

</TABLE>

<br><br>
<center><img src="graphics/jod_small.jpg" >
<br><br><br>
.



<!-- Begin form code -->

<!-- End form code -->
<%
Response.Write("<center><a href="&url&">Back</a></center>")
%>
</body></html>

<%
'Reset server objects
rsOutOfOffice.Close
Set rsOutOfOffice = Nothing
Set adoCon = Nothing
%>


________________________

It was working with the first entry in the database, then showing the following error for any subsequent:

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80020009)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/intranet/performance/Read_only.asp


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Avant Browser; Avant Browser; .NET CLR 2.0.50727; .NET CLR 1.1.4322; InfoPath.2)

Page:
GET /intranet/performance/Read_only.asp

________________________________

I cleared down the database and added in a few new entries, now it works with none of the entries.  The only difference I see is that since it worked, I started using a character stripping function on every field before writing it back to eliminate ' and " and other disallowed characters.  The function is as follows:

<%   Dim regEx
      Set regEx = New RegExp
      regEx.Global = true
      regEx.Pattern = "[^0-9_a-z A-Z .,-_!?]"
      %>

Called as follows:

Dim VarDept
varDept = regex.replace(request.form("Department"), "")&" " 

VarDept and the other values are then written back into the database using the following statement:

strSQL = "INSERT INTO Performance.details (Department, Age_Bracket, Adequate_Time, Understand_Questions, Not_Understood, Relevant_Questions, " _
            & "Irrelevant, Comfortable, Better_Way, Timing, Is_one_adequate, Is_one_adequate2, How_Many_reviewers, right_person, " _
            & "Who_should_review, Who_should_review_spec, Why_other_reviewer, Discuss_concerns, Performance_feedback, Happy_feedback, " _
            & "review_worthwhile, review_worthwhile_text, know_requirements, meet_requirements, improvement_ideas, fair_remuneration, " _
            & "fair_remuneration_desc, benefits, weaknesses) VALUES ('" _
            & VarDept & "', '" & VarAge & "', '" & VarTime & "', '"_
            & VarUnderstand & "', '" & VarNotUnder & "', '" & VarRel & "', '"_
            & VarIrrel & "', '" & VarComf & "', '" & VarBetter & "', '"_
            & VarTiming & "', '" & VarAdequate & "', '" &  VarAdequate2 & "', '" _
            & VarNumReview & "', '"_
          & VarRight & "', '" & VarWho & "', '" & VarWhoSpec & "', '" _
            & VarWhy & "', '" & VarDiscuss & "', '" & VarFeedback & "', '" _
            & VarHappy & "', '" & VarWorth & "', '" & VarWorthText & "', '" _
            & VarReq & "', '" & VarMeetReq & "', '" & VarImprove & "', '" _
            & VarFair & "', '" & VarFairDesc & "', '" & VarBen & "', '" _
            & VarWeak & "');"

I am thinking this is a datatypes problem, but I know so little about this that I don't know where to start with troubleshooting.  Any help would be greatly appreciated.
 

0
Comment
Question by:GlobexCorp
  • 2
  • 2
5 Comments
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 800 total points
ID: 19644747
Hi GlobexCorp,

The error suggests that you're not connected to the database.

Check that your connection string is correct and the connection actually occurs.


Kent
0
 
LVL 14

Accepted Solution

by:
bugs021997 earned 1200 total points
ID: 19645827
@GlobexCorp

Do following to troubleshoot....

> Make sure that you SELECT column1, column2, columns3 instead of using SELECT *

> Try below connectionstring

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={MySql}; Server=localhost; uid=myLogin; pwd=myPass; database=myDB; option=3; port=3306;"
Set rsmyTable = Conn.Execute("SELECT * From myTable")
%>
0
 
LVL 2

Author Comment

by:GlobexCorp
ID: 19645881
Folks, I'm narrowing it down.. it is seeing the database, because if I cut all out and call back one item, it works.  I'm going through them now to find which is the dodgy fellow.
0
 
LVL 14

Expert Comment

by:bugs021997
ID: 19645968
Welll wishes to ya....

Thats why i marked you to fetch records as SELECT column1, column2, columns3 instead of using SELECT * ...... so it point out which is the dodgy rsOutOfOffice

0
 
LVL 2

Author Comment

by:GlobexCorp
ID: 19646026
'Thanks folks, you both put me on the right track, I'll get through it now!  
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 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