Conversion Error 'Sql Server 2000" Hard one I think

[nad_scorp]

hi

I am using VB6 and SQL Server 2000

I call a stored procedure to store a new course with its related subcourses.

The subcourses are sent in delemited text format.

In the SQL Server, the stored procedure calls a function that does splitting of the text and saves it in the table.

Using the analyzer it worked just fine.
But when calling it through VB it gives a conversion error
"Error converting data type varchar to int"

I here provide the stored procedure, the function and the vb code:

==> Table SubCourses contains type as integer

The VB Code
---------------------

Function SaveRec() As Boolean
 
On Error GoTo ErrH
 
If Trim(txtCode.Text) = "" Then
MsgBox "Enter Short Course Code", vbExclamation, Me.Caption
SaveRec = False
txtCode.SetFocus
ElseIf Trim(txtNameE.Text) = "" Then
MsgBox "Enter English Name", vbExclamation, Me.Caption
SaveRec = False
txtNameE.SetFocus
Else
Dim strSubCourses As String
'----- Generate strSubCourses -------------------------------------------
strSubCourses = ""
For i = 1 To Grid.Rows - 1
If Grid.TextMatrix(i, 1) <> "" Then
strSubCourses = strSubCourses & Grid.TextMatrix(i, 1) & ";"
End If
Next i
'------------------------------------------------------------------------
 
Dim Arg(0 To 9) As String 'Arg() array holds argument used by the save stored procedure
Dim SPReturn As Long 'Return Value from the stored Procedure
Arg(0) = "%" & CurrentID 'ID It searches for ID=0 and will not find it so a new record is added with a new ID extrcacted from Table "Table_SN"
Arg(1) = "$5," & txtCode.Text
Arg(2) = "$80," & txtNameE.Text
Arg(3) = "$80," & txtNameA.Text
Arg(4) = "$10," & txtRegCateg.Text
Arg(5) = "@" & Val(txtFees.Text)
Arg(6) = "$80," & strSubCourses
Arg(7) = "$10," & CurrentUser 'User
Arg(8) = "$255," & CurrentComputer 'Computer
Arg(9) = "!0" ' 0 = Do not allow duplicate
SPReturn = ExecuteSP("SaveSubCourse", Arg, CN)
If SPReturn > 0 Then 'positive is the ID of the Record
SaveRec = True
CurrentID = SPReturn
ElseIf SPReturn = -2 Then
ans = MsgBox("The English Name is Duplicated, Do you wish to continue ?", vbYesNo + vbInformation, "Warning")
If ans = vbYes Then 'yes
Arg(10) = "!1" ' 1 = Allow duplicate
SPReturn = ExecuteSP("SaveSubCourse", Arg, CN)
Else '0=cancel
SaveRec = False
End If 'end if
ElseIf SPReturn = -1 Then
MsgBox "Code is Duplicated", vbExclamation, "Warning"
SaveRec = False
End If ' SPReturn
End If ' New Rec
Erase Arg 'Deallocates the array
Exit Function
 
On Error GoTo 0
Exit Function
 
 
'used to execute the Stored Procedure (SP)
 
Function ExecuteSP(SPName As String, Arguments() As String, ActiveCN As ADODB.Connection, Optional OutputRS As ADODB.Recordset) As Long
Dim CMD As New ADODB.Command
Dim Prm As New ADODB.Parameter
Dim PrmType As String
Dim PrmValue As String
Dim temp() As String
On Error GoTo ErrH
Set CMD.ActiveConnection = ActiveCN
CMD.CommandType = adCmdStoredProc
CMD.CommandText = SPName
Set Prm = CMD.CreateParameter("Return", adInteger, adParamReturnValue)
CMD.Parameters.Append Prm
For i = 0 To UBound(Arguments)
Set Prm = New ADODB.Parameter
PrmType = Mid(Arguments(i), 1, 1)
PrmValue = Mid(Arguments(i), 2)
Select Case PrmType
Case "#" ' Date
temp = Split(PrmValue, ",")
Prm.Type = adDate
Prm.Value = DateSerial(Val(temp(2)), Val(temp(1)), Val(temp(0)))
Case "$" ' String
temp = Split(PrmValue, ",")
Prm.Type = adVarChar
Prm.Size = Val(temp(0))
Prm.Value = temp(1)
Case "%" ' Long
Prm.Type = adInteger
Prm.Value = Val(PrmValue)
Case "@" ' Single (Float)
Prm.Type = adSingle
Prm.Value = Val(PrmValue)
Case "!" ' Boolean
Prm.Type = adBoolean
Prm.Value = Val(PrmValue)
Case "*" 'Text
Prm.Type = adVarChar
Prm.Value = Val(PrmValue)
' Prm.Value = IIf(PrmValue = "", Null, PrmValue)
End Select
CMD.Parameters.Append Prm
Next i
If OutputRS Is Nothing Then
CMD.Execute
Else
Set OutputRS = CMD.Execute
End If
ExecuteSP = CMD.Parameters(0).Value
For i = 0 To CMD.Parameters.Count - 1
CMD.Parameters.Delete (0)
Next i
Set Prm = Nothing
Set CMD = Nothing
Exit Function
 
