Invalid character value for cast specification

[Ziglesh]

I am working in an adp file with SS2K and am getting this error when running the code below. Basically, I am changing the unique identifiers of all the universities in the database. To do this, I first have to insert a new record with the new UnivID and the associated information from the old UnivID, then, of course, proceed with the pertinent steps that follow, but this is the step I'm hung up on.

I have a form in which I enter both the old UnivID and the new UnivID I want to change it to, then I click the command button with the code below. I'm not having an issue with changing the UnivID itself, but with the associated information I must insert with it. There are four types of universities (Christian, Government, Private, Other), each of which are simply bit fields in the table and passed to the sproc as adTinyInt.

Now to the strange part. The code only gives the error when one of the bit fields is set to True. When the bit fields are set to False, the code runs fine. I don't know what's wrong.

Con and Com are global variables for my connection object and command object, respectively.

Private Sub cmdCreateNewUnivID_Click()
Dim rsOldUniv As New ADODB.Recordset
Dim strOldUnivSQL As String

strOldUnivSQL = "select * from dbo.tblUniversity where UnivID = " & "'" & Me.txtOldUnivID & "'"

'Pull old university info into recordset
Set Com = New ADODB.Command
With Com
.ActiveConnection = Con
.CommandType = adCmdText
.CommandText = strOldUnivSQL
End With
Set rsOldUniv = Com.Execute

'Create new university record with new ID
Set Com = New ADODB.Command
With Com
.ActiveConnection = Con
.CommandType = adCmdStoredProc
.CommandText = "spChangeUnivCodes"
.Parameters.Append .CreateParameter("@UnivIDVal", adVarChar, adParamInput, 8, Trim(txtNewUnivID.Value))
.Parameters.Append .CreateParameter("@CountryIDVal", adVarChar, adParamInput, 2, rsOldUniv("CountryID"))
.Parameters.Append .CreateParameter("@UnivNameVal", adVarChar, adParamInput, 200, rsOldUniv("UnivName"))
.Parameters.Append .CreateParameter("@ChristUnivVal", adTinyInt, adParamInput, 1, rsOldUniv("ChristianUniv"))
.Parameters.Append .CreateParameter("@GovtUnivVal", adTinyInt, adParamInput, 1, rsOldUniv("GovtUniv"))
.Parameters.Append .CreateParameter("@PrivateUnivVal", adTinyInt, adParamInput, 1, rsOldUniv("PrivateUniv"))
.Parameters.Append .CreateParameter("@OtherUnivVal", adTinyInt, adParamInput, 1, rsOldUniv("OtherUniv"))
End With
Com.Execute

End Sub

 

[genomon]

What is the error?

Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes.

 

[Ziglesh]

Hi genomon,

The error is the title of the post, "Invalid character value for cast specification" Runtime error -2147217887 (8004e21)

 

[Ziglesh]

I should also have mentioned that I did some research on this issue, but only found information about the error with respect to datetime fields, which I'm not using. They mention that the error can happen when you are passing a parameter with the wrong datatype, but this isn't the issue I'm having.

 

[Crowley16]

try debugging/outputting the actual values in your rsOldUniv recordset

--------------------
Procrastinate Now!

 

[Ziglesh]

Well, after analyzing this, it looks like the root issue is that pulling the boolean value True (-1) into a recordset is having a very strange effect.

As expected, for all 'true' conditions, the rsOldUniv recordset will say the value of the bit field is True, and when a comparison is done, it will say it is indeed equal to -1. However, when the code executes, it does not pass as a -1 to the procedure, but simply says "True", which then returns the usual error.

Also, if I set the value of a check box on the form to one of the true bit fields, the check box will evaluate to True as well, but if I check a box myself, it evaluates to -1, which should be the same, but apparently is not.

To try to get around this, I set up an explicit if/then such that, if the bit field in the recordset is true, it will not send rsUnivOld("fieldname"), but rather, a -1. This, however, doesn't work either. It does not return an error, but the new UnivID record shows 0 for the field instead, as though it didn't recognize it and evaluated to False (as a booleans will do whether false or unknown/unrecognized).

Good grief! I don't know how to get around this now. Any ideas, I'd love to hear them.

Thanks!

 

[JoeAtWork]

Bit fields hold either 1 or 0, so this probably explains why you have a problem with -1.

I believe you need to use adBoolean instead of adTinyInt. ADO knows that it needs to translate the True (-1) to a 1 when mapping to SQL Server Bit data type.

In the stored procedure, make sure the parameters are declared as Bit data type.

 

[Ziglesh]

Thanks Joe! Looks like that did it.