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
-----------------
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
-----------------