Database

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

Colin Angus Mackay14-Jun-07 23:05

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.

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

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

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 )

Colin Angus Mackay14-Jun-07 23:54

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.

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

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

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

Pete O'Hanlon14-Jun-07 23:05

Pete O'Hanlon

14-Jun-07 23:05

Instead of creating a single primary key per field, you can select all of the fields that you want and create it as a primary key in one go.

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

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

14-Jun-07 23:15

Thanks, I tried that SQL Server created all the selected fields as primary. But still it is checking only the last field. Remaining all fields in the primary list are allowing repeats. Any other alternative for this ?

Navaneeth
My Website

Colin Angus Mackay14-Jun-07 23:52

14-Jun-07 23:52

The primary key as a whole will be unique. Individual elements within the primary key may not be unique.

Pete O'Hanlon15-Jun-07 0:26

Pete O'Hanlon

15-Jun-07 0:26

If the fields must be unique, then you need to implement a unique index on each field that you want to be unique, e.g.

CREATE UNIQUE 
  INDEX [] ON [dbo].[Workspace] ([Description])

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

mysql

WhiteGirl2314-Jun-07 22:48

WhiteGirl23

14-Jun-07 22:48

Hi,
I work on a project which transfer an access database in mysql.

My problem is that I have a function which convert a binary information in mysql and that function doesn't work so good. This function looks like this:

While i <= Len(str)<br />
<br />
                Select Case Mid$(str, i, 1)<br />
<br />
                    Case Chr(0)  ' ASCII NUL<br />
                        'nlstr = ""<br />
                        rstr = "\0"<br />
<br />
                    Case Chr(8)  ' backspace<br />
                        'nlstr = ""<br />
                        rstr = "\b"<br />
<br />
                    Case Chr(9)  ' tab<br />
                        'nlstr = ""<br />
                        rstr = "\t"<br />
<br />
                    Case Chr(180)<br />
                        'nlstr = ""<br />
                        rstr = " '' "<br />
<br />
                    Case "'"<br />
                        'nlstr = ""<br />
                        rstr = "\'"<br />
