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

Question

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!

Which RDBMS are you using ? And what is the data type of column NAME in the ALBUM_DB table ? — GMB
– GMB, Commented Feb 6, 2019 at 14:34
Its a microsoft SQL server running as a local database. The data type is text, but i have also tried using Nchar too. — Mia Randerson
– Mia Randerson, Commented Feb 6, 2019 at 14:39

GMB · Accepted Answer · 2019-02-06 14:40:38Z

1

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.

answered Feb 6, 2019 at 14:40

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mary · Accepted Answer · 2019-02-06 18:43:24Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related