none
Error after SQL Server 208 proc call "String[1]: the Size property has an invalid size of 0. " RRS feed

  • Question

  • Hello Group,

    I am calling a sql server 2008 proc from a .net 3.5 vb code behind page.  All the proc does it select a count. Very simple statement. When it comes back I open a data reader to loop through the record(s). Nothing to that....  after it finishes at the end of the sub, it always gives this error message below.  I have tried many different approaches trying to get this to stop without any luck. I am pasting my code below the error. Can anyone see what I am doing wrong? Thanks in advance. PS: I did try and use the "executenonquery" method as well but it does the same thing.

    [InvalidOperationException: String[1]: the Size property has an invalid size of 0.]
       System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +4873007
       System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters) +118
       System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc) +70
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +303
       System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +89
       System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +714
       System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +869
       System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +207
       System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

     

    Dim txtTeam As New Label

    txtTeam.Enabled = True

    txtTeam =

    CType(GridView1.Rows(e.RowIndex).FindControl("lblTeam"), Label)

     

    Dim fkTeamID As String = txtTeam.Text

     

    Dim txtRegion As New Label

    txtRegion.Enabled =

    True

    txtRegion =

    CType(GridView1.Rows(e.RowIndex).FindControl("lblRegion"), Label)

     

    Dim fkRegionID As String = txtRegion.Text

     

    Dim recCountTeam As Integer = 0

     

    Dim recCountRegion As Integer = 0

     

    ' see if there any of these are the last region and/or team records. if so, dont delete them.

     

    Try

     

    Dim conCk As New System.Data.SqlClient.SqlConnection(ConfigurationManager.AppSettings("StrConn").ToString())

     

    Dim myCommandCk As New System.Data.SqlClient.SqlCommand

    myCommandCk.CommandType = Data.CommandType.StoredProcedure

    myCommandCk.CommandText =

    "MITIGATION_REPORTING.DBO.delete_TEAM_REGION_ckTeam"

    myCommandCk.Connection = conCk

    conCk.Open()

     

    'team

    myCommandCk.Parameters.Add(

    New Data.SqlClient.SqlParameter("@MR_LU_TEAMS_NAME", Data.SqlDbType.VarChar, 30))

    myCommandCk.Parameters(

    "@MR_LU_TEAMS_NAME").Size = 30

    myCommandCk.Parameters(

    "@MR_LU_TEAMS_NAME").Value = fkTeamID

     

     

    Dim myReaderCk As Data.SqlClient.SqlDataReader = myCommandCk.ExecuteReader

     

     

    While (myReaderCk.Read())

    recCountTeam = recCountTeam + myReaderCk.GetSqlInt32(0)

     

    End While

     

    ' clean up

    myReaderCk.Close()

    conCk.Close()

     

    'If recCount < 2 Then

     

    ' Response.Write("<script language='javascript'>alert('Team/Region combo record cannot be deleted because it is the last entry for either the Team or the Region.');</script>")

     

    ' Exit Sub

     

    'End If

     

    Catch ex As Exception

    Response.Write(ex.Message &

    " - " & ex.StackTrace)

     

    End Try

     

    The procedure

    ALTER

     

    PROCEDURE [dbo].[delete_TEAM_REGION_ckTeam]

    (

    @MR_LU_TEAMS_NAME

    varchar(30)

    )

    AS

    BEGIN

     

    SET NOCOUNT ON;

    begin

    select

     

    count(C.MR_TEAMS_REGION_CRRLTN_PK)

    from

     

    dbo.MR_TEAMS_REGION_CRRLTN C,

    dbo

    .MR_LU_TEAMS T

    where

     

    T.MR_LU_TEAMS_PK = C.MR_TEAMS_REGION_CRRLTN_TEAMS_FK

    and

     

    T.MR_LU_TEAMS_NAME like @MR_LU_TEAMS_NAME;

    end

    end

    • Moved by Bob Beauchemin Tuesday, October 5, 2010 7:21 PM Moving to the closest relevent forum because ASP.NET forums are elsewhere (From:.NET Framework inside SQL Server)
    Tuesday, October 5, 2010 6:46 PM

Answers

  • and it still throws this error after the sub completes. 


    Lee W


    If your sub completes successfully, then its not causing the error.

    If the stack trace you posted in your first post is the complete stack trace, then it looks like something else is causing the exception to occur - possibly the 'DeleteCommand' of the SqlDataSource attached to your GridView is incorrect.

    • Marked as answer by liurong luo Friday, October 29, 2010 2:13 AM
    Thursday, October 7, 2010 6:30 PM
    Moderator

All replies

  • Why would you need to open data reader to retrieve scalar value? I think it is overhead. Since your stored procedure returns scalar value (count of rows), try to call ExecuteScalar method of myCommandCk. It will return value in one shot and you do not need rest of the logic for reader.


    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, October 6, 2010 10:17 AM
    Moderator
  • Why would you need top open data reader to retrieve scalare value? I think it is overhead. Since your stored procedure returns scalar value (count of rows), try to call ExecuteScalar method of myCommandCk. It will return value in one shot and you do not need rest of the logic for reader.


    Val Mazur (MVP) http://www.xporttools.net


    I changed it to this:

    myCommandCk.Parameters.AddWithValue(

    "@MR_LU_TEAMS_NAME", fkTeamID)

    myCommandCk.Parameters(

    "@MR_LU_TEAMS_NAME").Size = 30

    Dim count As Integer = CInt(myCommandCk.ExecuteScalar())

    and it still throws this error after the sub completes.  The sub i am doing this in is the

    GridView1_RowDeleting sub.


    Lee W
    Wednesday, October 6, 2010 1:36 PM
  • and it still throws this error after the sub completes. 


    Lee W


    If your sub completes successfully, then its not causing the error.

    If the stack trace you posted in your first post is the complete stack trace, then it looks like something else is causing the exception to occur - possibly the 'DeleteCommand' of the SqlDataSource attached to your GridView is incorrect.

    • Marked as answer by liurong luo Friday, October 29, 2010 2:13 AM
    Thursday, October 7, 2010 6:30 PM
    Moderator