On Error GoTo 0
Exit Function
 
ErrH:
MsgBox "Error No.:" & Err.Number & vbNewLine & "Module:Functions" & vbNewLine & "Function:ExecuteSP" & vbNewLine & "Description:" & Err.Description, vbExclamation, "WARNING !"
Set Prm = Nothing
Set CMD = Nothing
End Function

The Stored Procedure
------------------------------------

/*
This SP is used to update and insert Courses.
If the procedure succeeds it will return +ve value representing the new or current Course Code
else it will return -ve value representing an error :
(-1) The code is duplicated
(-2) The Name is duplicated (If @AllowDuplecateName is 0 only)
(-3) Connot get new id
(-100) Unknown Error
*/
CREATE PROCEDURE SaveCourse
@ID INT,
@Code VARCHAR(5), -- Course Code
@NameE VARCHAR(80), -- Course Name (Eglish Name)
@NameA VARCHAR(80),
@RegCat VARCHAR(10),
@Fees FLOAT, -- Course Fees
@SubCourses VARCHAR(80), -- Delimited string contains SubCourses ID's like '10;23;45;65; ..... ;78;'
@CurrentUser VARCHAR(10), -- Current user in application
@CurrentComputer VARCHAR(255),
@AllowDuplicateName BIT -- If @AllowDuplicateName=1 then user can enter many Courses with same name
AS
SET NOCOUNT ON
DECLARE @Temp INT
DECLARE @NewID INT
DECLARE @strTemp VARCHAR(100)

--1) Determine if the Course name (Eglish Name) is duplicated
SELECT @Temp = [ID] FROM Courses WHERE namee=@NameE AND [ID] <>@ID
IF @@ROWCOUNT>0 AND @AllowDuplicateName=0 RETURN(-2) --The name is duplicated

-- 2) Determine if the Course will be updated or inserted
SELECT @Temp = [ID] FROM Courses WHERE [ID]=@ID
IF @@ROWCOUNT=0 -- New Record
BEGIN
-- Make sure that the code is not duplicated
SELECT @Temp = [code] FROM Courses WHERE Code=@Code
IF @@ROWCOUNT>0 RETURN(-1) --The code is duplicated
EXEC GetNewID 'Courses', @NewID OUTPUT
IF @NewID<0 RETURN(-3)
BEGIN TRANSACTION
INSERT INTO Courses ([ID], code, NameE,NameA, Reg_Category, Fees, EntryUser, EntryDate) VALUES (@NewID, @code, @NameE, @NameA, @RegCat, @Fees, @CurrentUser, GETDATE())
IF @@ERROR<>0
BEGIN
ROLLBACK
RETURN (-100)
END
INSERT INTO Courses_SubCourses SELECT @NewID, CAST(Col1 AS INT) FROM Split(@SubCourses, ',', ';')
IF @@ERROR<>0
BEGIN
ROLLBACK
RETURN (-100)
END
COMMIT TRANSACTION
SET @strTemp='Insert COURCE ID = ' + CAST(@Temp as varchar(10)) + ' Code = ' + @Code
EXEC AddToLog @CurrentUser, @CurrentComputer, 'INSERT', 'COURCES', @strTemp
IF @@ERROR<>0
RETURN (-100)
ELSE
RETURN @NewID
END

