Compare values

I have two tables both with first name and last name fields. I want to create
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanks
select ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks
|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks
|||Great that works thanks!!!!! So that shows me the ones that are not there how
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:

> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if the
> values are NULL in both tables. You can add IS NULL criteria to the queries
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>
>
|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
[vbcol=seagreen]
> Great that works thanks!!!!! So that shows me the ones that are not there how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:

Compare values

I have two tables both with first name and last name fields. I want to creat
e
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanksselect ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||Great that works thanks!!!!! So that shows me the ones that are not there ho
w
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:

> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if th
e
> values are NULL in both tables. You can add IS NULL criteria to the queri
es
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>
>|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
[vbcol=seagreen]
> Great that works thanks!!!!! So that shows me the ones that are not there
how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:
>

Compare values

I have two tables both with first name and last name fields. I want to create
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanksselect ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
--
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||Great that works thanks!!!!! So that shows me the ones that are not there how
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:
> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if the
> values are NULL in both tables. You can add IS NULL criteria to the queries
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
> >I have two tables both with first name and last name fields. I want to
> >create
> > a query that pulls the names out of table ctct that are not in table
> > ctctws.
> > I tried this query but does not give me the expected reults
> >
> > select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> > ctctws.c_first_namews
> > from ctct, ctctws
> > where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> >
> > Can anyone suggest away to produce the required result.
> > I Really appreciate any help
> >
> > Thanks
>
>|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
> Great that works thanks!!!!! So that shows me the ones that are not there how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:
> > I suggest NOT EXISTS. Below is an untested example in which I removed the
> > ctctws data from the column list since these refer to non-existing rows:
> >
> > SELECT
> > ctct.c_last_name,
> > ctct.c_first_name
> > FROM ctct
> > WHERE NOT EXISTS
> > (
> > SELECT *
> > FROM ctctws
> > WHERE
> > ctct.c_last_name = ctctws.c_last_namews AND
> > ctct.c_first_name = ctctws.c_first_namews
> > )
> >
> > You could also use LEFT JOIN, but note that unlatching ctctws rows will
> > contain NULL values.
> >
> > SELECT
> > ctct.c_last_name,
> > ctct.c_first_name,
> > ctctws.c_last_namews,
> > c_first_namews
> > FROM ctct
> > LEFT JOIN ctctws ON
> > ctct.c_last_name = ctctws.c_last_namews AND
> > ctct.c_first_name = ctctws.c_first_namews
> > WHERE
> > ctctws.c_last_namews IS NOT NULL
> >
> > Both of these examples will consider NULL values as unmatching, even if the
> > values are NULL in both tables. You can add IS NULL criteria to the queries
> > if you need to consider NULLs as equal for comparison purposes.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Eric" <Eric@.discussions.microsoft.com> wrote in message
> > news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
> > >I have two tables both with first name and last name fields. I want to
> > >create
> > > a query that pulls the names out of table ctct that are not in table
> > > ctctws.
> > > I tried this query but does not give me the expected reults
> > >
> > > select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> > > ctctws.c_first_namews
> > > from ctct, ctctws
> > > where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> > >
> > > Can anyone suggest away to produce the required result.
> > > I Really appreciate any help
> > >
> > > Thanks
> >
> >
> >sqlsql

Compare results from SQL to textbox?

I am trying to make a user authentication system which pulls data from a SQL table (that has columns "UserID" and "PIN" in it).

How can I check to see what the user entered into the "UserID" and "PIN" textbox's on the page and compare those entries to the database to see if they match up? I have been able to make the below code to do the query itself, but I don't know how to check back and see if it actually returned a match or not.

Function CheckLogin(ByVal userID As String, ByVal pIN As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='test'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [UserAuth].[UserID], [UserAuth].[PIN] FROM [UserAuth] WHERE (([UserAuth].["& _
"UserID] like @.UserID) AND ([UserAuth].[PIN] like @.PIN))"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userID.ParameterName = "@.UserID"
 dbParam_userID.Value = userID
 dbParam_userID.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userID)
 Dim dbParam_pIN As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_pIN.ParameterName = "@.PIN"
 dbParam_pIN.Value = pIN
 dbParam_pIN.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_pIN)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(blah)
 Return dataSet
End Function

First, don't use "like". Next, you can check dataSet.Tables[0].Rows[0].Count and if it is non-zero, there is a match.

You might want to look at using a query that returns the Count, and then just call ExecuteScaler() on the command rather than doing what you are doing.

Another thought: rather than storing the PID in clear text, excrypt it, or better yet, use a one-way hash.|||Well I just switched from using the free development tool on www.asp.net to ASP.NET included with Visual Studio and now I'm totally confused. Does anyone have any code snippets to do anything remotely close to this that I could use as an example? I'm so lost.|||This article will show you most of what you need to know.