log in function

[ilyail3]

I want to build a T-SQL function on my database server that will get the
parameters user_name,password and return a boolean value if such a user
exist in the table Users. What kind of function I need and what should it look
like?

 

[levyuk]

ok try something like this

create procedure sp_SelectUser
 
(
@username text,
@password text
)
 
AS
 
DECLARE @Exists INT
 
IF EXISTS(SELECT * FROM users WHERE username = @username AND password = @password)
SELECT @Exists = 1
ELSE
SELECT @Exists = 0
RETURN @Exists
 
END

That should work, it might not because i haven't tried at, it's off the top of my head so good luck

It should give you some ideas though

 

[ilyail3]

It says :"ADO error:The text, ntext and image data types cannot be compared or stored, exept when using IS NULL or LIKE operator.
The text, ntext and image data types cannot be compared or stored, exept when using IS NULL or LIKE operator."
that's annoying, that's the reason I asked this question.

 

[levyuk]

ok try this

create procedure sp_SelectUser
 
(
@username text,
@password text
)
 
AS
 
DECLARE @Exists INT
 
IF EXISTS(SELECT * FROM users WHERE username LIKE @username AND password LIKE @password)
SELECT @Exists = 1
ELSE
SELECT @Exists = 0
RETURN @Exists
 
END

 

[ilyail3]

Problems again, for some reason, when I write the value as a string I get the right answer but when I use the Variables(@username,@password) I always get positive!

 

[levyuk]

Do you mean that in your program when you supply the values as parameters then it always returns 1 or is it when you just run the stored procedure in sql server it always returns 1?

 

[ilyail3]

For example I have used it as a function:

ALTER FUNCTION ilyail3.Function2
 (
 @username text,
 @password text
 )
 RETURNS TABLE
 AS
 	RETURN (SELECT * 
 			FROM Users 
			WHERE Users.[User name] LIKE @username AND Users.Password LIKE @password)

and called it the event:

 Dim comi As New SqlClient.SqlCommand
		comi.CommandText = "SELECT * FROM Function2('" & TxtUSR.Text & "','" & TxtPass.Text & "')Function2"
 		SqlDataAdapter2.UpdateCommand = comi
 		DataSet1.Clear()
 		SqlDataAdapter2.Fill(DataSet1, "Function2")
 		Me.BindingContext(DataSet1, "Function2").Position = 1

I'm have the first row after this event!
What have I done wrong?

 

[levyuk]

That doesn't make much sense to me because i've never used it as a function. I've changed it around a bit now. This is what I have done to get it to work.
Stored procedure