<br />
                    Case """"<br />
                        'nlstr = ""<br />
                        rstr = "\"""<br />
<br />
                    Case "\"<br />
                        'nlstr = ""<br />
                        rstr = "\\"<br />
<br />
                    Case Chr(10)  ' line feed<br />
                        'nlstr = ""<br />
                        rstr = "\n"<br />
<br />
                    Case Chr(13)  ' carriage return<br />
                        'nlstr = ""<br />
                        rstr = "\r"<br />
<br />
                End Select<br />
<br />
                If rstr <> "" Then<br />
                    str = Left$(str, i - 1) & rstr & Right$(str, Len(str) - i)<br />
                    i = i + Len(rstr)<br />
                    rstr = ""<br />
                Else<br />
                    i = i + 1<br />
                End If<br />
<br />
            End While<br />
<br />

where str is the information.

If you have an idea please help me.

Thanks.

Re: mysql

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

Using MySQL with ADO from MSVC++ 6

14-Jun-07 23:04

Why don't you read this[^] for a better post ?

Navaneeth
My Website

Accius14-Jun-07 21:23

Accius

14-Jun-07 21:23

Hi,
sorry if i crosspost,but i hadn't noticed this forum section,which is more suited to my question.I'm writing an app with MS VC++ 6,using ADO (the oldest one,not ADO.NET) to connect to an Access database.Now iu would like to use a MySQL database instead.Is it possible?What should i change in my code?
I'm kinda confused,because i have found,in MySQL site,the C API for My SQL,but using that would force me to write lots of new code,instead i was thinking that,with ADO,a single change to the connection string would be enough:is that true?

Re: Using MySQL with ADO from MSVC++ 6

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

Re: Using MySQL with ADO from MSVC++ 6

14-Jun-07 23:03

Will this[^] helps you ?

Navaneeth
My Website

Accius15-Jun-07 0:14

Accius

15-Jun-07 0:14

Thank you,i think it will help me Big Grin | :-D

But i have a question,due to my ignorance about databases Roll eyes | :rolleyes:

Reading the article i noticed that for a DSN-less connection there are many alternatives,depending on (i think) the database i would use.For a DSN connection,instead,i have the only ODBC DSN.
So my (newbie) questions are:is DSN a way to standardize connections to a database?Is it related to ODBC or are they two indipendent concepts?

Sub query Problem in sql server 2000 urgent pls

kankeyan14-Jun-07 20:56

kankeyan

14-Jun-07 20:56

hello friends here is the query for report generating depend upon the fromdate and todate this include a subquery.if we are selecting the same month means its not a problem we are moving to previous month it showing an error in subquery like this:

(1002 row(s) affected)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Plz help me friends

here is the syntax:

SELECT isnull((select p.name_first+'['+rtrim(ltrim(str(pid)))+']' from hms_person p where
p.pid = msts.patientid),patientname) as patientname, msts.salesid,billno, billingdate,billAmount,convert(numeric(16,2),(AmountReturned)) as
actualreturn ,convert(numeric(16,2),(amountpaid))Amountpaid,
isnull((select recdamount from trnreceipts tr where viabill=1 and creditcardno is null and tr.billno =msts.billno
and recddate = msts.billingdate),0) as cashchqamt,isnull((select recdamount from trnreceipts tr where viabill=1 and
tr.creditcardno is not null and tr.billno =msts.billno and recddate = msts.billingdate),0) as cardamt,(billamount -
isnull((select recdamount from trnreceipts tr where viabill=1 and tr.billno =msts.billno and recddate =
msts.billingdate ),0)) as credit ,msts.soldby FROM MstSales0706 AS msts where billingdate >='6/1/2007' and
billingdate <='6/14/2007' order by billingdate,msts.salesid

kankeyan

Re: Sub query Problem in sql server 2000 urgent pls

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

14-Jun-07 23:20

Try giving Select top 1 for all the sub queries. This will avoid the error, but remember this is not a good solution. If sub query is returning more than one values, then that shows the failure of your database. May be the unique key you are referring in the sub query got repeated.

Navaneeth
My Website

ado.net connections

rahulesquire14-Jun-07 19:10

rahulesquire

14-Jun-07 19:10

i made a login form with username--textbox,password--textbox and with 1 submit button..then i created 1 database in the name of "pranay" and then i created 1 table in the name of "login" with 2 column 1 for username and another for password..then i wrote some data in username column and password column..
now i want that when i will write the username and password in login form and when i will press the submit button .it should check from the database i created that its correct or not ,if its correct then i want that the page should redirect to another page and if its incorrect the page should direct to the same page...

pls help me in getting the code Rose | [Rose]

pranay akul

Colin Angus Mackay14-Jun-07 22:42

14-Jun-07 22:42

I presume that since you are asking on the SQL forum you are looking for database code. However, currently it would look like you are storing the passwords in clear text - That would be a bit of a security issue.

SELECT COUNT(*) FROM login WHERE username=@UserName AND password=@Password

This will result in a zero if the user name and password combination does not exist. And a 1 if it does exist. Any other value indicates the data is corrupted.

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

14-Jun-07 22:48

rahulesquire wrote:
pls help me in getting the code

How to help you with the entire code for your long story. First you pass the values to a stored procedure which checks the login details. That could be something like this.

CREATE PROCEDURE LoginCheck
(
    @LoginName varchar(50), @Password varchar(50), 
    @IsAuthenticated varchar(4) OUTPUT
)

AS

IF ( SELECT COUNT(*) FROM YourLoginTable WHERE LoginName = @LoginName And
Password = @Password ) = 0
BEGIN
    SET @IsAuthenticated = 'no'
END
Else
BEGIN
    SET @IsAuthenticated = 'yes'
END

Now check the output variable value in the page.

Navaneeth
My Website

Colin Angus Mackay14-Jun-07 22:58

14-Jun-07 22:58

You know, it would be easier to just return the value in a SELECT statement and use ExecuteScalar() - especially given the level of ability of the OP.

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

14-Jun-07 23:10

Thanks, That was a new information for me.

Colin Angus Mackay wrote:
especially given the level of ability of the OP.

I am not clear with this, could you please make it clear ?

Navaneeth
My Website

Colin Angus Mackay15-Jun-07 0:35