Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-23
5
Medium Priority
?
2,136 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 41

Expert Comment

by:guru_sami
ID: 33746388
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 2000 total points
ID: 33945418
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
ID: 33967033
Thank you for the replies. I'll test the potential solutions today or Monday.

-Bob
0
 

Author Closing Comment

by:bobcann
ID: 33969901
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
ID: 33970735
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

609 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