Solved

SQLEncode Error 800a000d

Posted on 2013-01-20
4
615 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
  • 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

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