Solved

SQLEncode Error 800a000d

Posted on 2013-01-20
4
618 Views
Last Modified: 2013-01-24
I'm tryng to pass form values to a VBScript page that adds records to an access database. I have similar functionality that is working perfectly that I've copied from, but I just can't get this new process to work.

I'm getting the following error:
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'SQLEncode'
/racing/calendar/entryForms/2013/socir2013Validate.asp, line 39

Line 39: currentDate                = SQLEncode(Request("currentDate"))

currentDate is a text field that is a maximum of 20 characters. The testing value is: January 20, 2013

Here is the code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../../../../Connections/eventCalendar.asp" -->
<%
Dim rssocir2013__MMColParam
rssocir2013__MMColParam = "0"
If (Request.Querystring("ID") <> "") Then
  rssocir2013__MMColParam = Request.Querystring("ID")
End If
%>
<%
Dim rssocir2013
Dim rssocir2013_cmd
Dim rssocir2013_numRows

Set rssocir2013_cmd = Server.CreateObject ("ADODB.Command")
rssocir2013_cmd.ActiveConnection = MM_eventCalendar_STRING
rssocir2013_cmd.CommandText = "SELECT * FROM socir2013 WHERE ID = ?"
rssocir2013_cmd.Prepared = true
rssocir2013_cmd.Parameters.Append rssocir2013_cmd.CreateParameter("param1", 5, 1, -1, rssocir2013__MMColParam) ' adDouble

Set rssocir2013 = rssocir2013_cmd.Execute
rssocir2013_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rssocir2013_numRows = rsEvent_numRows + Repeat1__numRows
%>
<%
' On Error Resume Next

' Classsic ASP pages created by Andre F Bruton
' E-mail: andre@bruton.co.za
' Date: 2008/01/19

