SQL Database problem

S

soniix

Member
Joined
Apr 18, 2003
Messages
19
i have a problem with the length of fields in a sql data table

let me say i have 3 fields in this table username, userpass and userid

all with the length of 10 bytes

on a form i have 2 textboxes for the username and the userpass
i want to compare if the username and the userpass matches the values in the database. i do this with a datareader code below

Code: 
        mainconn.Open()
        sel = New SqlCommand("SELECT * FROM users WHERE username=" & username & "", mainconn)
        Dim userread As SqlDataReader = sel.ExecuteReader()
        userread.Read()
        If userread.Item("Username") = username And userread.Item("Userpass") = password Then
            userid = userread.Item("userid")
            loggedin = True
            username = username
        Else
             loggedin = False
        End If
        useradap.Close()
        Return loggedin

in the databes there is one record stored userid = 1 username = ADMIN and userpass = admin

but if i try to compare these two values with the values the user entered i always get loggedin to false because of the length of the values. the length in the textboxes if i type in ADMIN is 5 and the length in the database for ADMIN is 10 so how can i compare these values without the left spaces in the database?
 
Some things to try...

Im not that familiar with VB.NET, but is it possible to get the length of the username and the length of the password read from the database table, then compare them to the length of the username and password entered by the user.

If it turns out that any of these are different, you may have leading or trainling white spaces for some reason. In that case, use the Trim function to strip all white spaces from the leading and trailing end of the offending strings.
 
i know this way but its to complicated there must b a shorter way too because most of the data in my tables have empty spaces because of the length of the fields
 
I am sure that the reason is not the length of the fields, except your data is stored on SQL Server and the fields are defined as CHAR(10). If you defined it like that, it means fixed length and the missing characters are filled with blanks. Then you can follow Gladimirs suggestion and use TRIM to compare the two items, or better change definition to VARCHAR(10).
Anyway I would take care on the compare option. You know, by default it is set to binary. So this is ok for comparing the password, but it sucks with the name, because normaly it should not matter if you enter the name in lower or upper cases.
So I recommend to leave OPTION COMPARE BINARY and instead use TOUPPER for this comparison.
 
thnx for help, i got it now, and apaule ur right

i looked at the northwind db and there i saw that all chat fields r nvarchar i changed this in my db and all worked fine

but my compare works becaus if i type in "admin" as username and not "ADMIN" it gives me an error
 
Back
Top