Failed to Enable Constraints (SQL/VB.NET issue)

I'm having a very annoying issue that I don't know how to fix. I've been working on a VB.NET/SQL school project for the past two weeks and have finally gotten to the very end (I think), but am running into a major hurtle that is really ticking me off. See, one part of my project has the user logging into the program to be able to access a series of tables that can be used for calculating payroll and other such things, however it's the logging in part that I'm stuck at; everything else is mostly done. Logging in has to have the program go out to the database to find the username and password, and match it to the user associated with the username and password given by the user. However, when I run this code...

Code:

---

Public Class Login_Processing
    Dim adapter As New RRBCDataSetTableAdapters.LoginTableAdapter

    Dim blnPass As Boolean

    Public Function Login(ByVal Username As String, ByVal Password As String) As Boolean
        Try
            If adapter.GetUserNames(Username).ToString = "VViscioni" Or _
                adapter.GetUserNames(Username).ToString = "Whiter" Then
                If adapter.GetPassword(Username).ToString = Password Then
                    frmMain.tsiAdmin.Enabled = True
                    frmMain.tsiEAdmin.Enabled = True
                    frmMain.tsiEPlayer.Enabled = True
                    MessageBox.Show("Welcome back!")
                    blnPass = True
                Else
                    MsgBox("Is this a new user?", MsgBoxStyle.YesNo)
                    If vbYes Then
                        AddAUser.ShowDialog()
                    Else
                        MessageBox.Show("Please re-input your password.")
                        blnPass = False
                    End If
                End If
            ElseIf adapter.GetUserNames(Username).ToString = Username Then
                If adapter.GetPassword(Username).ToString = Password Then
                    frmMain.tsiEPlayer.Enabled = True
                    MessageBox.Show("Welcome to the Roadrunners Baseball Club!")
                    blnPass = True
                Else
                    MessageBox.Show("Please re-input your password.")
                    blnPass = False
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Please re-input your username/password.")
            blnPass = False
        End Try
        Return blnPass
    End Function

End Class

---

...With these two SQL queries that go with the following adapter.query names...

GetUserName Query:

Code:

---

SELECT Login FROM Login
WHERE (Login = @Login)

---

GetPassword Query:

Code:

---

SELECT Password FROM Login
WHERE (Login = @Login)

---

...It keeps pinging me back with an error at the first If statement in my code, saying, "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Odd thing is, I checked this in the database designer, and the query builder, and there's no visible NULL fields being shown when the query is run in Visual Studio 2012! I don't understand what I'm doing wrong in my coding! Can someone help?

Hi,

How about revising your If statements to compare variable names rather than executing queries?

Ex.

Code:

---


' execute getting user name and password once.
Dim user as String
user = adapter.GetUserNames(Username).ToString()
Dim pass as String
pass = adapteradapter.GetUserNames(Password).ToString()

If user = "VViscioni" ....blah blah blah

---

Try checking your table mappings too... :)

I actually tried that, and it gave me another instance of the same issue even when assigning the query results to the variable. Is there another way?

And how do you mean the table mappings?

Check the EnforceConstraints property of the adapter. Try assigning false to it. Other scenarios will be to redo redesigning the adapter.
I don't know if its a bug on the adapter side.

Possibly, because that was one solution that was mentioned a lot, and I tried that, both with code and with the property, and neither worked either.

There's a lot that could be fixed with that code but first things first, do those GetUserNames and GetPassword methods actually return a String or does it return a DataTable? I suspect the latter and that's backed up by the error message but your code suggests the former.

There's a typo on my suggestion it should be:

Code:

---

Dim user as String user = adapter.GetUserNames(Username).ToString()
Dim pass as String
'it should be GetPassword
pass = adapteradapter.GetPassword(Password).ToString()

---

They're queries for the database. I want them to return an individual value from the table their table. However, as I understand it, it can only return a table, correct?

Quote:

---

Originally Posted by NFSRacer

They're queries for the database. I want them to return an individual value from the table their table. However, as I understand it, it can only return a table, correct?

---

No, not correct. They can do either but if they currently do return DataTables then why are you comparing the results to Strings that will obviously never match? When you create the query you get to decide what it returns.

You only need one query though. Why do you need to query the database to get the user name that you already have? Just query to get the password. If there's no match then you know that the user name doesn't exist and if there is a match then you know it does. If you want to test the user name to see if it matches something then just test what the user entered.

I guess, but what about the issue, though? Should I just drop the 'ToString' on the queries, or is there some other form of edit I need to perform? (Sorry if this post seems rushed; posted before heading to work.)

Dropping the ToString is of no use because you've still got a DataTable. I believe the issue is that both your columns are non-nullable and both your queries populate only one column, thus leaving the other containing NULL. It's the non-nullable constraint that is the issue. That's just one reason why you should be performing just one query and getting both columns if you return a DataTable. Otherwise, get rid of both those queries and create a new one on a new table adapter and configure it to return a single value, i.e. the password for a particular user name.

Okay, but I tried without the NULL check box in the table design checked and got that same result. However querying the entire table means I'd have to do a ToArray to be able to use the data, right? I mean, how can I access the password and username independantly?

UPDATE:
I do remember doing another debugging session a while back to see what the issue was, and found the string being returned in each of the queries was the column name, rather than anything else! I mean, as far as I understand it in the query, it's returning a table, but the ToString is supposed to turn the only value on that table into a string to be compared, the GetUserName searching for and returning that username in the database, and the GetPassword returning the password associated with that username. The column that keeps getting returned I named 'login', and each time I tried, it came back with that string, instead. Could that be it?

Quote:

---

Originally Posted by NFSRacer

Okay, but I tried without the NULL check box in the table design checked and got that same result. However querying the entire table means I'd have to do a ToArray to be able to use the data, right? I mean, how can I access the password and username independantly?

UPDATE:
I do remember doing another debugging session a while back to see what the issue was, and found the string being returned in each of the queries was the column name, rather than anything else! I mean, as far as I understand it in the query, it's returning a table, but the ToString is supposed to turn the only value on that table into a string to be compared, the GetUserName searching for and returning that username in the database, and the GetPassword returning the password associated with that username. The column that keeps getting returned I named 'login', and each time I tried, it came back with that string, instead. Could that be it?

---

It's not the column name. It's the table name. That's what happens when you call ToString on a DataTable: you get the value of its TableName property. The fact that you want it to do something else is irrelevant because that's what it does. The fact that you thought it was a column name is perfect example of why it's extremely poor practice to give a table and a column in that table the same name. Like I said, there's a lot to fix in that code.

Look, all you need to do is a single query. If you're going to populate a DataTable then you need to get both the user name and password columns because they're both non-nullable but if you're just going to get a value then you only need the password because you've already got the user name. You first need to decide which way you want to go. If you go the first way then your DataTable will contain zero or one record and if you go the second way you will get either a value or NULL.