query problem using sqldatareader

I'm having some trouble reading data from a query. This has to do with a previous posting here:view post 504339

I have corrected that problem but I now only get the response "wrong password" even though the right password is entered. Here is the new code for the click subrouthine. You will notice two different scenarios with one commented out. Both do not work. I created label1 to display the password and it is show correctly. What am I doing wrong?

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
 Dim test As String
 SqlConnection1.Open()
 SqlCommand1.Parameters.Item("@.email").Value = txtUsername.Text
 Dim dr As SqlDataReader = SqlCommand1.ExecuteReader
 If dr.Read() Then
 test = CStr(dr("pass"))
 Label1.Text = test
 'If dr("pass").ToString = txtPassword.Text Then
 'lblMessage.Text = "login successful"
 'Else
 ' lblMessage.Text = "Wrong password"
 'End If
 If String.Compare("test", "txtPassword.text") = 0 Then
 lblMessage.Text = "login successful"
 Else
 lblMessage.Text = "Wrong Password"
 End If
 Else
 lblMessage.Text = "Please register"
 End If
 dr.Close()
 SqlConnection1.Close()
 End Sub

Your problem is you are mixing up literals and variables. test and txtPassword.text arevariables and as such, you need to compare just their values. By using quotes, you are comparing theirnames, which will never be equal.

If String.Compare(test, txtPassword.text) = 0 Then

That said, the way you are storing passwords in clear text is a bad idea. I would strongly consider looking at hashing the password and storing only the hash.On this page is a link to aspform.zip, and aspform.txt which contain source from an article I did in Dr. Dobb's covering this.|||Ooops. I did remove the quotes. I forgot to type that in as well and remove them. Even so...with that change, the line exactly as typed above, it still doesn't work. Same prompt everytime..."wrong password"|||Then either debug the code and do a watch on the values, or do a Response.Write() of each of the values to see what they are. If they are in fact not equal, then the first step is to track back in the code and figure out why not.

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25
Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25
sql

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25

Query problem

I having trouble with the query below. I'm trying to
return the number of calls base on severity. The
#TempTable has 3 severities listed (1,2,&3). There are
no calls for severity 1, but i still want it to return a
record with a count of 0. A sample of what the query is
returning is at the bottom of this message. Please help.
select
Call.severity,
count(call.callno) as callcount
from
Call
left join Severity
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Call.severity
severity callcount
-- --
2 4
3 25Since you've posted no DDL or sample data I can't say for sure, but you can
try:
select
Severity.severityID,
SUM(CASE WHEN Call.Severity = Severity.severityID THEN 1 ELSE 0 END) as
callcount
from
Severity
left join Call
On Call.Severity = Severity.severityID
where
datetimesubmitted between '01/01/2004' and '01/31/2004'
and
status <> 'CANCELLED'
group by Severity.severityID
"Vic" <vduran@.specpro-inc.com> wrote in message
news:179e501c421a7$41aa2060$a001280a@.phx
.gbl...
> I having trouble with the query below. I'm trying to
> return the number of calls base on severity. The
> #TempTable has 3 severities listed (1,2,&3). There are
> no calls for severity 1, but i still want it to return a
> record with a count of 0. A sample of what the query is
> returning is at the bottom of this message. Please help.
>
> select
> Call.severity,
> count(call.callno) as callcount
> from
> Call
> left join Severity
> On Call.Severity = Severity.severityID
> where
> datetimesubmitted between '01/01/2004' and '01/31/2004'
> and
> status <> 'CANCELLED'
> group by Call.severity
>
> severity callcount
> -- --
> 2 4
> 3 25

Query performance problem

I'm having trouble figuring out why a query is having
varying response time. The following query takes anywhere
from a 2 seconds to 20 minutes:
select *
from tbl (nolock)
where ACTIVE = 1
There are 17 rows returned in the table. The largest data
in the text field is 34 KB, with most around 20 KB.
Anybody have any ideas?
Table structure is as follows:
CREATE TABLE [tbl] (
[ID] [int] IDENTITY (40, 1) NOT NULL ,
[ACTIVE] [bit] NOT NULL ,
[NAME] [varchar] (50) COLLATE NULL ,
[FILE] [text] NULL ,
CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
(
[SHEET_ID]
)
)What indexes do you have? And how many rows in the table? What query plan
does the optimizer select? Does it use an index on the Active column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||There is only one index, The PK on the field "ID" (see DDL
below).
There are 21 rows in the table, the query returns 17.
Notice that there is a text column in the tbl (20-30KB).
The optimizer does a full tablescan on the table. Also
note, I am running a trace for that table and there is not
any update DML.
Thanks,
Mark