[size=2][color=#0000ff]ALTER PROCEDURE [/color][/size][size=2]sp_getusers 

(

@Username [/size][size=2][color=#0000ff]text[/color][/size][size=2],

@Password [/size][size=2][color=#0000ff]text[/color][/size][size=2],

@Rowcount [/size][size=2][color=#0000ff]int output

[/color][/size][size=2])

[/size][size=2][color=#0000ff]AS

SELECT [/color][/size][size=2]Username, Password

[/size][size=2][color=#0000ff]FROM [/color][/size][size=2]users

[/size][size=2][color=#0000ff]WHERE [/color][/size][size=2](Username [/size][size=2][color=#0000ff]LIKE [/color][/size][size=2]@Username) [/size][size=2][color=#0000ff]AND [/color][/size][size=2](Password [/size][size=2][color=#0000ff]LIKE [/color][/size][size=2]@Password)

[/size][size=2][color=#0000ff]select [/color][/size][size=2]@rowcount=@@rowcount 

[/size][size=2][color=#0000ff]RETURN 

[/color][/size]

And the VB.net code

[size=2][color=#0000ff]Imports[/color][/size][size=2] System.Data.SqlClient

[/size][size=2][color=#0000ff]Imports[/color][/size][size=2] System.Data

[/size][size=2][color=#0000ff]Public[/color][/size][size=2] [/size][size=2][color=#0000ff]Class[/color][/size][size=2] Form1

[/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button1_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button1.Click

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] conn [/size][size=2][color=#0000ff]As[/color][/size][size=2] SqlConnection = [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlConnection([/size][size=2][color=#800000]"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Jon\Local Settings\Application Data\Temporary Projects\WindowsApplication1\Database1.mdf;Integrated Security=True;User Instance=True"[/color][/size][size=2])

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] ds [/size][size=2][color=#0000ff]As[/color][/size][size=2] DataSet

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] adapter [/size][size=2][color=#0000ff]As[/color][/size][size=2] SqlDataAdapter

[/size][size=2][color=#0000ff]Try

[/color][/size][size=2]adapter = [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlDataAdapter([/size][size=2][color=#800000]"sp_getusers "[/color][/size][size=2], conn)

adapter.SelectCommand.CommandType = CommandType.StoredProcedure

adapter.SelectCommand.Parameters.Add([/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlParameter([/size][size=2][color=#800000]"@Username"[/color][/size][size=2], SqlDbType.Text))

adapter.SelectCommand.Parameters.Add([/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlParameter([/size][size=2][color=#800000]"@Password"[/color][/size][size=2], SqlDbType.Text))

adapter.SelectCommand.Parameters.Add([/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlParameter([/size][size=2][color=#800000]"@Rowcount"[/color][/size][size=2], SqlDbType.Int, 4))

adapter.SelectCommand.Parameters([/size][size=2][color=#800000]"@Username"[/color][/size][size=2]).Value = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBox1.Text

adapter.SelectCommand.Parameters([/size][size=2][color=#800000]"@Password"[/color][/size][size=2]).Value = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBox2.Text

adapter.SelectCommand.Parameters([/size][size=2][color=#800000]"@Rowcount"[/color][/size][size=2]).Direction = ParameterDirection.Output

ds = [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataSet

adapter.Fill(ds, [/size][size=2][color=#800000]"Users"[/color][/size][size=2])

[/size][size=2][color=#0000ff]If[/color][/size][size=2] adapter.SelectCommand.Parameters(2).Value = 0 [/size][size=2][color=#0000ff]Then

[/color][/size][size=2][/size][size=2][color=#0000ff]Me[/color][/size][size=2].Label1.Text = [/size][size=2][color=#800000]"The user doesn't exist"

[/color][/size][size=2][/size][size=2][color=#0000ff]Else

[/color][/size][size=2][/size][size=2][color=#0000ff]Me[/color][/size][size=2].Label1.Text = [/size][size=2][color=#800000]"The user '"[/color][/size][size=2] & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBox1.Text & [/size][size=2][color=#800000]"' exists"

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception

[/size][size=2][color=#0000ff]Me[/color][/size][size=2].Label1.Text = ex.ToString

[/size][size=2][color=#0000ff]Finally

[/color][/size][size=2]adapter.Dispose()

conn.Close()

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub

End[/color][/size][size=2] [/size][size=2][color=#0000ff]Class

[/color][/size]

Hope that helps

 

[ilyail3]

Can I use PROCEDURE to return the whole row, if so how?
I changed my mind and I want to get all the row instead of just a boolean answer in order to know more then just the username.

 

[levyuk]

What is in the whole row? Is it just username and password or are there other things?

 

[ilyail3]

things like User ID,rank and more

 

[ilyail3]

Sorry for that stupid last question, now I know how, 1 question left:
How can I extract data from DS?
For example I need to extract the value of "ID" to the variable "ID", how can I do that?

 

[ilyail3]

Thank you, I found it by myself, thanks for the help!

 

[levyuk]

Ah ok, Could you post the code for getting information from a DS. I would like to know for future reference.

 

[ilyail3]

I used

Dim adapter As SqlDataAdapter
		adapter = New SqlDataAdapter("sp_mine ", SqlConnection1)
		adapter.SelectCommand.CommandType = CommandType.StoredProcedure
		adapter.SelectCommand.Parameters.Add(New SqlParameter("@Username", SqlDbType.Text))
		adapter.SelectCommand.Parameters.Add(New SqlParameter("@Password", SqlDbType.Text))
		adapter.SelectCommand.Parameters("@Username").Value = Me.TextBox1.Text
		adapter.SelectCommand.Parameters("@Password").Value = Me.TextBox2.Text
		ds = New DataSet
		adapter.Fill(ds, "Users")
		If Me.BindingContext(DS, "Users").Count > 0 Then
			user.init(DS.Tables.Item(0).Rows.Item(0))
			StatusBar1.Text = "You'r inside"
		Else
			StatusBar1.Text = "Incorrect log in"
		End If

"User.int" takes the row as a collection and brakes it apart

Public Class User_Type
	Public ID As Integer
	Public UserName As String
	Public Password As String
	Public Email As String
	Public Rank As Integer
	Public UpToDate As Double
	Public Exp As Long
	Public Sub init(ByVal row As Data.DataRow)
		ID = row.Item(0)
		UserName = row.Item(1)
		Password = row.Item(2)
		Email = row.Item(3)
		Rank = row.Item(4)
		UpToDate = row.Item(5)
		Exp = row.Item(6)
	End Sub
End Class

It may not be the best way to do it but it works!