Member Avatar for Mr.M

Hi Dw.

What is the correct way to write an sql query like this? "select * from MyTable where username ='username' And password ='Password'" in vb.net.

My problem is that on my database I have some usernames repeated but they differ by their passwords. Each record has a different information. When I tried this it will just return the wrong data especially if the intended record is not in the first same username record, it will just retrieve the first same username records, but I want to force it to only retrieve all data on the record where username and password match.

Any idea?

  • 4 Contributors
  • 7 Replies
  • 263 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Mr.M

Dani AI

Generated

A few concrete points to fix the behavior you describe (returning the “first” same-username row instead of the rows that actually match):

Use a WHERE that explicitly matches the logged-in name against both columns and also applies your status filter. This returns every matching transaction row rather than stopping at the first username match:

SELECT *
FROM MyTable
WHERE (username = @user OR BookedUser = @user)
  AND Status IN ('Offered','Scheduled','Paid')
ORDER BY DueDate DESC;

In VB.NET always use a parameterized SqlCommand so values are matched exactly (no accidental string concatenation or injection). Example pattern (trimmed for clarity):

Using cn As New SqlConnection(connString)
  Using cmd As New SqlCommand("SELECT ... WHERE (username=@u OR BookedUser=@u) AND Status IN (...)", cn)
    cmd.Parameters.Add("@u", SqlDbType.NVarChar, 100).Value = username
    cn.Open()
    Using rdr = cmd.ExecuteReader()
      While rdr.Read()
        ' Process each row — use RefNo or the table primary key to identify unique transactions
      End While
    End Using
  End Using
End Using

Important troubleshooting notes tied to the thread:

  • For ’s ListView polling: don’t dedupe rows by username. Use RefNo or the primary key (or a composite of username+RefNo) when deciding whether a transaction is already shown. That’s likely why you saw only the first row.
  • ’s subquery is a valid approach, but the simple OR + IN is usually clearer and efficient when you’re matching one user value.
  • As suggested, prefer a unique login identifier (userId/email) for authentication and joins rather than relying on repeated username strings.
  • Also heed ’s reminder about reserved words and general safety: avoid reserved names or bracket them, index the user columns for performance, and never store plaintext passwords — use salted hashes.

If you want, provide the exact VB.NET code you currently use for polling/ListView update and the code that decides "duplicate" and I can point out the exact line that causes the first-row-only behavior.

Recommended Answers

All 7 Replies

Member Avatar for cereal

What happens if they choose the same password? Usernames should be unique, if you cannot then make something else unique, like email addresses and force the sign in through the unique column. By fixing this, you solve the issue.

Member Avatar for Mr.M

Yes I have some other columns which are unique as well. I have a Status which is unique. This what Happens I have a table like this:

----------------------------------------------------------------------------
username|BookedUser|Amount|Status|RefNo|DueDate    |Id     |Rec |Remainder |
----------------------------------------------------------------------------
test      |Admin      |500  |Offered  |N82XP3|05/28/2016|Offer|0   |0         |
Admin   |0        |1000 |Open     |N82XP3|05/26/2016|Owner|0   |500       |
test    |0        |6000 |Scheduled|KZ95AV|05/30/2016|Owner|0   |0         |
----------------------------------------------------------------------------

Ok My database is like this. Now taking the username and password sample I used on my first post I was actually refering to username and BookedUser columns. I also check Status as I only want to return only record with these status (Offered,Scheduled,Paid) and each status is retrieved as per the Id as actions for Offer are not the same as actions for Owner. Now the trick is that each user can be in a database unlimitedly with different Id,Status.

basically what I want to achieve is "I will just say it as is" I want to retrieve all records where this username is in BookedUser and the status for that record is one of these I mentioned that I only want to retrieve records with it. Also I want to retrieve all records with this username in username field where the status is one of the above mentioned status's.

Hope this is more clear. The reason is that both Offer and Owner are using the same function to access the database but I first retrieve the data from the database and check return the Id for that particular username so that I can be able to Identify which role or privilagies to give as they both have some limitations depending on the record status that the user is working with.

This is a Client server app. The server is the one that does database accessing, I'm using signaling to tell the server what to do and with each signal a server knows what to do and how so this is one of the things a server does when recieving a signal for this.

Member Avatar for Reverend Jim

Unrelated suggestion - in some databases, password is a reserved word. You may want to specify it in the query as [password]. It may work in some databases but if you switch to another database it may throw an error.

Member Avatar for Mr.M

Unrelated suggestion - in some databases

Ow you mean in the where section? Thanks about that didn't know of it.

Edited by Mr.M because: code additing
Member Avatar for Mr.M

Any Help with this? My problem is retrieving data of the same username in the third or so record. eg. for the above example I want to display/retrieve the second test username record not the first one but the other record. in My app it seems as if it tries to retrieve the first test record.

Member Avatar for pritaeas 
SELECT * 
FROM MyTable MT1 
WHERE Username IN (
    SELECT BookedUser 
    FROM MyTable MT2 
    WHERE MT2.BookedUser = MT1.Username 
    AND MT2.Status IN ('Offered', 'Scheduled', 'Paid')
)

Something like that?

Member Avatar for Mr.M

Mmmmm Well I can see this seem to be not easy to point out. But What I've thought was to create another table and on that table I will only add the the user who is booking another as a username then for the booked user I will add that user as a BookedUser then will have two select statements which the first one will check if the username is the same with the current logged in user and if so, then that record will be read and sent to the client. The second select will check if the BookedUser is the same with the current logged in user, if so all record with this should be read and sent to the user.

I think the same problem will occure here. Let say admin has booked test which means under the username there will be admin and under BookedUser there will be test so now I think this will also be a problem when let say test has also booked let say John. The problem will be that I'm polling the data and on the client side I first check if the data being received has the same username as that on the ListView if so I don't add that data to prevent getting the same data over and over. But what I think is that I will try to add another verification method so that I can have more then one transactions displayed on the ListView with the same username but different refnumber

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.