Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

T

Tim Gaunt

Hi,

I'm hoping that someone will be able to help me with this one, I'm
developing an application which basically inserts a load of data into the
database ready for validation at a later date, I wrote an INSERT statement
that works fine however today I decided that as part of the application
upgrade it should be return the record ID so I wrote the following which I
admit is a little messy at places but it works. However when I added all the
fields it's now returning the error "Arguments are of the wrong type, are
out of acceptable range, or are in conflict with one another". I've been
going through it for a while now any can't work out why its happening, has
anyone got any ideas?

Cheers.

Tim
---------------
CODE
---------------

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/delayed.asp" -->
<!--#include file="adovbs.inc" -->
<%
If Request.Form("SaveForm")<>"" Then
%>
<%
Function InsertRecord(tblName, ArrFlds, ArrValues)
' This function recieves a tablename and an Array of
' Fieldnames and an Array of field Values.
' It returns the ID of the record that has been inserted.

' Turn error handling on.
On Error Resume Next

dim cnnInsert, rstInsert, thisID
Dim MM_delayed_STRING
MM_delayed_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\Delay\DELAYED.mdb"

' Object instantiation.
Set cnnInsert = Server.CreateObject ("ADODB.Connection")
Set rstInsert = Server.CreateObject ("ADODB.Recordset")

' Open our connection to the database.
cnnInsert.open MM_delayed_STRING

' Open our Table (using the tblName that we passed in).

rstInsert.Open tblName, cnnInsert, adOpenKeyset, adLockOptimistic,
adCmdTable

' Call the AddNew method.
rstInsert.AddNew ArrFlds, ArrValues

' Commit the changes.
rstInsert.Update

' Retrieve the ID.
thisID = rstInsert("CFNID")

If Err.number = 0 Then
' If the Err.number = 0 then close everything and
' return the ID.

rstInsert.Close
Set rstInsert = Nothing
cnnInsert.close
Set cnnInsert = Nothing

InsertRecord = thisID

Else
' Woops, an error occurred, close everything and display an
' error message to the user.
rstInsert.Close
Set rstInsert = Nothing
cnnInsert.close
Set cnnInsert = Nothing

' Call our re-useable Error handler function.
call myErrorHandler()

End If

End Function

Function DField(dtf)
If (Request.Form(dtf)<>"") Then
DField = "#" & Request.Form(dtf) & "#"
Else
DField = "Null"
End If
End Function

Function ChkBox(chk)
If (Request.Form(chk)<>"") Then
ChkBox = Request.Form(chk)
Else
ChkBox = "Null"
End If
End Function

Function TxtField(txt)
TxtField = Replace(Request.Form(txt),"'","''")
End Function
%>
<%

Dim ThisRecordID, arr1, arr2

