No mapping exists from DbType SByte to a known SqlDbType.

[dolot]

I have the following function that I'm using to build SqlCommand objects for an insert statement:

 Private Function GetParam(Name As String, type As Data.SqlDbType, value As Object)
        Dim RetVal As New Data.SqlClient.SqlParameter()
        With RetVal
            .ParameterName = Name
            .Value = value
            .DbType = type

        End With
        Return RetVal
    End Function

The problem I'm running into is when I try to add a parameter and set the DbType to 'UniqueIdentifier'. The Value in this case is a Guid and the column on the database is a UniqueIdentifier. It will throw the exception that I have in the title above. Curiously, this will happen even if I try to set the DbType before setting the Value.

So I'm guessing that UniqueIdentifier may not be a valid type?

Has anybody ran into this before?

[jmcilhinney]

First things first, why is your function not declared with a return type? For that code to compile successfully, you must have Option Strict Off. That's very bad. You should turn Option Strict On in the project properties and then fix all the errors that get flagged. You should then set it On in the VS options too, so it is On by default for all future projects.

If you had done that already then you probably would have picked up the reason for this current issue. You're setting the wrong property. You method parameter is type SqlDbType but you're setting the DbType property of your SqlParameter. That's wrong. The DbType property expects a System.Data.DbType value. If you have a System.Data.SqlDbType value then you should be assigning that to the SqlDbType property.

Did you wonder why it mentions SByte in the error message? That's because an implicit conversion is being performed, which is only allowed because you have Option Strict Off. The SqlDbType.UniqueIdentifier field that you're passing in has a numerical value of 14. In order to set the DbType property, you must have a DbType value. The DbType field with a numerical value of 14 is... DbType.SByte. Hopefully this demonstrates clearly why you should have Option Strict On. In this case, the compiler would have told you that you could not assign a SqlDbType value to the DbType property rather than trying to do what it thought you wanted it to do. It would have been even worse if this error message had not been flagged because then the code would have executed successfully but done something other than what you wanted. ALWAYS work with Option Strict On unless you explicitly need to use late-binding. Even then, create partial classes that contain the minimum code possible and set Option Strict Off at the file level.

[dolot]

Thanks for pointing out the difference between the two properties - I totally missed that detail and thought they were the same but just with different names. My bad.

You're absolutely right about Option Strict. I normally have that on in my projects, but I'm modifying this for a friend of mine and, knowing him, I'm a little afraid to turn on Option Strict for this project.

[jmcilhinney]

Thanks for pointing out the difference between the two properties - I totally missed that detail and thought they were the same but just with different names. My bad.

Interestingly, I suspected that that was the issue but consulted the documentation for confirmation and it seems that there's a bit of an error there. It says that the DbType property is System.Data.DbType but the description says "One of the SqlDbType values." and links to the System.Data.SqlDbType documentation. That had me confused for a bit.

You're absolutely right about Option Strict. I normally have that on in my projects, but I'm modifying this for a friend of mine and, knowing him, I'm a little afraid to turn on Option Strict for this project.

Hopefully you can use this episode as an example of why it is important to have Option Strict On. As I said, you were lucky in this case that it actually generated an error rather than working incorrectly but doing so silently.

[dolot]

Just noticed something in your earlier post - about setting option strict off at the file level. Didn't know you could do that - thought it was project level only. So can I do the reverse - set option strict on at the file level? Gonna look into that.

Thanks again JMC - you're always a good source of information.

[jmcilhinney]

So can I do the reverse - set option strict on at the file level?

Absolutely. All VB Options (Compare, Explicit, Strict, Infer) have a default value set at the project level and you can change each one at the file level if you want to. If Option Strict is Off by default, setting it On in one file at a time can be a little less overwhelming and does allow you to "fix" the code over a period of time rather than all at once.

[dolot]

Heh. I tried it on the file that I was working on... and found 72 type conversion errors. Got some cleaning up to do.