HELP PLEase with SQL form data entry - Database Discussion Boards

originSH15-Jun-07 5:00

15-Jun-07 5:00

How would this WHERE caluse return no results?

WHERE UniqueID = 'C7B411C6-E17D-454a-9B7B-1A170F33A072'
OR UniqueID != 'C7B411C6-E17D-454a-9B7B-1A170F33A072'

There are items in the IS catalog, if i remove the WHERE clause they are returned. If I do a similar query using Contains() then its fine too.

WHERE Contains(UniqueID,'C7B411C6-E17D-454a-9B7B-1A170F33A072')

Now I don't want to jump the gun and suggest there is something fundimentally wrong here without fully understanding IS and its Sql Query language but equality is pretty basic, it's either equal to or not equal to.

Is this a case where FILE_NOT_FOUND might come in?

(WTF FTW Laugh | :laugh:

)

Any insight into this wierd behaviour would be great.

Andy

EDIT: Forgot to mention this is on Windows Xp Pro, where UniqueID is a cached VT_LPSTR, 40 length.
(Yes I know I cross posted ... naughty me :P but I'm really not sure usre how much user cross over the OS and Sql forums get Wink | ;)

)

NULL vs '' (Nothing)

dboy22115-Jun-07 4:24

dboy221

15-Jun-07 4:24

I have a sql statement where I am trying to pull all records that have a value as well as those records that do not have a value for a particular attribute in a table. To pull the values that were Null I used the following condition "OR cd.BCAT IS NULL" (where BCAT is the attribute) When adding this condition to my sql statement none of the records that have NULL values are returned. When checking the design of the table in SQL Server, I noticed that whoever set the table up, specified the default value for this particular attribute to '' (which I assume is nothing as opposed to null). Knowing this I changed the condition to "OR cd.BCAT IS ''". However, when doing this it seems like the query takes for ever, does not return anything, and basically hangs up. Is there a difference between NULL and ''. If so how can I return records that have '' as the value? Thanks in advance to all that reply.

Re: NULL vs '' (Nothing)

Pete O'Hanlon15-Jun-07 4:30

Pete O'Hanlon

15-Jun-07 4:30

NULL is the absence of a value. '' is a value - an empty string. Basically, if you have something which does not have a valid value, e.g. an optional date, you would normally use a nullable column. This means that you don't have to store a "magic" value to identify these values.

Deja View - the feeling that you've seen this post before.

Re: NULL vs '' (Nothing)

dboy22115-Jun-07 4:46

dboy221

15-Jun-07 4:46

Thanks Pete. Making the column's default value NULL should make my sql statement work. However, w/o jeopardizing the integrity of the database (i.e. relationships, data), I am assuming that making this change will only affect the values in this table (and not update other tables where this value may be referenced). Secondly will I need to do an update on existing records such that they now incorporate the NULL value or will making this change apply it to existing records? Is there absolutely any way to check for ''? (if not I will try the suggested) Thanks, again.

Re: NULL vs '' (Nothing)

Pete O'Hanlon15-Jun-07 5:13

Pete O'Hanlon

15-Jun-07 5:13

Alternatively you could just add WHERE (column IS NULL or column = '') to your query. It's not pretty, but it would work.

Deja View - the feeling that you've seen this post before.

Re: NULL vs '' (Nothing)

andyharman15-Jun-07 7:38

andyharman

15-Jun-07 7:38

Or WHERE IsNull(column, '') = ''

Re: NULL vs '' (Nothing)

PIEBALDconsult15-Jun-07 4:32

PIEBALDconsult

15-Jun-07 4:32

dboy221 wrote:
"OR cd.BCAT IS ''".

Perhaps you mean OR cd.BCAT = ''?

HELP PLEase with SQL form data entry

theimmortalmoo15-Jun-07 4:21

theimmortalmoo

15-Jun-07 4:21

my boss sent me this, I dont know how to do any kinda SQL

Hey Danny, can you make a data entry form so Jane can type customers' email addresses into our database? It needs to have two fields: store number and email address, and a submit button that inserts into sutherlands_mailing_list. Can you save it as www.webice.net/addresses.asp

and the second email she sent

Either way - you're connecting to a table on the SQL server, which is called sutherlands_mailing_list, not an access database. Look at the data entry pages on www.sutherlandshomedesigncenter.com/manager to see how to connect. Your connection string is already built if you use the file include 'database_connect' - you basically just have to write a simple SQL INSERT statement.

can anyone help me with this?

Re: HELP PLEase with SQL form data entry

leckey15-Jun-07 5:29

leckey

15-Jun-07 5:29

Does your boss know you do not know SQL? If so you should ask for time to study SQL, especially if you will be in charge of the upkeep of this. If you gave your boss the impression you do know SQL and really don't all I can say is you dug your own hole.

Also it looks like you posted about this in another forum (ASP). Please do not crosspost.

______________________
stuff + cats = awesome

Re: HELP PLEase with SQL form data entry

theimmortalmoo15-Jun-07 5:33

theimmortalmoo

15-Jun-07 5:33

sorry about the cross post, and i did not tell her that i knew it or hinted at it. I will have a talk i guess

Re: HELP PLEase with SQL form data entry

leckey15-Jun-07 5:59

leckey

15-Jun-07 5:59

United States law says that (as long as you did not claim at some point you did know) if you do not know how to do something that is part of your job, the company is required to give you the necessary time and resources (book, class, etc) for you to learn the process in a reasonable amount of time on the company dollar.

______________________
stuff + cats = awesome

mysql exchange data

p_15-Jun-07 0:31

