dcsimg
Results 1 to 7 of 7
  1. #1

    Thread Starter
    Frenzied Member dolot's Avatar
    Join Date
    Nov 2007
    Location
    Music city, U.S.A.
    Posts
    1,246

    Question No mapping exists from DbType SByte to a known SqlDbType.



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

    Code:
     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?
    I always add to the reputation of those whose post are helpful, and even occasionally to those whose posts aren't helpful but who obviously put forth a valiant effort. That is, when the system will allow it.
    My war with a browser-redirect trojan

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,144

    Re: No mapping exists from DbType SByte to a known SqlDbType.

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Frenzied Member dolot's Avatar
    Join Date
    Nov 2007
    Location
    Music city, U.S.A.
    Posts
    1,246

    Re: No mapping exists from DbType SByte to a known SqlDbType.

    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.
    I always add to the reputation of those whose post are helpful, and even occasionally to those whose posts aren't helpful but who obviously put forth a valiant effort. That is, when the system will allow it.
    My war with a browser-redirect trojan

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,144

    Re: No mapping exists from DbType SByte to a known SqlDbType.

    Quote Originally Posted by dolot View Post
    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.
    Quote Originally Posted by dolot View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Frenzied Member dolot's Avatar
    Join Date
    Nov 2007
    Location
    Music city, U.S.A.
    Posts
    1,246

    Re: No mapping exists from DbType SByte to a known SqlDbType.

    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.
    I always add to the reputation of those whose post are helpful, and even occasionally to those whose posts aren't helpful but who obviously put forth a valiant effort. That is, when the system will allow it.
    My war with a browser-redirect trojan

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,144

    Re: No mapping exists from DbType SByte to a known SqlDbType.

    Quote Originally Posted by dolot View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7

    Thread Starter
    Frenzied Member dolot's Avatar
    Join Date
    Nov 2007
    Location
    Music city, U.S.A.
    Posts
    1,246

    Re: No mapping exists from DbType SByte to a known SqlDbType.

    Heh. I tried it on the file that I was working on... and found 72 type conversion errors. Got some cleaning up to do.
    I always add to the reputation of those whose post are helpful, and even occasionally to those whose posts aren't helpful but who obviously put forth a valiant effort. That is, when the system will allow it.
    My war with a browser-redirect trojan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width