|
 |
asp_databases thread: ADODB.Recordset error '800a0c93'
Message #1 by srobinson@g... on Fri, 5 Oct 2001 10:09:11
|
|
Hi All,
I am in desperate need of some help!
I have been experiencing inumerable problems while trying to implement the
following code. The most recent error is ADODB.Recordset error '800a0c93'
The error occurs when I try to close the first record set:
Recordset1.Close()
The second problem occurs when I try to submit the form to the jobs table
in the database. What I am trying to do is get the last job no from the
database, pass the value to the variable 'jn', increment it by the value
of 1 and then submit it as a new record into the database with all the
other fields.
Can anyone help?
The purpose of the code is a job booking form.
<%@LANGUAGE="VBSCRIPT"%>
<% session("svCompany")=Request.form("Company")%>
<!--#include file="../Connections/conCustomers.asp" -->
<%
' *** Edit Operations: declare variables
MM_editAction = CStr(Request("URL"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If
' boolean to abort record edit
MM_abortEdit = false
' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert")) <> "") Then
MM_editConnection = MM_conCustomers_STRING
MM_editTable = "JOBS"
MM_editRedirectUrl = "bag_print.asp"
MM_fieldsStr
= "Jobnumber1|value|invoicenumber|value|custordnumber|value|custjobnumber|v
alue|job_title|value|PRODUCT|value|Company|value|DELIVADD1|value|DateIn|val
ue|DELIVADD2|value|account_exec|value|DELIVCITY|value|County|value|Delivery
Method|value|DELIVPCODE|value|cod|value|Telephone|value|comments|value|Fax|
value|Web|value"
MM_columnsStr
= "Jobnumber1|none,none,NULL|invoicenumber|none,none,NULL|custordnumber|',n
one,''|custjobnumber|',none,''|job_title|',none,''|PRODUCT|',none,''|Compan
y|',none,''|DELIVADD1|',none,''|DateIn|#,none,NULL|DELIVADD2|',none,''|acco
unt_exec|',none,''|DELIVCITY|',none,''|County|',none,''|DeliveryMethod|',no
ne,''|DELIVPCODE|',none,''|cod|none,Yes,No|Telephone|',none,''|comments|',n
one,''|Fax|',none,''|Web|',none,''"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")
' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it
If (CStr(Request("MM_insert")) <> "") Then
' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),",")
Delim = MM_typeArray(0)
If (Delim = "none") Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none") Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none") Then EmptyVal = ""
If (FormVal = "") Then
FormVal = EmptyVal
Else
If (AltVal <> "") Then
FormVal = AltVal
ElseIf (Delim = "'") Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''") & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues
& ") values (" & MM_dbValues & ")"
If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conCustomers_STRING
Recordset1.Source = "Select * From JOBS"
Recordset1.CursorType = 2
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Function GetJobNumber()
MM_offset = -1
Dim jn
Dim jpfix
Recordset1.MoveLast
jn = Recordset1.Fields.Item("Jobnumber1").Value
Response.Write(jn)
jpfix = Recordset1.Fields.Item("JOBPREFB").Value
Response.Write(jpfix)
If jn < 9999 Then
jn = jn + 1
Recordset1.Fields.Item("Jobnumber1").Value = jn
Recordset1.Fields.Item("JOBPREFB").Value = jpfix
Response.Write(jn)
Response.Write(jpfix)
If jn = 9999 then
jn = 0001
Recordset1.Fields.Item("JOBPREFB").Value = "F"
Response.Write(jn)
End If
End If
End Function
%>
<%
set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_conCustomers_STRING
Recordset2.Source = "SELECT COMPANY, ADDRESS1, ADDRESS2, CITY, COUNTY,
POSTCODE, TELEPHONE, FAX, EMAIL, WEB, DELIVMETH FROM CUSTOMER"
Recordset2.CursorType = 2
Recordset2.CursorLocation = 2
Recordset2.LockType = 3
Recordset2.Open()
Recordset2_numRows = 0
%>
<%
Dim Recordset2__MMColParam
Recordset2__MMColParam = "1"
if (Request.QueryString("COMPANY") <> "") then Recordset2__MMColParam =
Request.QueryString("COMPANY")
%>
<%
set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_conCustomers_STRING
Recordset2.Source = "SELECT COMPANY, ADDRESS1, ADDRESS2, CITY, COUNTY,
POSTCODE, TELEPHONE, FAX, EMAIL, WEB, DELIVMETH FROM CUSTOMER WHERE
COMPANY = '" + Replace(Recordset2__MMColParam, "'", "''") + "'"
Recordset2.CursorType = 2
Recordset2.CursorLocation = 2
Recordset2.LockType = 3
Recordset2.Open()
Recordset2_numRows = 0
%>
<%
Call GetJobNumber()
%>
<html>
<head>
<title>Graphi-Intranet</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
<!--
function MM_reloadPage(init) { //reloads the window if Nav4 resized
if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt
(appVersion)==4)) {
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight;
onresize=MM_reloadPage; }}
else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH)
location.reload();
}
MM_reloadPage(true);
// -->
//-->
</script>
<style type="text/css">
<!--
.option { font-family: Arial, Helvetica, sans-serif; font-size: 12px;
font-weight: bold; color: #FFFFFF}
.box { font-family: Arial, Helvetica, sans-serif; font-size: 12px; color:
#000000; background-color: #CCCCCC; border-style: none}
.box2 { font-family: Arial, Helvetica, sans-serif; font-size: 12px;
color: #000000; background-color: #B3C1CE; border-style: none}
.but1 { font-family: Arial, Helvetica, sans-serif; font-size: 12px; font-
weight: normal; color: #000000; background-color: #FFCC66; border: 1px
#FFFFFF solid}
.but2 { font-family: Arial, Helvetica, sans-serif; font-size: 12px; font-
weight: normal; color: #000000; background-color: #CC3333; border: 1px
#FFFFFF solid }
.but3 { font-family: Arial, Helvetica, sans-serif; font-size: 12px; font-
weight: normal; color: #000000; background-color: #CC3366; border: 1px
#FFFFFF solid }
.but4 { font-family: Arial, Helvetica, sans-serif; font-size: 12px; font-
weight: normal; color: #000000; background-color: #FFFF66; border: 1px
#FFFFFF solid }
-->
</style>
</head>
<body bgcolor="#000000" text="#FFFFFF">
<form ACTION="<%=MM_editAction%>" METHOD="POST" name="add_job_form">
<table width="660" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><img src="../Images/logo.gif" width="367" height="85"><br>
<br>
</td>
</tr>
<tr>
<td>
<table width="95%" border="0" cellspacing="0" cellpadding="0"
align="center">
<tr>
<td width="309"> <a href="browse_client.asp"><img
src="../Images/jobs.gif" width="38" height="20" border="0"></a>
<img src="../Images/reports.gif" width="64" height="20">
<img src="../Images/time_sheet.gif" width="92" height="20">
<a href="search.asp"><img src="../Images/search.gif"
width="56" height="20" border="0"></a>
</td>
<td width="38"> <img src="../Images/transparent.gif"
width="24" height="11" border="0"></td>
<td width="37"><img src="../Images/transparent.gif" width="24"
height="11" border="0">
</td>
<td width="37"><img src="../Images/transparent.gif" width="24"
height="11" border="0">
</td>
<td width="37"> <img src="../Images/transparent.gif"
width="24" height="11" border="0" name="Image1"></td>
<td width="16"> </td>
<td width="75">
<input type="image" name="Submit" value="Submit"
src="../Images/submit.gif" width="56" height="20">
</td>
<td width="78"> <img src="../Images/print.gif" width="35"
height="20"></td>
</tr>
</table>
</td>
</tr>
</table>
<table width="650" border="0" cellspacing="0" cellpadding="5">
<tr>
<td width="63"> </td>
<td width="222"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="2" class="option" valign="top" rowspan="2">
<div align="right">
<table width="292" border="0" cellspacing="0" cellpadding="5"
height="100%">
<tr>
<td width="65" class="option" height="44">
<div align="right">Job No: </div>
</td>
<td width="227">
<%Call GetJobNumber()%> <input type="hidden"
name="Jobnumber1"> <%add_job_form.Jobnumber1=jn%>
</td>
</tr>
<tr>
<td width="65" class="option" height="44">
<div align="right">Invoice no: </div>
</td>
<td width="227">
<input type="text" name="invoicenumber" class="box2"
size="30">
</td>
</tr>
<tr>
<td width="65" class="option" height="44">
<div align="right">Cust Ord: </div>
</td>
<td width="227">
<input type="text" name="custordnumber" class="box2"
size="30">
</td>
</tr>
<tr>
<td width="65" class="option" height="44">
<div align="right">Cust Job: </div>
</td>
<td width="227">
<input type="text" name="custjobnumber" class="box2"
size="30">
</td>
</tr>
<tr>
<td width="65" class="option" height="44">
<div align="right">Job Title: </div>
</td>
<td height="34" width="227">
<input type="text" name="job_title" class="box2" size="30">
</td>
</tr>
</table>
</div>
</td>
<td height="42" valign="top" width="63"><span
class="option">Product:</span><br>
</td>
<td height="42" valign="top">
<input type="text" name="PRODUCT" class="box2" size="30">
</td>
</tr>
<tr>
<td valign="top"> </td>
<td valign="top"> </td>
</tr>
<tr>
<td width="63" height="44" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Company:</font></div>
</td>
<td width="222" height="44">
<input type="text" name="Company" size="30" class="box2" value="<%
(Recordset2.Fields.Item("COMPANY").Value)%>">
</td>
<td height="44">
<div align="right"></div>
</td>
<td height="44"> </td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Address
1:</font></div>
</td>
<td width="222">
<input type="text" name="DELIVADD1" size="30" class="box2"
value="<%=(Recordset2.Fields.Item("ADDRESS1").Value)%>">
</td>
<td>
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Date
In: </font></div>
</td>
<td>
<input type="text" name="DateIn" size="30" class="box"
value="DD/MM/YYYY">
</td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Address
2:</font></div>
</td>
<td width="222">
<input type="text" name="DELIVADD2" size="30" class="box2"
value="<%=(Recordset2.Fields.Item("ADDRESS2").Value)%>">
</td>
<td>
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Account
Exec:</font></div>
</td>
<td>
<select name="account_exec" class="box2" >
<option value="Gary" selected>Gary Wedekind </option>
<option value="Tim">Tim Vaughan </option>
<option value="Sam">Sam Mckenzie </option>
</select>
</td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">City:</font></div>
</td>
<td width="222">
<input type="text" name="DELIVCITY" size="30" class="box2"
value="<%=(Recordset2.Fields.Item("CITY").Value)%>">
</td>
<td>
<div align="right"></div>
</td>
<td> </td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">County:</font></div>
</td>
<td width="222">
<input type="text" name="County" size="30" class="box2" value="<%
(Recordset2.Fields.Item("COUNTY").Value)%>">
</td>
<td>
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Delivery:
</font></div>
</td>
<td>
<div align="left">
<input type="text" name="DeliveryMethod" size="30" class="box"
value="<%=(Recordset2.Fields.Item("DELIVMETH").Value)%>">
</div>
</td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Post
Code:</font></div>
</td>
<td width="222" valign="top">
<input type="text" name="DELIVPCODE" size="30" class="box2"
value="<%=(Recordset2.Fields.Item("POSTCODE").Value)%>">
</td>
<td valign="top">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">COD:</font></div>
</td>
<td valign="top">
<input type="checkbox" name="cod">
</td>
</tr>
<tr>
<td width="63" class="option"> </td>
<td width="222" valign="top"> </td>
<td valign="top"> </td>
<td valign="top"> </td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Telephone:</font></div>
</td>
<td width="222" valign="top">
<input type="text" name="Telephone" size="30" class="box" value="<%
=(Recordset2.Fields.Item("TELEPHONE").Value)%>">
</td>
<td valign="top">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Comments:</font></div>
</td>
<td rowspan="3" valign="top">
<textarea name="comments" cols="32" rows="5"
class="box"></textarea>
</td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Fax:</font></div>
</td>
<td width="222" valign="top">
<input type="text" name="Fax" size="30" class="box" value="<%
(Recordset2.Fields.Item("FAX").Value)%>">
</td>
<td valign="top"> </td>
</tr>
<tr>
<td width="63" class="option">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">E-mail:</font></div>
</td>
<td width="222" valign="top">
<input type="text" name="Email" size="30" class="box" value="<%
(Recordset2.Fields.Item("EMAIL").Value)%>">
</td>
<td valign="top"> </td>
</tr>
<tr>
<td width="63" class="option" height="0">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Web:</font></div>
</td>
<td width="222" valign="top" height="23">
<input type="text" name="Web" size="30" class="box" value="<%
(Recordset2.Fields.Item("WEB").Value)%>">
</td>
<td valign="top">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Account
Info:</font></div>
</td>
<td rowspan="3" valign="top">
<textarea name="account_info" cols="32" rows="5"
class="box"></textarea>
</td>
</tr>
<tr>
<td width="63" class="option" height="0" valign="top">
<div align="right"><font face="Arial, Helvetica, sans-serif"
size="4" class="option">Contact
Name:<br>
<br>
<br>
Contact Name 2: </font></div>
</td>
<td valign="top" height="54">
<p>
<input type="text" name="contact_Name1" size="30" class="box">
</p>
<p>
<input type="text" name="contact_Name2" size="30" class="box">
<br>
<br>
</p>
</td>
<td valign="top"> </td>
</tr>
<tr>
<td width="63" class="option" height="0" valign="top">
<div align="right"></div>
</td>
<td width="222" valign="top" height="47"> </td>
<td valign="top"> </td>
</tr>
</table>
<input type="hidden" name="MM_insert" value="true">
</form>
</body>
</html>
<%
Recordset1.Close()
%>
<%
Recordset2.Close()
%>
I would reaaly appreciate any help that could be offered.
Kind regards,
Simon.
Message #2 by "Ken Schaefer" <ken@a...> on Mon, 8 Oct 2001 13:10:01 +1000
|
|
Hi Simon,
You posted 565 lines of code. Do you seriously expect us to search through
this entire thing trying to find out what might be wrong?
Please post about 10-20 lines of *relevant* code (you should have the line
number from the error message), plus the error code (which you did), and the
associated error message (since some error codes have a number of
corresponding messages).
I was going to suggest leaving the () off the call to the .Close method
since it does not return a value, and you can't use paranthesis in VBScript
when calling a routine that has no return value without using the Call
keyword, but that said, your error might be because you closed the recordset
already (and thus you can't close it again...), so please provide the error
message you are getting.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <srobinson@g...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, October 05, 2001 10:09 AM
Subject: [asp_databases] ADODB.Recordset error '800a0c93'
: Hi All,
:
: I am in desperate need of some help!
:
: I have been experiencing inumerable problems while trying to implement the
: following code. The most recent error is ADODB.Recordset error '800a0c93'
:
: The error occurs when I try to close the first record set:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<snipped for your viewing comfort>
Message #3 by David Cameron <dcameron@i...> on Mon, 8 Oct 2001 10:08:38 +1000
|
|
Where to begin...
1. Never let any program generate code for you. Ever. Period. Not even
Ultradev. This has generated a number of problems in your code
(incomprehensible variable names, use of numbers rather than constants with
ADO Objects, inefficient code).
2. You are referencing Recordset1 inside your fn GetJobNumber. Recordset1 is
defined outside the function and is not passed through so is outside the
scope of the function. Therefore you cannot reference it.
regards
David Cameron
nOw.b2b
Message #4 by "Michael Seils" <mseils@s...> on Mon, 8 Oct 2001 03:37:56
|
|
Hi Simon,
The answer to the first problem is that you don't need the () when using
the .close property. Instead of "Recordset1.close()", just put
Recordset1.close, then put: Set Recordset1= Nothing
This gets rid of the object all together after the connection is closed.
Sort of like destroying the phone call after the connection. Hope this
helps, at least with the first part.
Mike
|
|
|
View Cart
