check whether there is already a record in the database (LinqToSql, vb.net)

Question

I need some help at inserting my data (username and password) into a mssql database. with linq to sql this is my solution but at the -> marked line I always get an EntityCommandExecutionException. I have a html website with two textboxes where I get the String out of and send the request via javascript - ajax to vb.net.

I'm doing research now for a long time and i can't find any solution.

As you can see I want to check if there is a datarow with this username but it doesn't work in the situation when there is no datarow in the table. That means the query should be 'NULL' or am I wrong?

Public Shared Function addData(ByVal username As String, ByVal passwort As String)

    Dim db = New LoginContext
    Dim benutzer = New Benutzer
    Dim available As Boolean = False
    Dim sha As New SHA1CryptoServiceProvider

    Dim vName = (_
        From b In db.BenutzerSet 
        Where b.Name = username.ToLower() 
        Select b)

  ->  If vName.Count() = 0 Or vName Is Nothing Then
        benutzer.Name = username.ToLower()

        Dim bytesToHash() As Byte

        bytesToHash = System.Text.Encoding.ASCII.GetBytes(passwort)

        bytesToHash = sha.ComputeHash(bytesToHash)

        Dim encPassword As String = ""

        For Each b As Byte In bytesToHash
            encPassword += b.ToString("x2")
        Next

        benutzer.Passwort = encPassword
        db.BenutzerSet.Add(benutzer)
        db.SaveChanges()
        available = True
    Else
        available = False
    End If
    Return available
End Function

Thanks for your help.

What is the exact exception? Please add the exception message and stacktrace to your question. — Steven, Aug 21 '13 at 13:16

Nikolaj Zander · Accepted Answer · 2013-08-21T13:17:19.260

2

Use vName.Any() to check if there are any records in your Query!

->  If vName.Any() = false Then

also change Dim benutzer = New Benutzer to Dim benutzer As New Benutzer

edited Aug 21 '13 at 13:17

answered Aug 21 '13 at 13:11

Nikolaj Zander

1,270
9
13

Thanks but how do I have to correct my code If vName.Count() = 0 Or vName Is Nothing Then If vName.Count() = 0 Or vName.Any() Is Nothing Then I need to go forward if there is no such a user with this username or in this case when the table is still empty (no users) – Anthrax Aug 21 '13 at 13:12

score 2 · Answer 2 · edited May 23 '17 at 12:28

I don't have an answer to your question, but here's some feedback:

The check vName.Count() = 0 Or vName Is Nothing is in the wrong order since VB executes them from left to right. If vName is actually Nothing, you'll get a NullReferenceException, since vName.Count() is executed first.
LINQ operators (such as Where and Select) will actually never return Nothing, so you don't have to check vName Is Nothing; it is redundant.
Using vName.Any() has the same result as vName.Count() == 0, but is more readable, and results most of the time in a more efficient SQL query.
Very good that you are hashing your passwords. But to be safe, you should add a salt and don't use SHA as a hashing algoritm for storing passwords.

I'm a bit late but thanks for your answer, I'll make some of these changes which I haven't implemented yet! — Anthrax, Sep 18 '13 at 07:37

check whether there is already a record in the database (LinqToSql, vb.net)

2 Answers2