Question Data TableAdapter

[Th4x3]

He im creating a user login form where my users put in a nickname and the TableAdapter searches for the matching password and returns it to my program,

I made a little code to convert the tabulardata to an single string but when i use it i get a blank msgbox where the password should be...

This is the VB code:

        Dim DataConnection As TDTableAdapters.UsersTableAdapter = New TDTableAdapters.UsersTableAdapter
                
                Dim dt As System.Data.DataTable = DataConnection.GetLoginCredentials(Nickname.Text)

                For Each dr As System.Data.DataRow In dt.Rows
                    returnValue = dr("Password").ToString()
                Next

This is the SQL String used by the TableAdadapter

SELECT        Password
FROM            Users
WHERE        (Nickname LIKE @Nickname)

And when i used this SQL code because i thought that the select values where the problem:

SELECT        Password, Nickname
FROM            Users
WHERE        (Nickname LIKE @Nickname)

I got this error code on the line: Dim dt As System.Data.DataTable = DataConnection.GetLoginCredentials(Nickname.Text)

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Did somebody encounter this problem before or just knows the solution to my problems?

Thanks in advance,
Milan v. Dijck

 

[cjard]

Right click Dataset designer surface
Choose Add.. Query
In wizard that appears set connection string
Choose "Query that returns a single value"
SQL: SELECT MAX(password) FROM users WHERE nickname = @nickname
Name it GetPasswForNick
Finish

In code:

Dim strPw as String = queriesTableAdapter.GettPasswForNick(nickTextbox.Text)
If String.IsNullOrEMpty(strPw) Then 
  MessageBox.SHow("There is no password for that nick, or no nick exists")
Else
  MessgaeBox.SHow(strPw)
End If

 

[Th4x3]

The error im gettin is:
Operand data type text is invalid for max operator.

???

 

[Th4x3]

Found something in my original code i misstyped the inputbox for the nickname but when i type it like it is supposed to i get this error:
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
on the line: Dim dt As System.Data.DataTable = DataConnection.GetLoginCredentials(Nickname.Text)

 

[Th4x3]

Fixed!

Hey ppl i fixed it

i used my own code and in the sql query i added al of my columns and that solved the error and i get my password i the msgbox

use this code:

                Dim DataConnection As TDTableAdapters.UsersTableAdapter = New TDTableAdapters.UsersTableAdapter

                Dim dt As System.Data.DataTable

                ' execute the sql query and return the table
                dt = DataConnection.GetLoginCredentials(NicknameMain.Text)

                ' Get the wanted column out of the table to a string
                For Each dr As System.Data.DataRow In dt.Rows
                    returnValue = dr("Wanted column name here").ToString()
                Next

And a table adapter with this type sql (correct it for your own database) :

SELECT        Nickname, Password, Email, Sex, DateOfBirth, Land, Name, Active, Rank, ProfilePicture
FROM            Users
WHERE        (Nickname LIKE @Nickname)

SUC6

 

[cjard]

Funny, when I run that same query against either Oracle, SQLServer or Access it doesnt return that error message.. Selecting the max of any text column simply gives you the one that is alphabetically at the end.. You don't have to use it: you can remove the MAX and it will still work fine, though which one it will return if there is more than one matching user/pass combo is undefined..