ELSE -- Update Record
BEGIN
BEGIN TRANSACTION
UPDATE Courses SET code=@code, NameE=@NameE,NameA=@NameA, Reg_Category=@RegCat, Fees=@Fees, EditUser=@CurrentUser, EditDate=GETDATE() WHERE [ID] = @ID
IF @@ERROR<>0
BEGIN
ROLLBACK
RETURN (-100)
END
DELETE Courses_SubCourses WHERE Course_ID = @ID
IF @@ERROR<>0
BEGIN
ROLLBACK
RETURN (-100)
END

INSERT INTO Courses_SubCourses SELECT @ID, Col1 FROM Split(@SubCourses, ',', ';')
IF @@ERROR<>0
BEGIN
ROLLBACK
RETURN (-100)
END

COMMIT TRANSACTION
SET @strTemp='Update COURCE ID = ' + CAST(@Temp as varchar(10)) + ' Code = ' + @Code
EXEC AddToLog @CurrentUser, @CurrentComputer, 'UPDATE', 'COURCES', @strTemp
IF @@ERROR<>0
RETURN (-100)
ELSE
RETURN @ID
END
GO

The User Defined Function
-----------------------------------------
CREATE FUNCTION dbo.Split (@Data VARCHAR(80), @ColDelimiter CHAR(1)=',', @RowDelimiter CHAR(1)=';')
RETURNS @Result TABLE (
Col1 VARCHAR(100),
Col2 VARCHAR(100),
Col3 VARCHAR(100),
Col4 VARCHAR(100),
Col5 VARCHAR(100),
Col6 VARCHAR(100),
Col7 VARCHAR(100),
Col8 VARCHAR(100),
Col9 VARCHAR(100),
Col10 VARCHAR(100)
)
BEGIN
DECLARE @ColVal VARCHAR(100)
DECLARE @CurrCol TINYINT
DECLARE @Length INT
DECLARE @i INT
DECLARE @Ch CHAR(1)
DECLARE @Col1 VARCHAR(100),
@Col2 VARCHAR(100),
@Col3 VARCHAR(100),
@Col4 VARCHAR(100),
@Col5 VARCHAR(100),
@Col6 VARCHAR(100),
@Col7 VARCHAR(100),
@Col8 VARCHAR(100),
@Col9 VARCHAR(100),
@Col10 VARCHAR(100)
SET @Col1='0'
SET @Col2='0'
SET @Col3='0'
SET @Col4='0'
SET @Col5='0'
SET @Col6='0'
SET @Col7='0'
SET @Col8='0'
SET @Col9='0'
SET @Col10='0'
SET @ColVal=''
SET @CurrCol=1
SET @i=1
SET @Length=DATALENGTH(@Data)
WHILE @i<=@Length
BEGIN
SET @Ch=SUBSTRING(@Data, @i, 1)
IF @Ch=@ColDelimiter OR @Ch = @RowDelimiter
BEGIN
IF @CurrCol=1
SET @Col1=@ColVal
ELSE IF @CurrCol=2
SET @Col2=@ColVal
ELSE IF @CurrCol=3
SET @Col3=@ColVal
ELSE IF @CurrCol=4
SET @Col4=@ColVal
ELSE IF @CurrCol=5
SET @Col5=@ColVal
ELSE IF @CurrCol=6
SET @Col6=@ColVal
ELSE IF @CurrCol=7
SET @Col7=@ColVal
ELSE IF @CurrCol=8
SET @Col8=@ColVal
ELSE IF @CurrCol=9
SET @Col9=@ColVal
ELSE IF @CurrCol=10
SET @Col10=@ColVal
SET @ColVal=''
SET @CurrCol=@CurrCol+1
IF @Ch = @RowDelimiter
BEGIN
INSERT INTO @Result VALUES(@Col1, @Col2, @Col3, @Col4,@Col5, @Col6,@Col7, @Col8,@Col9, @Col10)
SET @CurrCol=1
END
END
ELSE
BEGIN
SET @ColVal = @ColVal + @Ch
END
SET @i=@i+1
END
RETURN
END


Thank you in advance

[techgnome]

While seemingly clever, this is actualy a poor way to do it. No where are the names of the parameters are being defined when you call it. Not to mention the SP being called "SaveSubCourse" doesn't correspond with the supplied SP "SaveCourse"

TG