>--Original Message--
>What indexes do you have? And how many rows in the table?
What query plan
>does the optimizer select? Does it use an index on the
Active column?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <anonymous@.discussions.microsoft.com>
wrote in message
>news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
anywhere
data
>
>.
>|||OK. Well, you could try creating a supporting index for the query, but it
seems like the major issue here is actually accessing the BLOB data. Bit
still it is not so much data that I would suspect so varying response times.
And just to be certain, check for blocking (I know you mention you are
running a profiler trace, but just to be certain). I guess you could
investigate where the wait time is, a recent SQL Server Magazine had a nice
article regarding this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
> There is only one index, The PK on the field "ID" (see DDL
> below).
> There are 21 rows in the table, the query returns 17.
> Notice that there is a text column in the tbl (20-30KB).
> The optimizer does a full tablescan on the table. Also
> note, I am running a trace for that table and there is not
> any update DML.
> Thanks,
> Mark
>
> What query plan
> Active column?
> wrote in message
> anywhere
> data|||Have you attempted to call just the column names instead of using the "*"?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||Another table has the "id" field as a fk. Could it be that
an exclusive lock is placed on the "id" when the other
table is inserted or updated?

>--Original Message--
>OK. Well, you could try creating a supporting index for
the query, but it
>seems like the major issue here is actually accessing the
BLOB data. Bit
>still it is not so much data that I would suspect so
varying response times.
>And just to be certain, check for blocking (I know you
mention you are
>running a profiler trace, but just to be certain). I
guess you could
>investigate where the wait time is, a recent SQL Server
Magazine had a nice
>article regarding this.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
>news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
DDL
not
table?
>
>.
>|||Yes, that is possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:c49201c40c6c$0fc53440$a601280a@.phx.gbl...
> Another table has the "id" field as a fk. Could it be that
> an exclusive lock is placed on the "id" when the other
> table is inserted or updated?
>
> the query, but it
> BLOB data. Bit
> varying response times.
> mention you are
> guess you could
> Magazine had a nice
> DDL
> not
> table?

Query Logic - SQL Help

I have some table data and know how I want the results but I'm just having a bit of trouble in constructing the SQL logic to obtain the desired results. There's a site where visitors are able to select from a list of parts, and it will return a set of model/products that they can produce with the selected parts. Here's the data ...

tblModel tblPart
ModelId ModelName PartId PartName
------- -------
1 Alpha 1 CHOO1 Stem
2 Bravo 2 BH034 Rod
3 Bravo Pro 3 HRE Seat

tblModelPart
ModelPartId ModelId PartId
----------
1 1 1
2 2 1
3 2 3
4 3 1
5 3 2
6 3 3

... and here's the logic that I'm trying to implement, assume that the user selects from a form, parts with the PartId 1 and 3 ...

1. Return all models that contain only the parts selected.

ModelId ModelName
-------
2 Bravo

2. Return all models that contain the parts selected, and may contain other parts.

ModelId ModelName
-------
2 Bravo
3 Bravo Pro

... so do you have any idea on how the SQL would look for either of these queries?

Thanks in advance,
GoranWhat have you tried so far?

This sounds a lot like homework to me, and at DBForums we don't do homework outright (although we'll often provide guidance).

For what it is worth, I'd probably read up a bit on the EXISTS clause if I were you.

-PatP|||HOMEWORK!!!

yes, i have a very clear idea what the SQL would like like

you must give it a try yourself, nobody here does homework assignments

however, we will help you with it, once you put in the necessary effort

:)|||rats, sniped again :(|||lmao, never been accused of that before, my programming skills (and age) are much beyond student level but my SQL leaves alot to be desired. The tables have been simplified and context changed to protect the nature of the site ;) so I'm sorry if it appears too simple.

One thing I've tried is querying the table tblModelPart for the selected parts, grouping on ModelId and if the count matches the number of components selected on the client side form then voila! you have a match. It didn't work out so well and didn't get me any further to sussing out the 2nd piece of logic.

I've never really used the EXISTS clause (yes I know, blasphemy!) but will check it out later. Thanks for the pointer ;)|||One thing I've tried is querying the table tblModelPart for the selected parts, grouping on ModelId and if the count matches the number of components selected on the client side form then voila! this is the solution i would have offered, it works ~so~ well

could you show your query please?|||I think I may of had a case of things-always-seem-much-simpler-when-explaining-to-someone-else syndrome :s The query I was using has been left at home so I decided to quickly knock one up as example and it seems to work now.

SELECT ModelId
FROM tblModelPart
WHERE (PartId = 1 OR PartId = 3)
AND (SELECT COUNT(*) FROM tblModelPart AS tblModelPart2 WHERE tblModelPart2.ModelId = tblModelPart.ModelId) = 2
GROUP BY ModelId
HAVING COUNT(ModelId) = 2

Include the line in red for logic #1 and exclude it for #2. Is it really that simple or am I missing something completely obvious? I'd still be interested in seeing if there's a way using EXISTS.|||since we are looking for presence or absence of more than one row, i.e. a group, therefore GROUP BY is necessary
SELECT ModelId
FROM tblModelPart
GROUP
BY ModelId
HAVING ...this is the basic approach, which you anticipated

now for specific situations, simply count what you want

for #1, use
HAVING SUM(CASE WHEN PartId IN ( 1,3 )
THEN 1 ELSE 0 END) = 2
AND SUM(CASE WHEN NOT PartId IN ( 1,3 )
THEN 1 ELSE 0 END) = 0
for #2, remove the AND condition|||actually, #1 can be simplified (and made more efficient) like this:SELECT ModelId
FROM tblModelPart
WHERE PartId IN ( 1,3 )
GROUP
BY ModelId
HAVING COUNT(*) = 2this means fewer rows go into the grouping process