CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
Thanks in advance,
sck10
I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

Case Sensitive?

Dear everyone,

I am doing Login webform (C# .NET web application) with SQL Server 2000.

The staff table is to store authenticated user info.

But when I test it, I found that the password can be case insensitive, i.e. 'A0001' should be correct password, but 'a0001' can allow login.

Could anyone tell me how to solve this problem??

Thanks you very much!!

private void btnLogin_Click(object sender, System.EventArgs e)
{
//instantiate SQL connection
SqlConnection sqlConnect = new SqlConnection(connectStg);
SqlCommand selectLogin = sqlConnect.CreateCommand();
selectLogin.CommandText = "SELECT sid, type from STAFF Where sid= '" + txtId.Text + "' and pwd= '" + txtPwd.Text + "' ";
//open connectin for execution
sqlConnect.Open();
//instantiate the SqlDataReader reader
SqlDataReader loginReader = selectLogin.ExecuteReader();
//try and catch SqlException error
try
{
if(loginReader.Read())
{
// check whether the user is the role of administrator or operator
// I use GetValue(1) i.e. type field from the above select statement // if "O' then go operator page, else go to administrator page.
if (loginReader.GetValue(1).ToString().ToUpper().Equals("O"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginOper.aspx");
}
else if (loginReader.GetValue(1).ToString().ToUpper().Equals("A"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginAdmin.aspx");
}
}
else
{
//clear content of textbox and display error message
txtId.Text="";
txtPwd.Text="";
lblLoginFail.Visible = true;
lblLoginFail.Text="Login Failed!<br>" + "Ensure that ID and Password are correct!";
}
}
catch (SqlException se)
{
if (se.Number == 17)
{
lblLoginFail.Visible = true;
lblLoginFail.Text = "Could not connect to the database";
}
else
{
lblLoginFail.Visible = true;
lblLoginFail.Text = se.Message;
}
}
//close SqlDataReader and SqlConnection
loginReader.Close();
sqlConnect.Close();

You can alter the database to be case sensitive, and I think you can also do that on a per connection basis - but you'd have to check that. The other way could be to return the passwords that have matched and then double check them in c#. I sure someone has a better method.|||Case-sesitivity is determined when installing SQL Server,
try running sp_help to see the current settings.
Passwords shouldn't be stored in plaintext in the database
anyway. I suggest you have a look at the hashing functions
in .Net and use them to calculate a hash and then save that
in the database.
Then you wouldn't have to worry about case-sensitivity either.|||Thanks you for reply!!

As you said running sp_help to see the current settings, how to change the current settings of case-sensitive problems.

I recognise that the passwords should be better stored in encrypted forms. But how to encrypt it in SQL Server. I am new in web development. Could you briefly tell me how to do? Or any web reference provided?

Waiting for reply! Thanks

Roy|||::As you said running sp_help to see the current settings, how to change the current settings
::of case-sensitive problems

He DID tell you it is determined on install time. So you can not change it.

::I recognise that the passwords should be better stored in encrypted forms.

Good. You are wrong, though. Storing encrypted passwords in SQL Server is as bad as storing them plain text. Hashing is not encryption.

::But how to encrypt it in SQL Server.

Why should you?

Hash (not encrypt) the passwords on the website, then store he hashed passwords in the server.

In the SQL only ask for the user's data by user name, retrieve the password hash from the server, hash the user input and compare. Do not forget to salt your hashes, as otherwise you are totally open to a dictionary attack.

::I am new in web development.

Not to development in general? Sounds more like this. I would suggest you invest heavily into some books.|||First of all you should be aware that the case-sensitivity settings are GLOBAL to the entire SQL Server and all databases on it.
If you really want to to the change you have to rebuild the master database using
Rebuildm.exe.
Do look it up in the books online first, and don't forget to backup your database before!

For hashing password have a look at the classes:
System.Security.Cryptography.MD5
or preferrably
System.Security.Cryptography.SHA1

Case insensitivity problem

I have an application that needs to check users' log on credentials from a
web front end.
The web front end passes the user name and password to a stored procedure
and, if the stored procedure finds someone with those credentials then it
returns the user's ID.
Trouble is that SQLServer has been installed to be case insensitive, so
"password" = "PASSWORD"
Is there anything that I can do in the stored procedure that can make the
select statement case sensitive for this particular query?
Thanks
Griff> Is there anything that I can do in the stored procedure that can make the
> select statement case sensitive for this particular query?
Of course.
http://www.aspfaq.com/2152|||http://vyaskn.tripod.com/case_sensi..._sql_server.htm
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Griff" <Howling@.The.Moon> wrote in message
news:OS5efhMqFHA.1556@.TK2MSFTNGP12.phx.gbl...
> I have an application that needs to check users' log on credentials from a
> web front end.
> The web front end passes the user name and password to a stored procedure
> and, if the stored procedure finds someone with those credentials then it
> returns the user's ID.
> Trouble is that SQLServer has been installed to be case insensitive, so
> "password" = "PASSWORD"
> Is there anything that I can do in the stored procedure that can make the
> select statement case sensitive for this particular query?
> Thanks
> Griff
>|||Try to convert them to a binary and then compare...
Marcel van Eijkel
( www.vaneijkel.com )sql

Case Insensitivity

I have a SQL 2000 database. I have a ASP.NET web app that I use to
search this database. I need to make my data case insensitive,
espcially my last name column. How do I change this?

Thanks,
BrianI was doing some further reading and I am hearing that you set case
sensitivity when you first install SQL by choosing an ANSI set and the
only way to change this is to re-install SQL. Is this correct? There
has to be another way around this...|||See "Specifying Collations" and "Collation Precedence" in Books
Online. You can change the collation at the database or column level
(see ALTER DATABASE and ALTER TABLE), or in your queries (see COLLATE).

Personally, I would modify the queries (or perhaps create a view)
rather than have one or two columns in a database in a different
collation from the rest.

Simon|||There is another way in SQL2000. Collation is determined at column
level so you can alter the case-sensitivity and other collation
properties at any time. For example:

ALTER TABLE YourTable
ALTER COLUMN last_name VARCHAR(50)
COLLATE Latin1_General_CI_AS

Read the Collations topics in Books Online to understand the collation
syntax and how this affects comparisons between columns of different
collation.

--
David Portas
SQL Server MVP
--|||I used your syntax and everything works like a charm except for one
thing, now when I do a search, such as "W" in the lastname field, it
pulls every records that contains a "W" in the last name, rather than
names that start with "W". How do you correct this? it needs to search
from left to right.

Thanks,
Brian|||What's the SQL statement you are using to SELECT? It sounds like
you're putting a wildcard in front of and behind the character you are
searching on, e.g.:

SELECT ColName
FROM Table
WHERE ColName LIKE '%W%'

when it sounds like you want the wildcard after

SELECT ColName
FROM Table
WHERE ColName LIKE 'W%'

Your collation settings should only affect the case sensity of the
database; not how your LIKE comparisons perform. Am I
misunderstanding?

Stu

Case Expression in SQL Server 2000

I have to use the Case expression in my query, so I search arround the web and got the following:

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

It should run OK base on my research in the internet. But my SQL Server gave me error:

syntax error arround '>'.

I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.

can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.

Thanks!

Try this:

SELECT title, price, CASE price AS Budget
WHEN > 20.00 THEN 'Expensive'
WHEN BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

|||

come out error too.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '>'.

|||

SELECT title, price,

CASE

WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END AS Budget
FROM titles

|||

The case expression has 2 formats:

Simple CASE function:

CASEinput_expression
WHENwhen_expressionTHENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

Searched CASE function:

CASE
WHENBoolean_expression THENresult_expression
[ ...n]
[
ELSEelse_result_expression
]
END

So you're using the simple CASE function when you add 'price' column following CASE keyword. Note this will evaluateinput_expression, and then, in the order specified, evaluatesinput_expression=when_expression for each WHEN clause. That's why CASE price WHEN price>N doesn't work. For more information about CASE, you can take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp

|||

Great, Thanks so much for you answer! funny thing is I searched so many web sites, they all have bad Case expression..

CAS

I'm having a difficult time wrapping my head around Code Access Security.
We have just installed Sharepoint Portal Server 2003. I have a web part that
uses the obc.net dataprovider that is causing me a security error when I try
to upload the web part.
I'm just not understanding what I need to change on the server to allow this
code to run. Since very few people will be able to add web parts does it
make sense just to take the security down to a lower level? This is on our
corporate intranet. If so, can someone explain to me how to lower the
security for the whole site?
Thanks,
JasonSorry - wrong newsgroup
"Jason MacKenzie" <jmackenzie_nospamallowed@.formet.com> wrote in message
news:uunGCpJpEHA.896@.TK2MSFTNGP12.phx.gbl...
> I'm having a difficult time wrapping my head around Code Access Security.
> We have just installed Sharepoint Portal Server 2003. I have a web part
> that uses the obc.net dataprovider that is causing me a security error
> when I try to upload the web part.
> I'm just not understanding what I need to change on the server to allow
> this code to run. Since very few people will be able to add web parts
> does it make sense just to take the security down to a lower level? This
> is on our corporate intranet. If so, can someone explain to me how to
> lower the security for the whole site?
> Thanks,
> Jason
>