Solved

Why "String[2]: the Size property has an invalid size of 0"?

Posted on 2010-09-23
5
1,599 Views
Last Modified: 2012-08-13
I've performed searched in EE and Google, and did not find anything useful. Th SP was executed in SQL Manager and it works. But, in VB.NET this message appears:

"String[2]: the Size property has an invalid size of 0"

I'm wasting too much time for this one Sub. Can anyone offer some help. I'm probably "blind" to what the problem is. Thank you.

-Bob


Public Sub CheckPhysicalExam( _

        ByRef strLineTitle As String, _

        ByRef lngLineCandidatesID As Integer, _

        ByRef strSS_Number As String, _

        ByRef ablnRequirementChecks() As Boolean)



        On Error GoTo ErrorTrap



        Const c_VTC_Result As Integer = 0

        Const c_DoctorsExpirationDate As Integer = 1

        Const c_PhysicalExamTbTitleCodes As String = "|60430|06070|60405|60440|06664|"

        Const c_PhysicalExamCode As String = "11"

        Const c_DocNoteExpirationInYears As Short = 1



        Dim utCandidateLineDates As utStartEndDates = SingleLineCandidateDateRange(lngLineCandidatesID)



        If InStr(c_PhysicalExamTbTitleCodes, strLineTitle) Then

            Dim cmd As New SqlCommand

            cmd.CommandText = "ps__employee_vtc_data_SELECT_VtcPhysicalExamResults"

            cmd.CommandType = CommandType.StoredProcedure



            cmd.Parameters.AddWithValue("@ssn", strSS_Number)

            cmd.Parameters.AddWithValue("@physical_exam__cd", c_PhysicalExamCode)



            Dim param As New SqlClient.SqlParameter

            param.Direction = ParameterDirection.Output

            param.DbType = DbType.StringFixedLength = 1

            param.ParameterName = "@physical_exam_result"

            cmd.Parameters.Add(param)



            param = New SqlClient.SqlParameter

            param.Direction = ParameterDirection.Output

            param.DbType = DbType.DateTime

            param.ParameterName = "@doctors_expiration_dt"

            cmd.Parameters.Add(param)



            Dim cnn As New Connection

            cnn.OpenConnection()

            cmd.Connection() = cnn.GetConnection



            cmd.ExecuteReader()



            Dim DoctorsExpirationDate As DateTime = cmd.Parameters("@doctors_expiration_dt").Value



            If IsDBNull(DoctorsExpirationDate) Then

                ablnRequirementChecks(rqcRequirementChecks.rqcPhysicalRequired) = True



            ElseIf DateDiff("d", CDate(DoctorsExpirationDate), utCandidateLineDates.utStartDate) <= 0 Then

                ablnRequirementChecks(rqcRequirementChecks.rqcPhysicalRequired) = True



            ElseIf DateDiff("d", CDate(DoctorsExpirationDate), utCandidateLineDates.utStartDate) > 0 Then

                If cmd.Parameters("@physical_exam_result").Value = mc_Failed Then

                    ablnRequirementChecks(rqcRequirementChecks.rqcPhysicalFailed) = True

                End If

            End If



            cnn.CloseConnection()

            cmd = Nothing

        End If



        On Error GoTo 0

        Exit Sub

ErrorTrap:

        Err.Raise(Err.Number, "LinePopulation.CheckPhysicalExam > " & Err.Source, Err.Description)

        On Error GoTo 0

    End Sub

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

ALTER PROCEDURE [dbo].[ps__employee_vtc_data_SELECT_VtcPhysicalExamResults]

(

@ssn						char(11),

@physical_exam__cd			char(2),

@physical_exam_result		char(1)			OUTPUT,

@doctors_expiration_dt		datetime		OUTPUT

)

AS



SELECT 	@physical_exam_result = vtc_result,

		@doctors_expiration_dt = doctors_expiration_dt

FROM 	employee_vtc_data

WHERE 	ssn = @ssn AND

		vtc_cd = @physical_exam__cd AND

		emp_vtc__id =

		(

			SELECT 	MAX(emp_vtc__id)

			FROM 	employee_vtc_data

			WHERE 	ssn = @ssn AND

					vtc_cd = @physical_exam__cd

		);

Open in new window

0
Comment
Question by:bobcann
  • 3
5 Comments
 
LVL 41

Expert Comment

by:guru_sami
Comment Utility
Looks may be there is some mismatch in DbType of the sql parameters mainly the output.
Make sure they are right type... and also try to set their Size explicitly e.g.
param.Size= some value
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
Comment Utility
Can you try the following

Dim param As New SqlClient.SqlParameter
            param.Direction = ParameterDirection.Output
            param.DbType = DbType.StringFixedLength
            param.ParameterName = "@physical_exam_result"
            cmd.Parameters.Add(param)

(Without = 1 add your DbType).
Perhaps you can add the following
Dim param As New SqlClient.SqlParameter
            param.Direction = ParameterDirection.Output
            param.DbType = DbType.StringFixedLength
            param.Size = 1
            param.ParameterName = "@physical_exam_result"
            cmd.Parameters.Add(param)
0
 

Author Comment

by:bobcann
Comment Utility
Thank you for the replies. I'll test the potential solutions today or Monday.

-Bob
0
 

Author Closing Comment

by:bobcann
Comment Utility
Thanks for pointing this out. It's kind of strange, but varchar when returned from an SP as OUTPUT cannot use  DbType.String as the parameter type. It gives a strange error stating the string size is zero.

Instead, it must be declared as:

param.DbType = DbType.StringFixedLength
param.Size = XXX

XXX being the size of the varchar in the DB.  After the return, TrimEnd(" ") must be used and all is well. Sheesh!

-Bob

0
 

Author Comment

by:bobcann
Comment Utility
guru_sami, sorry for not splitting the points. You did help, but it went unnoticed. My bad. Get you next time.

-Bob
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now