What's the correct way to write this query

[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?

[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.

[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.

[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.

[Mr.M]

Unrelated suggestion - in some databases

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

[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.

[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?

[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