Is There Any Correct Syntax For ParameterDirection.Input ?

 I try to call the storeproc to perform task by allowing only input 6 of length. The syntax over here might not be relevant, im seeking for correct syntax.

 webform

Dim connString2 As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString Using myConnection2 As New SqlConnection(connString2)

Dim test01 As IntegerDim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection2)

myPuzzleCmd2.CommandType = CommandType.StoredProcedure

Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6)                 < --------   allow 6 letters in length to be input   

retLengthParam.Direction = ParameterDirection.InputmyPuzzleCmd2.Parameters.Add(retLengthParam) Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30)

retRandomCode.Direction = ParameterDirection.Output

myPuzzleCmd2.Parameters.Add(retRandomCode)

TryDim reader2 As SqlDataReader = myPuzzleCmd2.ExecuteReader()

myPuzzleCmd2.ExecuteNonQuery()

Catch ex As Exception Response.Write("sp value : " & retRandomCode.Value)

Dim iRandomCode(1) As StringReDim Preserve iRandomCode(1)

iRandomCode(1) = Convert.ToString(retRandomCode.Value)myPuzzleCmd2 = Nothing

Session.Remove("RandomCode") HttpContext.Current.Session("RandomCode") = iRandomCode(1) Response.Write(Session("RandomCode"))

Finally

myConnection2.Close()

End Try

End Using

 

StoredProcedure ALTER PROC [dbo].[GetRandomCode]

@Length TINYINT,

@RandomCode VARCHAR(30) OUTPUT

ASDECLARE

@Chars2BUsed VARCHAR(30),

@LookAt TINYINT,

@iCnt INT,

@CharFound CHAR(1)

 

SELECT @Chars2BUsed ='ABCDEFGHJKLMNPQRTUVWXYZ2346789'

SELECT @RandomCode = ''SELECT @iCnt = 1 WHILE @iCnt<=@Length

BEGINSELECT @LookAt = FLOOR((RAND()*LEN(@Chars2BUsed))) + 1

SELECT @CharFound = SUBSTRING(@Chars2BUsed, @LookAt, 1)IF CHARINDEX(@CharFound, @RandomCode)=0

BEGINSELECT @RandomCode = @RandomCode + @CharFound

SELECT @Chars2BUsed = REPLACE(@Chars2BUsed, @CharFound, '')SELECT @iCnt = @iCnt + 1

END

END