arr1 = Array("PasID", "Pref_Name", "Pat_Alerts", "Home_Access",
"Home_Hazards", "GP_Email", "Ref_Name", "Ref_Team", "Ref_Add1", "Ref_Add2",
"Ref_Add3", "Ref_Add4", "Ref_AddPC", "Ref_Tel", "Ref_Email", "Ref_Type",
"Ref_Sign", "Ref_SignDate", "RRA", "PACIO", "ONEI", "AOFC", "HAI_Att12m",
"HAI_Date1", "HAI_Reason1", "HAI_Ward1", "HAI_Date2", "HAI_Reason2",
"HAI_Ward2", "HAI_Date3", "HAI_Reason3", "HAI_Ward3", "HAI_Date4",
"HAI_Reason4", "HAI_Ward4", "ANSPSU", "EC_Forename", "EC_Surname",
"EC_Relation", "EC_Add1", "EC_Add2", "EC_Add3", "EC_Add4", "EC_AddPC",
"EC_ConNum", "MC_Forename", "MC_Surname", "MC_Relation", "MC_Add1",
"MC_Add2", "MC_Add3", "MC_Add4", "MC_AddPC", "MC_ConNum", "MC_CAO",
"MC_CACom", "MC_AIType", "MC_AIOther", "MC_AICom", "Pat_User", "CC_AssDur",
"CC_AssPre", "CC_AssReas", "AI_ComSup", "AI_PFL", "AI_Occ", "AI_OccDet",
"AI_PropType", "AI_PropTen", "AI_CliGroup1", "AI_CliGroup2", "AI_PropLive",
"ExDisDate")
arr2 = Array(TxtField("PasID") & ", " & TxtField("Pref_Name") & ", " &
TxtField("Pat_Alerts") & ", " & TxtField("Home_Access") & ", " &
TxtField("Home_Hazards") & ", " & TxtField("GP_Email") & ", " &
TxtField("Ref_Name") & ", " & TxtField("Ref_Team") & ", " &
TxtField("Ref_Add1") & ", " & TxtField("Ref_Add2") & ", " &
TxtField("Ref_Add3") & ", " & TxtField("Ref_Add4") & ", " &
TxtField("Ref_AddPC") & ", " & TxtField("Ref_Tel") & ", " &
TxtField("Ref_Email") & ", " & TxtField("Ref_Type") & ", " &
TxtField("Ref_Sign") & ", " & DField("Ref_SignDate") & ", " &
TxtField("RRA") & ", " & TxtField("PACIO") & ", " & TxtField("ONEI") & ", "
& TxtField("AOFC") & ", " & TxtField("HAI_Att12m") & ", " &
DField("HAI_Date1") & ", " & TxtField("HAI_Reason1") & ", " &
TxtField("HAI_Ward1") & ", " & DField("HAI_Date2") & ", " &
TxtField("HAI_Reason2") & ", " & TxtField("HAI_Ward2") & ", " &
DField("HAI_Date3") & ", " & TxtField("HAI_Reason3") & ", " &
TxtField("HAI_Ward3") & ", " & DField("HAI_Date4") & ", " &
TxtField("HAI_Reason4") & ", " & TxtField("HAI_Ward4") & ", " &
TxtField("ANSPSU") & ", " & TxtField("EC_Forename") & ", " &
TxtField("EC_Surname") & ", " & TxtField("EC_Relation") & ", " &
TxtField("EC_Add1") & ", " & TxtField("EC_Add2") & ", " &
TxtField("EC_Add3") & ", " & TxtField("EC_Add4") & ", " &
TxtField("EC_AddPC") & ", " & TxtField("EC_ConNum") & ", " &
TxtField("MC_Forename") & ", " & TxtField("MC_Surname") & ", " &
TxtField("MC_Relation") & ", " & TxtField("MC_Add1") & ", " &
TxtField("MC_Add2") & ", " & TxtField("MC_Add3") & ", " &
TxtField("MC_Add4") & ", " & TxtField("MC_AddPC") & ", " &
TxtField("MC_ConNum") & ", " & ChkBox("MC_CAO") & ", " &
TxtField("MC_CACom") & ", " & TxtField("MC_AIType") & ", " &
TxtField("MC_AIOther") & ", " & TxtField("MC_AICom") & ", " &
TxtField("Pat_User") & ", " & ChkBox("CC_AssDur") & ", " &
ChkBox("CC_AssPre") & ", " & TxtField("CC_AssReas") & ", " &
ChkBox("AI_ComSup") & ", " & TxtField("AI_PFL") & ", " & TxtField("AI_Occ")
& ", " & TxtField("AI_OccDet") & ", " & TxtField("AI_PropType") & ", " &
TxtField("AI_PropTen") & ", " & TxtField("AI_CliGroup1") & ", " &
TxtField("AI_CliGroup2") & ", " & TxtField("AI_PropLive") & ", " &
DField("ExDisDate"))

ThisRecordID = InsertRecord("ContactAssesment", arr1, arr2)

Response.Write ThisRecordID
%>
<%
End If
%>
<html>
<head>
<title>Assesment Added</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<script language="VBScript" Runat="Server">
Sub myErrorHandler()

If Err.Number <> 0 Then

'Declare CDONTS related variables.
Dim objCDO
Dim strName
Dim strBody
Dim errTitle
'Declare error page related variables.
Dim pageURL
Dim theDate
Dim mailto
Dim mailfrom
Dim backto

backto = Request.ServerVariables ("HTTP_REFERER")
pageURL = Request.ServerVariables ("SCRIPT_NAME")
theDate = Now
mailto = "(e-mail address removed)"
mailfrom = "(e-mail address removed)"
errTitle = "Error reported on page: " & pageURL & " at " & theDate