currentDate                = SQLEncode(Request("currentDate"))
title                      = SQLEncode(Request("title"))
firstName                  = SQLEncode(Request("firstName"))
midInit                    = SQLEncode(Request("midInit"))
lastName                   = SQLEncode(Request("lastName"))
suffix                     = SQLEncode(Request("suffix"))
sex                        = SQLEncode(Request("sex"))
birthYear1                 = SQLEncode(Request("birthYear1"))
email                      = SQLEncode(Request("email"))
address                    = SQLEncode(Request("address"))
address2                   = SQLEncode(Request("address2"))
city                       = SQLEncode(Request("city"))
stateProvince              = SQLEncode(Request("stateProvince"))
country                    = SQLEncode(Request("country"))
zipCode                    = SQLEncode(Request("zipCode"))
mobilePhone                = SQLEncode(Request("mobilePhone"))
affil                      = SQLEncode(Request("affil"))
crewName1                  = SQLEncode(Request("crewName1"))
crewEmail1                 = SQLEncode(Request("crewEmail1"))
birthYear2                 = SQLEncode(Request("birthYear2"))
crewName2                  = SQLEncode(Request("crewName2"))
crewEmail2                 = SQLEncode(Request("crewEmail2"))
birthYear3                 = SQLEncode(Request("birthYear3"))
hullNo                     = SQLEncode(Request("hullNo"))
boatName                   = SQLEncode(Request("boatName"))
hullColor                  = SQLEncode(Request("hullColor"))
natLetters                 = SQLEncode(Request("natLetters"))
sailNo                     = SQLEncode(Request("sailNo"))
deepSouth                  = SQLEncode(Request("deepSouth"))
dstssz1                    = SQLEncode(Request("dstssz1"))
dstssz2                    = SQLEncode(Request("dstssz2"))
dstssz3                    = SQLEncode(Request("dstssz3"))
dstssz4                    = SQLEncode(Request("dstssz4"))
dstssz5                    = SQLEncode(Request("dstssz5"))
dsatssz1                   = SQLEncode(Request("dsatssz1"))
dsatssz2                   = SQLEncode(Request("dsatssz2"))
dsatssz3                   = SQLEncode(Request("dsatssz3"))
dsatssz4                   = SQLEncode(Request("dsatssz4"))
dsatssz5                   = SQLEncode(Request("dsatssz5"))
dslSat                     = SQLEncode(Request("dslSat"))
dslSun                     = SQLEncode(Request("dslSun"))
dsOyster                   = SQLEncode(Request("dsOyster"))
dscbsk                     = SQLEncode(Request("dscbsk"))
midWinter                  = SQLEncode(Request("midWinter"))
mwcbsk                     = SQLEncode(Request("mwcbsk"))
winter                     = SQLEncode(Request("winter"))
mWinter                    = SQLEncode(Request("mWinter"))
spebSat                    = SQLEncode(Request("spwpThu"))
spebSat                    = SQLEncode(Request("spebSat"))
spcbsk                     = SQLEncode(Request("spcbsk"))
signature                  = SQLEncode(Request("signature"))
browser                    = Request.ServerVariables("HTTP_USER_AGENT")
ip                         = Request.ServerVariables("REMOTE_HOST")
%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/mainLayout.dwt" codeOutsideHTMLIsLocked="false" -->
.
.
.
.
.

    <div id="mainContent"> <!-- InstanceBeginEditable name="mainContent" -->
      <div id = "docHdr">
        <h3>2013 Southern Circuit Entry Submitted</h3>
      </div>
      <%       
                  ' execute the insert
                  Dim MM_editCmd
            
                  Set MM_editCmd = Server.CreateObject ("ADODB.Command")
                  MM_editCmd.ActiveConnection = MM_eventCalendar_STRING
                  MM_editCmd.CommandText = "INSERT INTO socir2013 (firstName, title, firstName, middleInitial, lastName, suffix, sex, birthYear, email, address, address2, city, state, country, zipCode, mobilePhone, yachtClub, crew1Name, crew1Email, crew1Birth, crew2Name, crew2Email, crew2Birth, hullNo, boatName, hullColor, nationalLetters, sailNo, deepSouth, dsfreeSmall, dsfreeMedium, dsfreeLarge, dsfreeXLarge, dsfreeXXLarge, dstSmall, dstMedium, dstLarge, dstXLarge, dstXXLarge, dslunchSat, dslunchSun, dsoysterDinner, dsfirstSkippering, midwinterChampionship, mwfirstSkippering, winterChampionship, winterMasters, wcWelcome, wceveningBanquet, wcfirstSkippering, signature) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"  
                  MM_editCmd.Prepared = true
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1",  202, 1, 20, Request.Form("currentDate")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2",  202, 1, 4,  Request.Form("title")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3",  202, 1, 18, Request.Form("firstName")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4",  202, 1, 1,  Request.Form("midInit")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5",  202, 1, 20, Request.Form("lastName")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6",  202, 1, 5,  Request.Form("suffix")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7",  202, 1, 6,  Request.Form("sex")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8",  202, 1, 4,  Request.Form("birthYear1")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param9",  202, 1, 60, Request.Form("email")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param10", 202, 1, 32, Request.Form("address")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param11", 202, 1, 30, Request.Form("address2")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param12", 202, 1, 24, Request.Form("city")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param13", 202, 1, 24, Request.Form("stateProvince")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param14", 202, 1, 18, Request.Form("country")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param15", 202, 1, 10, Request.Form("zipCode")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param16", 202, 1, 18, Request.Form("mobilePhone")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param17", 202, 1, 60, Request.Form("affil")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param18", 202, 1, 60, Request.Form("crewName1")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param19", 202, 1, 80, Request.Form("crewEmail1")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param20", 202, 1, 4,  Request.Form("birthYear2")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param21", 202, 1, 60, Request.Form("crewName2")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param22", 202, 1, 80, Request.Form("crewEmail2")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param23", 202, 1, 4,  Request.Form("birthYear3")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param24", 202, 1, 5,  Request.Form("hullNo")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param25", 202, 1, 24, Request.Form("boatName")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param26", 202, 1, 15, Request.Form("hullColor")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param27", 202, 1, 5,  Request.Form("natLetters")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param28", 202, 1, 6,  Request.Form("sailNo")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param28", 202, 1, 5,  Request.Form("sailNo")) ' adVarWChar
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param29", 5, 1, -1,   Request.Form("deepSouth")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param30", 5, 1, -1,   Request.Form("dstssz1")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param31", 5, 1, -1,   Request.Form("dstssz2")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param32", 5, 1, -1,   Request.Form("dstssz3")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param33", 5, 1, -1,   Request.Form("dstssz4")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param34", 5, 1, -1,   Request.Form("dstssz5")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param35", 5, 1, -1,   Request.Form("dsatssz1")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param36", 5, 1, -1,   Request.Form("dsatssz2")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param37", 5, 1, -1,   Request.Form("dsatssz3")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param38", 5, 1, -1,   Request.Form("dsatssz4")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param39", 5, 1, -1,   Request.Form("dsatssz5")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param40", 5, 1, -1,   Request.Form("dslSat")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param41", 5, 1, -1,   Request.Form("dslSun")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param42", 5, 1, -1,   Request.Form("dsOyster")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param43", 5, 1, -1,   Request.Form("dscbsk")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param44", 5, 1, -1,   Request.Form("midWinter")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param45", 5, 1, -1,   Request.Form("mwcbsk")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param46", 5, 1, -1,   Request.Form("winter")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param47", 5, 1, -1,   Request.Form("mWinter")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param48", 5, 1, -1,   Request.Form("spwpThu")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param49", 5, 1, -1,   Request.Form("spebSat")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param50", 5, 1, -1,   Request.Form("spcbsk")) ' adDouble
                  MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param51", 202, 1, 4,  Request.Form("signature")) ' adVarWChar

                  MM_editCmd.Execute
                  MM_editCmd.ActiveConnection.Close
            
                  ' append the query string to the redirect URL
                  'Dim MM_editRedirectUrl
                  'MM_editRedirectUrl = "eventValidate2.asp"
                  'If (Request.QueryString <> "") Then
                  '  If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
                  '    MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
                  '  Else
                  '    MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
                  '  End If
                  'End If
                  'Response.Redirect(MM_editRedirectUrl)
