Password lookup !

  • Thread starter Thread starter Ola Myrgart
  • Start date Start date
O

Ola Myrgart

Hi !

I´m seraching for a easy way for users to delete their records. When the
user supply their information, they are requested to submit a password.
This password comes to use when the user wants to delete the
information. If they can supply the required password their information
is deleted. It´s the comparasing with the password stored in my SQL DB I
need help with.

I´m a VB guy so any code in VB would be most helpful.


OM "Myggan"
 
DELETE SomeRecord From MyTable where User_Name = @username and _Password =
@Password if they are in the same table.

If not, just use an exists.

I'm not sure how familiar you are with VB.NET, but basically you'd just
declare a connection

Dim cn as New SqlConnection(ConnectStringHere)
Dim cmd as New SqlCommand(SqlStateMent, cn)
Try
If cn.State <> ConnectionState.Open Then cn.Open
cmd.ExecuteNonQuery
Catch sq as SqlException


Finally
cn.Close
End Try


I'd probably recommend using Stored Proceduers and parameters
www.knowdotnet.com/articles/storedprocsvb.html

HTH,

Bil
 
Hi William !

Thank you for your reply. I will try it out. As a follow up I´d like to
know how I can control/compare the password against already submitted
passwords. I´ve tried to find the right syntax for doing a SELECT
COUNT(*) to se if the supplied passsword exist, but it doesn´t work.
It´s basically those two questions I have.

1. How to control password at submit by SELECT COUNT(*)
2. The DELETE function (This one I can handle now)

Syntax is important.

Then it´s very near to launchdate. If you can help I´d be most greatful.

OM "Myggan"
 
It should work. An alternate method instead of COUNT(*) is using an Output
parameter, but let's see if we can figure out this problem first.

Show me the SELECT Statement.. Are you using Stored Procs and/or Command
Parameters by any chance or Dynamic SQL? One problem you may find is if you
have _Password as VARCHAR 22 in your database, but you declare your
parameters without a size SELECT COUNT(*) FROM MyTable where User_Name =
@Name and _Password = @Password, you'll probably return nothing.

It's critical to know why it's not returning anything. Will the same
command return values in Query Analyzer? If so, you may want to run a trace
and see what's being sent to the db (If you aren't using stored procs, just
?myCommand.CommandText and plug the result into QA and see what happends).

As far as DELETE..what does the table look like? If you have username and
password in the same table (you should normalize your table before
proceeding) then it's straightforward DELETE FROM MyTable WHERE User_Name =
@Username and _Password = @Password. However, let's assume that it's in
another table and it's linked to the password table by UserID.

DELETE FROM ITemsTable Where UserID = (SELECT UserID from UsersTable where
User_Name = @Username and _Password = @Password) And ProductID = @ProductID.

This is just a sample and it's hard to give much more specific quidance
without seeing the tables involved.

Also, I'd REALY SUGGEST that you don't store passwords in the DB in
plaintext. You may want your classes to encrypt/decrypt the data so that if
anything is compromised, you won't give up the keys to the kingdom (you
never know when someone unhappy employee or evil hacker may decide to check
out your table and get busy).

Let me see the tables and I'll do what I can.

Cheers,

Bill
 
Hi again !

I don´t use different tables for UserID and Passwords (yet). I supply
the table and insert function below.



<code>

Function InsertKundAnnonser(ByVal tboxLosen As String, ByVal
tboxBekraftaLosen As String) As Integer

Dim connectionString As String = "server='localhost'; user
id='xxxx'; password='xxxxx'; Database='xxxxx'"
Dim sqlConnection As New SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [kunder] ([Password],
[ConfirmPassword], [Date]) VALUES (@Password, @ConfirmPassword,
getdate())"
Dim sqlCommand As New SqlCommand(queryString, sqlConnection)

sqlCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value
= tboxPassword
sqlCommand.Parameters.Add("@ConfirmPassword",
SqlDbType.VarChar).Value = tboxConfrimPassword

Dim rowsAffected As Integer = 0
sqlConnection.open
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Catch exc as System.Exception
Errorlabel.Text = exc.ToString()
Finally
sqlConnection.Close
End Try

Return rowsAffected
End Function

Sub SkickaAnnons(Sender As Object, e As EventArgs)
If Page.IsValid Then
InsertKundAnnonser(tboxPassword.text, tboxConfirmPassword.text)
Response.Redirect ("search.aspx")
End If
End Sub

</code>

I don´t use StoredProcedure so I think it´s fairly simple for those with
a little more knowledge than me to solve the problem.

I hope this is what you required. I´ve shortened the code to Password,
ConfirmPassword and Date.

Regards !

OM "Myggan"
 
Back
Top