' Display information to the user informing them that an error has
occurred..
Response.Write "<DIV ALIGN=""center"">" & vbCrLf
Response.Write "<TABLE>" & vbCrLf
Response.Write "<TR BGCOLOR=""Maroon"">" & vbCrLf
Response.Write "<TH COLSPAN=""2"">The following error has
occurred:</TH>" & vbCrLf
Response.Write "</TR><TR>" & vbCrLf
Response.Write "<TH>Error number:</TH>" & vbCrLf
Response.Write "<TD>" & Err.Number & "</TD>" & vbCrLf
Response.Write "</TR><TR>" & vbCrLf
Response.Write "<TH>Error description:</TH>" & vbCrLf
Response.Write "<TD>" & Err.Description & "</TD>" & vbCrLf
Response.Write "</TR><TR>" & vbCrLf
Response.Write "<TH>Error source:</TH>" & vbCrLf
Response.Write "<TD>" & Err.Source & "</TD>" & vbCrLf
Response.Write "</TR><TR>" & vbCrLf
Response.Write "<TH>Page:</TH>" & vbCrLf
Response.Write "<TD>" & pageURL & "</TD>" & vbCrLf
Response.Write "</TR><TR>" & vbCrLf
Response.Write "<TH>Date/Time:</TH>" & vbCrLf
Response.Write "<TD>" & Now & "</TD>" & vbCrLf
Response.Write "</TR>" & vbCrLf
Response.Write "</TABLE>" & vbCrLf
Response.Write "<P>An e-mail has been sent to the webmaster"
Response.Write " advising of the error.</P>" & vbCrLf
Response.Write "<P>We apologize for any inconvenience</P>" & vbCrLf
Response.Write "<P><a href=""" & backto & """>" & vbCrLf
Response.Write "Click here to return to the previous page</a></P>" &
vbCrLf
Response.Write "</DIV>" & vbCrLf

' Send a fault report to the administration people (*that's you
:)* ).
Set objCDO=server.CreateObject("CDONTS.NewMail")

objCDO.To = mailto
objCDO.From = mailfrom
objCDO.Subject = errTitle
objCDO.Importance = 2

' Build a string for the body of the e-mail, detailing information
' about the error information (which we get by interrogating the
properties
' of the Err object and also display information obtained from the
' ServerVariables this will help with debugging.
strBody = "The following error has occurred:" & vbCrLf
strBody = strBody & "Error number: " & Err.Number & vbCrLf
strBody = strBody & "Error description: " & Err.Description & vbCrLf
strBody = strBody & "Error source: " & Err.Source & vbCrLf
strBody = strBody & "========================" & vbCrLf & vbCrLf

'Display the ServerVariables
strBody = strBody & "Information ServerVariables collection:" &
vbCrLf & vbCrLf

For Each strName In Request.ServerVariables
strBody = strBody & strName & " - " &
Request.ServerVariables(strName) & vbCrLf
Next

objCDO.Body = strBody
objCDO.Send 'Send the e-mail

Set objCDO = Nothing 'Tidy up

' End Processing.
Response.End

End If

End Sub
</Script>
</head>

<body>
Record added for <%= Request.Form("PasID") %>
</body>
</html>
 
R

Ray at

It'll be very hard to determine what's wrong with all that code and html in
here. Can you narrow it down to the line with the error? And on that line,
response.write the SQL string that is being executed?

This is one of the reasons that, if at all possible, you should code your
ASP code yourself instead of having Dreamweaver do it. The code is
difficult on the eyes, and it's much harder to get community support with
such code. But I'm sure we can at least try... But please try to trim away
all that irrelvant HTML and just focus on the area with the error.

Ray at work

Tim Gaunt said:
Hi,

I'm hoping that someone will be able to help me with this one, I'm
developing an application which basically inserts a load of data into the
database ready for validation at a later date, I wrote an INSERT statement
that works fine however today I decided that as part of the application
upgrade it should be return the record ID so I wrote the following which I
admit is a little messy at places but it works. However when I added all the
fields it's now returning the error "Arguments are of the wrong type, are
out of acceptable range, or are in conflict with one another". I've been
going through it for a while now any can't work out why its happening, has
anyone got any ideas?

Cheers.

Tim
---------------
CODE
---------------

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/delayed.asp" -->
<!--#include file="adovbs.inc" -->
<%
Click to expand...
<trim>
 
T

Tom B

Unfortunately, like Ray, I'm not going to wade through all of your
Dreamweaver code. I did read your first paragraph and figured that since
you knew how to do an INSERT you could probably use @@IDENTITY

An example....

sSQL="INSERT INTO tblName (columnNameA, columnNameB) VALUES(valueA, valueB)"
anActiveADOConnection.Execute sSQL
Set RS=anActiveADOConnection.Execute("SELECT @@IDENTITY")

aVariableToRepresentTheNewRowsIdentity=RS.Fields(0)

Response.Write "The New Identity is......" &
aVariableToRepresentTheNewRowsIdentity


I know there's an FAQ at http://www.aspfaq.com but I can't seem to get there
right now.
 
R

Ray at

I know there's an FAQ at http://www.aspfaq.com but I can't seem to get there
right now.
Click to expand...

It's been down for quite some time now. When it comes back up, everyone
should be cool and donate money to the site and maybe the owner can hire
someone to kill the person who caused teh outage.

Ray at home
 
T

Tim Gaunt

That wasn't dreamweaver generated code it is completley hardcoded using
Notepad, the only thing Dreamweaver there is the MM_delayed_STRING which is
from the previous design, the problem occurs on the AddNew line which uses
the exact same statement as the INSERT statement (which works fine). The
values from the form are passed through a function which assigns the correct
deliminators ('' for text, ## for date nothing for Bolean). Hope that
explains it a little further. I cannot use @@IDENTITY because this is sadly
developed in an Access dB.

Any other ideas?

------------
Line that error occurs at:

' Call the AddNew method.
rstInsert.AddNew ArrFlds, ArrValues

Values inserted:
ArrFlds= Array("PasID", "Pref_Name", "Pat_Alerts", "Home_Access",
"Home_Hazards", "GP_Email", "Ref_Name", "Ref_Team", "Ref_Add1", "Ref_Add2",
"Ref_Add3", "Ref_Add4", "Ref_AddPC", "Ref_Tel", "Ref_Email", "Ref_Type",
"Ref_Sign", "Ref_SignDate", "RRA", "PACIO", "ONEI", "AOFC", "HAI_Att12m",
"HAI_Date1", "HAI_Reason1", "HAI_Ward1", "HAI_Date2", "HAI_Reason2",
"HAI_Ward2", "HAI_Date3", "HAI_Reason3", "HAI_Ward3", "HAI_Date4",
"HAI_Reason4", "HAI_Ward4", "ANSPSU", "EC_Forename", "EC_Surname",
"EC_Relation", "EC_Add1", "EC_Add2", "EC_Add3", "EC_Add4", "EC_AddPC",
"EC_ConNum", "MC_Forename", "MC_Surname", "MC_Relation", "MC_Add1",
"MC_Add2", "MC_Add3", "MC_Add4", "MC_AddPC", "MC_ConNum", "MC_CAO",
"MC_CACom", "MC_AIType", "MC_AIOther", "MC_AICom", "Pat_User", "CC_AssDur",
"CC_AssPre", "CC_AssReas", "AI_ComSup", "AI_PFL", "AI_Occ", "AI_OccDet",
"AI_PropType", "AI_PropTen", "AI_CliGroup1", "AI_CliGroup2", "AI_PropLive",
"ExDisDate")

ArrValues= Array(TxtField("PasID") & ", " & TxtField("Pref_Name") & ", " &
TxtField("Pat_Alerts") & ", " & TxtField("Home_Access") & ", " &
TxtField("Home_Hazards") & ", " & TxtField("GP_Email") & ", " &
TxtField("Ref_Name") & ", " & TxtField("Ref_Team") & ", " &
TxtField("Ref_Add1") & ", " & TxtField("Ref_Add2") & ", " &
TxtField("Ref_Add3") & ", " & TxtField("Ref_Add4") & ", " &
TxtField("Ref_AddPC") & ", " & TxtField("Ref_Tel") & ", " &
TxtField("Ref_Email") & ", " & TxtField("Ref_Type") & ", " &
TxtField("Ref_Sign") & ", " & DField("Ref_SignDate") & ", " &
TxtField("RRA") & ", " & TxtField("PACIO") & ", " & TxtField("ONEI") & ", "
& TxtField("AOFC") & ", " & TxtField("HAI_Att12m") & ", " &
DField("HAI_Date1") & ", " & TxtField("HAI_Reason1") & ", " &
TxtField("HAI_Ward1") & ", " & DField("HAI_Date2") & ", " &
TxtField("HAI_Reason2") & ", " & TxtField("HAI_Ward2") & ", " &
DField("HAI_Date3") & ", " & TxtField("HAI_Reason3") & ", " &
TxtField("HAI_Ward3") & ", " & DField("HAI_Date4") & ", " &
TxtField("HAI_Reason4") & ", " & TxtField("HAI_Ward4") & ", " &
TxtField("ANSPSU") & ", " & TxtField("EC_Forename") & ", " &
TxtField("EC_Surname") & ", " & TxtField("EC_Relation") & ", " &
TxtField("EC_Add1") & ", " & TxtField("EC_Add2") & ", " &
TxtField("EC_Add3") & ", " & TxtField("EC_Add4") & ", " &
TxtField("EC_AddPC") & ", " & TxtField("EC_ConNum") & ", " &
TxtField("MC_Forename") & ", " & TxtField("MC_Surname") & ", " &
TxtField("MC_Relation") & ", " & TxtField("MC_Add1") & ", " &
TxtField("MC_Add2") & ", " & TxtField("MC_Add3") & ", " &
TxtField("MC_Add4") & ", " & TxtField("MC_AddPC") & ", " &
TxtField("MC_ConNum") & ", " & ChkBox("MC_CAO") & ", " &
TxtField("MC_CACom") & ", " & TxtField("MC_AIType") & ", " &
TxtField("MC_AIOther") & ", " & TxtField("MC_AICom") & ", " &
TxtField("Pat_User") & ", " & ChkBox("CC_AssDur") & ", " &
ChkBox("CC_AssPre") & ", " & TxtField("CC_AssReas") & ", " &
ChkBox("AI_ComSup") & ", " & TxtField("AI_PFL") & ", " & TxtField("AI_Occ")
& ", " & TxtField("AI_OccDet") & ", " & TxtField("AI_PropType") & ", " &
TxtField("AI_PropTen") & ", " & TxtField("AI_CliGroup1") & ", " &
TxtField("AI_CliGroup2") & ", " & TxtField("AI_PropLive") & ", " &
DField("ExDisDate"))
 
B

Bob Barrows

Tim said:
That wasn't dreamweaver generated code it is completley hardcoded
using Notepad, the only thing Dreamweaver there is the
MM_delayed_STRING which is from the previous design, the problem
occurs on the AddNew line which uses the exact same statement as the
INSERT statement (which works fine). The values from the form are
passed through a function which assigns the correct deliminators (''
for text, ## for date nothing for Bolean). Hope that explains it a
little further. I cannot use @@IDENTITY because this is sadly
developed in an Access dB.

Any other ideas?
Click to expand...
http://support.microsoft.com/?kbid=232144

Bob Barrows
 
R

Ray at

What you'll need to do is Response.Write your final SQL string prior to
executing it. Like

response.write TheVariableWithYourFinalSQLQuery
Response.End

What does that give you? Does it work if you paste it into Access's query
window thing?

Ray at work
 
T

Tim Gaunt

Thats great, many thanks, I've managed to get it working now! All I wanted
to do was retreive the record ID when a user inserted a record!

Thanks again to you all.

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another ER 2
DataAdd Error: Arguments are wrong type or are out of acceptable r 1
Arguments are of the wrong type...connecting to Access DB 0
Arguments are of the wrong type...connecting to Access DB 1
"Arguments are of the wrong type, out of acceptable range" error 1
id after auto increment from a form 7
Passing value from one script on one page to another script on another page. 3
Problem sending data info with Jmail page. 2

Members online

No members online now.
Total: 49 (members: 0, guests: 49)
Robots: 418

Forum statistics

Threads
473,968
Messages
2,570,154
Members
46,702
Latest member
LukasConde

Latest Threads

Top