[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Find an ID and select it in another table

David Trasbo

3/27/2007 9:50:00 AM

Hi group,

I am making a login system for an instant messenger. What I want is a
fast way to find a users primary key (UserID) in a "Users" table by
using the "UserName" and "UserPassword" columns.

Then check if there exists a "loginsession" with that "UserID" in the
"LoginSessions" table and then return the "SessionID" from the
"LoginSessions" table.

Can it be done with one single query?

--
David Trasbo.
6 Answers

Roy Harvey

3/27/2007 11:31:00 AM

0

SELECT A.UserID,
B.SessionID
FROM Users as A
LEFT OUTER
JOIN LoginSessions as B
ON A.UserID = B.UserID
WHERE A.UserName = 'Marx'
AND A.UserPassword = 'swordfish'

If there is no match in LoginSessions the SessionID will be NULL. If
UserName/Password are not unique then it can return multiple rows.

Roy Harvey
Beacon Falls, CT

On 27 Mar 2007 02:49:44 -0700, "David Trasbo" <davidtrasbo@gmail.com>
wrote:

>Hi group,
>
>I am making a login system for an instant messenger. What I want is a
>fast way to find a users primary key (UserID) in a "Users" table by
>using the "UserName" and "UserPassword" columns.
>
>Then check if there exists a "loginsession" with that "UserID" in the
>"LoginSessions" table and then return the "SessionID" from the
>"LoginSessions" table.
>
>Can it be done with one single query?

Rafael Lenartowicz

3/27/2007 11:35:00 AM

0

I'm not sure if I understand your question, but I think simple join between
"Users" and "LoginSessions" based on "User ID"
will return your "LoginSessions" if there is any or nothing if there is
none...
r

"David Trasbo" <davidtrasbo@gmail.com> wrote in message
news:1174988984.557961.131350@y66g2000hsf.googlegroups.com...
> Hi group,
>
> I am making a login system for an instant messenger. What I want is a
> fast way to find a users primary key (UserID) in a "Users" table by
> using the "UserName" and "UserPassword" columns.
>
> Then check if there exists a "loginsession" with that "UserID" in the
> "LoginSessions" table and then return the "SessionID" from the
> "LoginSessions" table.
>
> Can it be done with one single query?
>
> --
> David Trasbo.
>


David Trasbo

3/27/2007 12:07:00 PM

0

Roy Harvey <roy_har...@snet.net> wrote:

> >Can it be done with one single query?
> SELECT A.UserID,
> B.SessionID
> FROM Users as A
> LEFT OUTER
> JOIN LoginSessions as B
> ON A.UserID = B.UserID
> WHERE A.UserName = 'Marx'
> AND A.UserPassword = 'swordfish'
>
> If there is no match in LoginSessions the SessionID will be NULL. If
> UserName/Password are not unique then it can return multiple rows.

Thanks a lot. I'm new to SQL so I don't _really_ understand those JOIN
things, but I hope I can learn it.

--
David Trasbo.

David Trasbo

3/27/2007 12:42:00 PM

0

Roy Harvey <roy_har...@snet.net> wrote:
> SELECT A.UserID,
> B.SessionID
> FROM Users as A
> LEFT OUTER
> JOIN LoginSessions as B
> ON A.UserID = B.UserID
> WHERE A.UserName = 'Marx'
> AND A.UserPassword = 'swordfish'

OK, I'm executing following code (via remoting):

public bool LoggedIn(string userName, string userPassword)
{

SqlCommand findLoginSessionSqlCommand;
SqlParameter userNameParameter;
SqlParameter userPasswordParameter;
SqlDataReader findLoginSessionSqlDataReader;

try
{
runnerSqlConnection.Open();
}
catch
{
throw;
}

findLoginSessionSqlCommand = new SqlCommand("SELECT
U.UserID, L.SessionID FROM Users as U LEFT OUTER JOIN LoginSessions as
L ON U.UserID = L.UserID WHERE U.UserName = @UserName AND
U.UserPassword = @UserPassword", runnerSqlConnection);

userNameParameter = new SqlParameter("@UserName",
userName);
userPasswordParameter = new SqlParameter("@UserPassword",
userPassword);


findLoginSessionSqlCommand.Parameters.Add(userNameParameter);

findLoginSessionSqlCommand.Parameters.Add(userPasswordParameter);

userNameParameter.Value = userName;
userPasswordParameter.Value = userPassword;

findLoginSessionSqlDataReader =
findLoginSessionSqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);

if (findLoginSessionSqlDataReader[1] != null)
<=<=<=<=<=<=<=<=<= LOOK HERE!
{
runnerSqlConnection.Close();
return true;
}
else
{
runnerSqlConnection.Close();
return false;
}
}

On the line with findLoginSessionSqlDataReader[1] I am getting an
exception: InvalidOperationException. Right now the second column
[zero-based: 1] returns NULL because their is no sessions. But I don't
understand that exception!?

--
David Trasbo.

Roy Harvey

3/27/2007 2:55:00 PM

0

This amounts to a new question, one that I am not familiar with. I
suggest starting a new thread for this one in the hope that someone
familiar with this problem will respond.

Roy Harvey
Beacon Falls, CT

On 27 Mar 2007 05:42:28 -0700, "David Trasbo" <davidtrasbo@gmail.com>
wrote:

>OK, I'm executing following code (via remoting):
>
>public bool LoggedIn(string userName, string userPassword)
>{
>
>SqlCommand findLoginSessionSqlCommand;
> SqlParameter userNameParameter;
> SqlParameter userPasswordParameter;
> SqlDataReader findLoginSessionSqlDataReader;
>
> try
> {
> runnerSqlConnection.Open();
> }
> catch
> {
> throw;
> }
>
> findLoginSessionSqlCommand = new SqlCommand("SELECT
>U.UserID, L.SessionID FROM Users as U LEFT OUTER JOIN LoginSessions as
>L ON U.UserID = L.UserID WHERE U.UserName = @UserName AND
>U.UserPassword = @UserPassword", runnerSqlConnection);
>
> userNameParameter = new SqlParameter("@UserName",
>userName);
> userPasswordParameter = new SqlParameter("@UserPassword",
>userPassword);
>
>
>findLoginSessionSqlCommand.Parameters.Add(userNameParameter);
>
>findLoginSessionSqlCommand.Parameters.Add(userPasswordParameter);
>
> userNameParameter.Value = userName;
> userPasswordParameter.Value = userPassword;
>
> findLoginSessionSqlDataReader =
>findLoginSessionSqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);
>
> if (findLoginSessionSqlDataReader[1] != null)
><=<=<=<=<=<=<=<=<= LOOK HERE!
> {
> runnerSqlConnection.Close();
> return true;
> }
> else
> {
> runnerSqlConnection.Close();
> return false;
> }
>}
>
>On the line with findLoginSessionSqlDataReader[1] I am getting an
>exception: InvalidOperationException. Right now the second column
>[zero-based: 1] returns NULL because their is no sessions. But I don't
>understand that exception!?

Rafael Lenartowicz

3/27/2007 5:46:00 PM

0

I don't know the front end language you're using,
but if it's NULL that "bothers" the compiler -
replace first part of your select statement instead of :
"SELECT U.UserID, L.SessionID FROM ... "
use
"SELECT U.UserID, IsNull( L.SessionID, 0 ) FROM ... "
it will return 0 (not null) if no sessions found.

or even simpler - use INNER JOIN. If there is no session for your user id -
no rows will be returned at all.
r



"David Trasbo" <davidtrasbo@gmail.com> wrote in message
news:1174999348.510115.159460@y66g2000hsf.googlegroups.com...
> Roy Harvey <roy_har...@snet.net> wrote:
>> SELECT A.UserID,
>> B.SessionID
>> FROM Users as A
>> LEFT OUTER
>> JOIN LoginSessions as B
>> ON A.UserID = B.UserID
>> WHERE A.UserName = 'Marx'
>> AND A.UserPassword = 'swordfish'
>
> OK, I'm executing following code (via remoting):
>
> public bool LoggedIn(string userName, string userPassword)
> {
>
> SqlCommand findLoginSessionSqlCommand;
> SqlParameter userNameParameter;
> SqlParameter userPasswordParameter;
> SqlDataReader findLoginSessionSqlDataReader;
>
> try
> {
> runnerSqlConnection.Open();
> }
> catch
> {
> throw;
> }
>
> findLoginSessionSqlCommand = new SqlCommand("SELECT
> U.UserID, L.SessionID FROM Users as U LEFT OUTER JOIN LoginSessions as
> L ON U.UserID = L.UserID WHERE U.UserName = @UserName AND
> U.UserPassword = @UserPassword", runnerSqlConnection);
>
> userNameParameter = new SqlParameter("@UserName",
> userName);
> userPasswordParameter = new SqlParameter("@UserPassword",
> userPassword);
>
>
> findLoginSessionSqlCommand.Parameters.Add(userNameParameter);
>
> findLoginSessionSqlCommand.Parameters.Add(userPasswordParameter);
>
> userNameParameter.Value = userName;
> userPasswordParameter.Value = userPassword;
>
> findLoginSessionSqlDataReader =
> findLoginSessionSqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);
>
> if (findLoginSessionSqlDataReader[1] != null)
> <=<=<=<=<=<=<=<=<= LOOK HERE!
> {
> runnerSqlConnection.Close();
> return true;
> }
> else
> {
> runnerSqlConnection.Close();
> return false;
> }
> }
>
> On the line with findLoginSessionSqlDataReader[1] I am getting an
> exception: InvalidOperationException. Right now the second column
> [zero-based: 1] returns NULL because their is no sessions. But I don't
> understand that exception!?
>
> --
> David Trasbo.
>