CODEWITHSUNDEEP
sqlsql-servervb.net
Mia Randerson
Mia Randerson

Reputation: 37

vb.net sql Check if value exists in table and translate result into a variable

I'm creating a simple relational database system, to ensure no repeated data is saved in. I have created a chunk of code to check if the value of Album is already present in the selected table:

If Album IsNot Nothing Then 'checks if ALBUM exists
    ALBUM_CHECK = New SqlCommand("SELECT ALBUM_ID FROM ALBUM_DB WHERE NAME=(@NAME)", SQLcon)
    ALBUM_CHECK.Parameters.AddWithValue("@NAME", Album)
    If ALBUM_CHECK.ExecuteScalar IsNot Nothing Then
        album_Exist = True
    Else
        album_Exist = False
    End If
End If

However this returns the error:

System.Data.SqlClient.SqlException: 'The data types text and nvarchar are incompatible in the equal to operator.'

Any ideas on how to get round this?

I believe it's not allowing me to read if the value returned is null. All help appreciated!

Upvotes: 1

Views: 768

Answers (2)

Mary
Mary

Reputation: 15091

Text is a very poor choice for an Album. Comments and explanations in line.

Private Sub OPCode()
    Dim Album As String = ""
    Dim albumExist As Boolean
    If String.IsNullOrEmpty(Album) Then
        Return
    End If
    'Using blocks ensure that your database objects are not only closed but disposed.
    'The disposal is important because they can contain unmanaged objects.
    Using SQLcon As New SqlConnection("Your connection string")
        'If Exists is a good method for large tables because it stops as soon
        'as it finds a match.
        Using ALBUM_CHECK As New SqlCommand("If Exists (SELECT 1 FROM ALBUM_DB WHERE NAME = @NAME) Select 1 Else Select 0;", SQLcon)
            'Using .Add instead of .AddWithValue tells the database what kind of
            'data we are sending. Without this we have no control over what ADO
            'decides to sent to the database.
            ALBUM_CHECK.Parameters.Add("@NAME", SqlDbType.NVarChar).Value = Album
            'The query will only return a 0 or a 1.
            SQLcon.Open
            albumExist = CBool(ALBUM_CHECK.ExecuteScalar())
        End Using
    End Using
End Sub

Upvotes: 0

GMB
GMB

Reputation: 222432

From the documentation of SQL Server 2017 :

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

I would suggest to change the datatype of this column to varchar(max), which has the same capacity in terms of storage and is properly supported by this RDBMS.

Upvotes: 1

Related Questions