Strange Error With ASP and SQL functions. 1

[Eldaria]

I have a really strange problem,
I have created some ASP pages that all use a common code for executing Stored Procedures on an SQL server.
However, for most of the pages it works, but for one it does not, And I simply can not understand the Error Message, and where it comes from:
I have included the function code, The Line that the error message referres to is the Line where it executes I have marked it with --->>>
I have also included the Stored Procedures, so you can see that it is not a Datatype error.

Error Message at the end:
I have added a couple of Debug lines in the function this is what is showing before the error,

Help on this is higly appriciated, since I been bannging my head in the wall for several hours now. :-/
If you need anything else, let me know.

Regards.
Brian

The Function.
-----------------
Function RetSParg(strSP, ReturnRec, Fields(), FieldTypes(), RetVal, Values())
Dim cmdSP
Dim I
Set cmdSP = Server.CreateObject("ADODB.Command")
With cmdSP
.ActiveConnection = cn
.CommandText = strSP
.CommandType = adCmdStoredProc
For i = LBound(Values) to UBound(Values)
Select Case FieldTypes(i)
Case adVarChar
response.write("Field=" & Fields(i) & " - FieldType=" & FieldTypes(i) & " - Length=" & Len(Values(i)) & " - Value=" & Values(i) & "<br/>")
.Parameters.Append .CreateParameter(Fields(i), FieldTypes(i), adParamInput, Len(Values(i)), Values(i))
Case adInteger
response.write("Field=" & Fields(i) & " - FieldType=" & FieldTypes(i) & " - Value=" & Values(i) & "<br/>")
.Parameters.Append .CreateParameter(Fields(i), FieldTypes(i), adParamInput, , Values(i))
End Select
Next
Select Case ReturnRec
Case True
--->>> Set rsRet = .Execute
Case False
.Execute , , adExecuteNoRecords
End Select
Select Case RetVal
Case 0
Case Else
RetSParg = .Parameters(RetVal).Value
End Select
End With
set ParamId = Nothing
set cmdSP = Nothing
set i = nothing
End Function
-----------------


The Stored Procedures that works:
-----------------
CREATE PROC spSMS_ListTemplatesByCat
@Catid INT
AS
SELECT SMSTemplateID, SMSTemplateTitle, SMSTemplateText, SMSTemplateCatID
FROM tbl_SMS_Template
WHERE (SMSTemplateCatID = @Catid)
GO

CREATE PROC spSMS_AddCat
@categorytext VARCHAR(16)
AS
INSERT INTO tbl_SMS_Category (SMSCategoryText)
VALUES (@categorytext)
GO
-----------------

The Stored Procedure that does not work:
-----------------
CREATE PROC spSMS_AddTemplate
@title VARCHAR(20),
@text VARCHAR(160),
@catID INT
AS
INSERT INTO tbl_SMS_Template (SMSTemplateTitle, SMSTemplateText, SMSTemplateCatID)
VALUES (@title, @text, @catID)
GO
-----------------


Code in ASP Pages:

Works:
-----------------
Dim Values
Dim Fields
Dim FieldTypes
Values = Array(Request.Form("txtText"))
Fields = Array("@CategoryText")
FieldTypes = Array(adVarChar)
call RetSParg("spSMS_AddCat", False, Fields, Fieldtypes, 0, Values)
-----------------

Also Works:
-----------------
Dim Values
Dim Fields
Dim FieldTypes
Values = Array(CatID)
Fields = Array("@CatId")
FieldTypes = Array(adInteger)
call RetSParg("spSMS_ListTemplatesByCat", True, Fields, FieldTypes, 0, Values)
Do until rsRet.EOF
-----------------

Does not Work:
-----------------
Dim Values
Dim Fields
Dim FieldTypes
Values = Array(Request.Form("selCategory"), Request.Form("txtText"), Request.Form("txtTitle"))
Fields = Array("@CatID", "@Text", "@title")
FieldTypes = Array(adInteger, adVarChar, adVarChar)
call RetSParg("spSMS_AddTemplate", False, Fields, Fieldtypes, 0, Values)

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

The Result on the one that does not Work:
-----------------
Field=@CatID - FieldType=3 - Value=8
Field=@Text - FieldType=200 - Length=67 - Value=Testing a Text for a Category, this is a bit longer.
Field=@title - FieldType=200 - Length=16 - Value=Testing Title

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to int.

/Includes/commoninc.asp, line 73
-----------------

 

[Chopstik]

Your error message indicates that you are trying to pass a varchar in SQL when it is looking for an int data type. If your values are being read into the function (and thus your SQL SP) as you have them entered, then you should just need to change the order. Instead of this:

-----------------
Dim Values
Dim Fields
Dim FieldTypes
Values = Array(Request.Form("selCategory"), Request.Form("txtText"), Request.Form("txtTitle"))
Fields = Array("@CatID", "@Text", "@title")
FieldTypes = Array(adInteger, adVarChar, adVarChar)
call RetSParg("spSMS_AddTemplate", False, Fields, Fieldtypes, 0, Values)

Try this (changes highlighted in red):

-----------------
Dim Values
Dim Fields
Dim FieldTypes
Values = Array(Request.Form("selCategory"), Request.Form("txtText"), Request.Form("txtTitle"))
Fields = Array([COLOR=red]"@title"[/color], "@Text", [COLOR=red]"@CatID"[/color])
FieldTypes = Array(adInteger, adVarChar, adVarChar)
call RetSParg("spSMS_AddTemplate", False, Fields, Fieldtypes, 0, Values)

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[Eldaria]

Aha, well yes that did solve it, but why does it matter in what order I pass the parameters?
Is that not the reason why I have to pass the name and the datatype of the parameter?

But Thanks.
Regards.
Brian

 

[Chopstik]

Your SP expects them in the order in which you listed them: title, text, catID. When you call your SP, you have to pass the variables in the same order else you run into the problem you encountered earlier. The SP has no way of knowing what order you are passing them if you change them around.

To be honest, I've not used this process before, so not sure of the point of passing the name and the datatype. Perhaps someone with more experience with ASP and SQL SP's can better answer why (or even if) it is necessary to enter all of that data.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous