Tech Off Thread

11 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

PLEASE HELP!!! FormatException?

Back to Forum: Tech Off
  • User profile image

    I'm getting this error when I am firing a command, and have no idea what it means. There are 2 parameters filled in the SQL command, the ID field, and RowState which equals Deleted. Both parameters are filled properly. There are other parameters for this command, but they are not needed when this error fires, so they are Nothing.

    I have no idea what string or boolean it is talking about!!! And it's really bugging me.

    System.FormatException: String was not recognized as a valid Boolean.
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    Here's the code that errors:


    If you need more details I can provide them. But I have no idea what you would need except for the SQL.

  • User profile image

    More code is necessary to construct a reasonable answer.


  • User profile image

    Check this:

    This shows my code that's getting the error, and the sql query it's trying to fire.

    If I do ProductsUpdate @ProductID = 1234, @RowState = 'Deleted' in query analyzer, it's no problem.

  • User profile image

    Me.cmdProductsUpdate.Parameters("@RowState").Value = dr.RowState.ToString

    @RowState is a char(1) so you want 'D' for 'Deleted'

    But dr.RowState for Deleted is 8

    Not sure what 8.ToString is... presumably "8"?

    Try setting the @RowState parameter, in each Case, to the letter that corresponds to that case... rather than using ToString on .RowState

  • User profile image
    Sven Groot

    Maurits wrote:
    But dr.RowState for Deleted is 8

    Not sure what 8.ToString is... presumably "8"

    True for an integer, but for an enum like this ToString will give you the name of the enum value, not its underlying value.

  • User profile image

    Might be a red herring, but...

    DataRowState is a FlagsAttribute-kind of enum, so values are not mutually exclusive... that doesn't tend to work well with Select Case.

    Perhaps a set of successive Ifs like
        If dr.RowState & DataRowState.Deleted Then
           ' do Deleted stuff
        End If

        If dr.RowState & DataRowState.Added Then
           ' do Added stuff
        End If

    ... together with some debugging statements...

    might help to isolate the problem.

    BTW, what is (DataRowState.Deleted | DataRowState.Modified).ToString(), for example?

    EDIT: Ah, I see... "Deleted, Modified" in enum order.

  • User profile image

    Maurits wrote:
    FlagsAttribute... Select Case

    Although it would be an interesting (though scary) construct if

    Select Case dr.RowState
        Case DataRowState.Added
        Case DataRowState.Deleted
        Case DataRowstate.Modified
    End Select

    were to execute each case that had a "1" in dr.RowState!

  • User profile image
    Sven Groot

    Maurits wrote:
    EDIT: Ah, I see... "Deleted, Modified" in enum order.

    That, in combination with Enum.Parse, is actually a pretty nice way to persist enum values.

  • User profile image

    yea, I've used that a lot. And for filling the other values, for an Added or modified I use this:

    Private Function CommandFill(ByVal cmd As SqlClient.SqlCommand, ByVal changeRow As DataRow, ByVal makeNull As Boolean)
     Dim curCol As DataColumn
     For Each curCol In changeRow.Table.Columns
      If makeNull Then 
        cmd.Parameters("@" & curCol.ColumnName).Value = DBNull.Value
        If cmd.Parameters.Contains("@" & curCol.ColumnName) Then cmd.Parameters("@" & curCol.ColumnName).Value = changeRow(curCol)
      End If

     Catch ex As Exception
      Throw New ApplicationException(ex.ToString)

     End Try
    End Function

    This works, and I have used it for many other queries. I found it more usefull than the dataAdapter object for me. As long as your parameters are the field names for the table with the @, it works fine, and most of, if not all of, the time that is what I do. (You can even uses different select queries on the same table w/ one update. Let's say, just a list of 2 or 3 fields in one select verses 8 or 9 in another.)

    My thing is why/where is it getting the boolean/string Format Exception? All my parameters match up exactly.

  • User profile image

    Where's the code where you create the parameter objects? I'm thinking you may have missed 1 param & are sending vThumb (varchar) in place of SummaryText (bit).

  • User profile image

    Good question. I was using the designer to look at the paramters for the SQL Command. I noticed some interesting things:




    Me.cmdProductsUpdate.CommandText = "dbo.[ProductsUpdate]"

    Me.cmdProductsUpdate.CommandType = System.Data.CommandType.StoredProcedure

    Me.cmdProductsUpdate.Connection = Me.sConn

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProductID", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProductName", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProductDesc", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProductSize", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProductTypeID", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Note", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PhotographerID", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MinPhotoCount", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MinDesignCount", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ColorLabPrint", System.Data.SqlDbType.Bit, 1))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FontRequired", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, ""))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TextNodes", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Sort", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProgramName", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PackageName", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DP2_ProductID1", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DP2_ProductID2", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DP2_HorizCmd", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ReportFlag", System.Data.SqlDbType.VarChar, 50))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PerImage", System.Data.SqlDbType.Bit, 1))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DiscountPerImage", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DiscountPerUnits", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Qty", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ImageNodes", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CropRatio", System.Data.SqlDbType.Float,Eye Rolling)

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CropX", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CropY", System.Data.SqlDbType.Int, 4))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SummaryText", System.Data.SqlDbType.Bit, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, ""))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RowState", System.Data.SqlDbType.VarChar, 1))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@vThumb", System.Data.SqlDbType.VarChar, 100))

    Me.cmdProductsUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@hThumb", System.Data.SqlDbType.VarChar, 100))

    You notice @FontRequired and @SummaryText have a bunch of parameters, the last ones being "" where the value (default value) should be. A bit certainly can't have a value of "" You don't see that or notice that in the designer. Taking that out fixed my problem.

    Thank you all very very much for helping me, sometimes another pair of eyes is such a help.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.