15-Jun-07 0:31

hi all
sir i have made a database and a table and add data in tables in mysql

but then i reinstall another version of mysql .
now i cut the data folder from older version and paste it
into newer version
now when i use show databases in newer version of mysql
it will show that database and tables also
but when i try to show values in tables it will give an error that this table
does not exists
please sir

thanks

Re: mysql exchange data

Pete O'Hanlon15-Jun-07 1:15

Pete O'Hanlon

15-Jun-07 1:15

You've already posted this question - just below in fact. Please don't repost.

Deja View - the feeling that you've seen this post before.

Re: mysql exchange data

Colin Angus Mackay15-Jun-07 4:13

Colin Angus Mackay

15-Jun-07 4:13

Pete O`Hanlon wrote:
You've already posted this question - just below in fact. Please don't repost

Actually, the post below was orginally asking if it was okay to post a question on MySQL in this forum. When I replied that it was okay he then changed the content of the post to the question he wanted to ask.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: mysql exchange data

Pete O'Hanlon15-Jun-07 4:31

Pete O'Hanlon

15-Jun-07 4:31

Hmmmm. Oh well - still breaching the "rules" though. Big Grin | :-D

Deja View - the feeling that you've seen this post before.

permission [modified]

p_14-Jun-07 23:40

14-Jun-07 23:40

thanks sir
hi all
sir i have made a database and a table and add data in tables in mysql

but then i reinstall another version of mysql .
now i cut the data folder from older version and paste it
into newer version
now when i use show databases in newer version of mysql
it will show that database and tables also
but when i try to show values in tables it will give an error that this table
does not exists
please sir

thanks Rose | [Rose]

-- modified at 6:04 Friday 15th June, 2007

Re: permission

Colin Angus Mackay14-Jun-07 23:49

Colin Angus Mackay

14-Jun-07 23:49

paras_s12 wrote:
can i ask a query related to mysql please in this section

Yes

Re: permission

Colin Angus Mackay14-Jun-07 23:57

Colin Angus Mackay

14-Jun-07 23:57

I should add that unless your question relates to the way MySQL interacts with .NET applications you may find the responses limited. If it is a purely MySQL question then a forum that deals with that would more likely be useful to you. People will still be able to help you, there are just less of them specialised in that technology here.

Welcome anyway. Big Grin | :-D

Two fields can't repeat

N a v a n e e t h14-Jun-07 22:54

N a v a n e e t h

14-Jun-07 22:54

Assume that I have a table with UserName and Password columns. Both are varchar type. I given primary key for UserName. But we got another requirement that Password also should not repeat. So I tried setting primary key for Password also. But my first attempt resulted in failure. SQL said you can't create multiple primary keys on a table. Is there a concept called composite key ? Is it related to my requirement ? Or is there any other way to make both fields to be acted like primary key (Both fields should not repeat).? Confused | :confused:

Navaneeth
My Website

Re: Two fields can't repeat

Colin Angus Mackay14-Jun-07 23:05

Colin Angus Mackay

14-Jun-07 23:05

You can mark a column as UNIQUE which ensures its value doesn't repeat. However, I don't believe that fits your requirements.

What if two independent users happen, by chance, to pick the same password? What if one of them is mallicious? The fact the second user cannot choose their password means they know someone else already has that password. Now their brute force attack on the system is faster because they have a known password and just have to match it to an existing user.

Most systems don't allow a user to duplicate a password they've used in the past. I would guess that it your requirement. In which case you need to store a history of passwords for each user.

Re: Two fields can't repeat

N a v a n e e t h14-Jun-07 23:14

N a v a n e e t h

14-Jun-07 23:14

Colin Angus Mackay wrote:
What if two independent users happen, by chance, to pick the same password?

Thanks, But this is not the real scenario. I just given a simple example about my requirement. I am using this in another table. Not using for users. Any other way to ensure both of the fields won't repeat ?

Navaneeth
My Website

Re: Two fields can't repeat

Christian Graus14-Jun-07 23:30

Christian Graus

14-Jun-07 23:30

What he told you to do, solves the problem. He was just telling you why not to do it with passwords.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Re: Two fields can't repeat

Colin Angus Mackay14-Jun-07 23:54

Colin Angus Mackay

14-Jun-07 23:54

Navaneeth. wrote:
Thanks, But this is not the real scenario

Why not tell us the real scenario? It would have mean I didn't have to write all those warnings about security which are, only now, obviously not needed.

Sometimes I really wonder whether I'm wasting my time helping here or not.

Re: Two fields can't repeat

N a v a n e e t h15-Jun-07 1:12

N a v a n e e t h

15-Jun-07 1:12

Real scenario is, I store ItemCode and ItemName in one table. ItemCode is primary key. I don't want to repeat ItemName also. For that I tried this. Actually I just given a example about username and password to understand the problem easily. But I think I am mistaken and my first example made lot of confusion. Sorry.

Colin Angus Mackay wrote:
Sometimes I really wonder whether I'm wasting my time helping here or not.

Never. Your postings are excellent. You are doing good job. Thanks

Navaneeth
My Website

Re: Two fields can't repeat

Arun.Immanuel15-Jun-07 1:19

Arun.Immanuel

15-Jun-07 1:19

Colin Angus Mackay wrote:
Sometimes I really wonder whether I'm wasting my time helping here or not.

Not only the poster whom you are replying gets benefited.
It helps everyone who sees you reply.

Colin Angus Mackay wrote:
I didn't have to write all those warnings about security

These warning may help others.;)

So you are not wasting you time.

Regards,
Arun Kumar.A

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.