%>
          Your 2013 Southern Circuit Entry Form has been submitted</span> &nbsp; <a href="../../eventSelect.asp" class="links">ILCA Calendar</a>
      <!-- InstanceEndEditable --></div>
  </div>

Why am I getting an Encode error?
0
Comment
Question by:sherleon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
aflockhart earned 500 total points
ID: 38800779
SQLEncode is probably a function which you need to provide.

If you look at the page from which you copied the code, you may find EITHER:

a) a function definition for SQLEncode, within the page itself

or

b) a reference to an INCLUDE file which contains a function definition for SQLEncode


I would expect the function to be doing somethign like tidying up the input to deal with single quote marks.  If you can't find the existing function, you could try adding one to your page: something like this :

Function sqlEncode(x)
  sqlEncode = Replace(x, "'", "''")
End Function
0
 

Author Comment

by:sherleon
ID: 38811338
Sorry for the delay - I got sidetracked with another project. You hit the nail right on the head, and I realized it as soon as I read your comment. I had neglected to copy two includes:


<!--#include file="../../../adovbs.asp"-->
<!--#include file="../../../iasutil.asp"-->

So I added them, sure it would solve the problem. But it didn't. I'm going to be working this afternoon, trying to reproduce as closely as possible the version that works. Will get back as soon as I discover anything.

Thank you for setting me on the right track.
0
 

Author Comment

by:sherleon
ID: 38812614
Well, I've made progress. I was finally able to get the first field to insert, so I started adding one field at a time. Everything worked until I got the the phone number. It was entered as 999-999-9999.
The insert looks like this:
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param16", 202, 1, 18, mobilePhone) ' adVarWChar
The maxlength in the form is 18 characters, and the length of the field in the database is 18.
But I get the following error:

Microsoft OLE DB Provider for ODBC Drivers
 error '80040e57'
[Microsoft][ODBC Microsoft Access Driver]Invalid string or buffer length


What am I missing?
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 38813487
Sorry, -  I knew a possible answer to the first problem because I've done it myself in the past  ... but in this case, I'd just be guessing or googling, and you can do both as effectively as I can.  

If it;s the only field among many which is giving an error, look for what is different about it, either in the code, the form definitions ,or in the data entered at run time.

You may be better to post it as a seperate question, as it may not attract much attention buried in the middle of a different question.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to dynamically set the form action using jQuery.

732 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