Database

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

SELECT * FROM dbo.Function1(@UserName)

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

Private Structure UserData<br />
        Dim strRecordID As Integer<br />
        Dim strAllowed As Integer<br />
        Dim strName As String<br />
        Dim strSurname As String<br />
        Dim strClass As String<br />
        Dim strEmail As String<br />
        Dim strUsername As String<br />
        Dim strPassword As String<br />
        Dim strSkin As String<br />
        Dim strImagePath As String<br />
End Structure

ALTER FUNCTION dbo.Function1<br />
	(<br />
		@Username   varchar(50)<br />
	)<br />
RETURNS TABLE<br />
AS<br />
	RETURN ( SELECT * FROM Users WHERE Username = @Username)

Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn)<br />
       cmd.CommandType = CommandType.StoredProcedure<br />
       cmd.Parameters.Add("@Username", txtUsername.Text)<br />
       cmd.Parameters.Add("@Password", inputPassword1.Value)

CREATE PROCEDURE GetUserDetails(@UserName varchar(64), @Password varchar(64))
AS
    -- This should always return zero or one records only.
    SELECT RecordId, Allowed, Name, Surname, Class, Email, UserName, Password, Skin, ImagePath
    FROM Users
    WHERE Username = @UserName
    AND Password = @Password
GO

Dim cmd As New SqlClient.SqlCommand("GetUserDetails", vilemConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", txtUsername.Text)
cmd.Parameters.Add("@Password", inputPassword1.Value)

' The data from the Select can be returned in to a DataSet or through a DataReader
' As you are populating your own structure it is probably better to use the DataReader
Dim dataReader As cmd.ExecuteDataReader()
Dim details As New UserData()
If dataReader.Read()
Then
    ' A record has returned from the Stored Procedure
    ' The numbers in the GetInt32 / GetString methods refer to 
    ' the position of the field. So, care must be taken to SELECT
    ' the fields in the same order in the stored procedure.
    details.strRecordId = dataReader.GetInt32(0)
    details.strAllowed= dataReader.GetInt32(1)
    details.strName = dataReader.GetString(2)
    details.strSurname = dataReader.GetString(3)
    details.strClass = dataReader.GetString(4)
    details.strEmail = dataReader.GetString(5)
    details.strUserName = dataReader.GetString(6)
    details.strPassword = dataReader.GetString(7)
    details.strSkin = dataReader.GetString(8)
    details.strImagePath = dataReader.GetString(9)
Else
    ' There is no record, therefore the user name and password don't match
    ' TODO: Put code here that will handle the failed login details.
End If

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

CREATE PROCEDURE UpdateUserDetails(@RecordID int, @Allowed int, @Name varchar(64), @Surname varchar(64),
@Class varchar(64), @Email varchar(64), @Username varchar(64), @Password varchar(64),
@Skin varchar(64), @ImagePath varchar(64))
AS

-- Check to make sure the data exists already to be updated.
IF EXISTS(SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password)
BEGIN
    -- Perform the update, all fields, except UserName and Password are updated here
    UPDATE Users
    SET Allowed = @Allowed, Name = @Name, Surname = @Surname -- and so on....
    WHERE UserName = @UserName AND Password = @Password
END
ELSE
BEGIN
    -- THere is no existing data to be updated, Raise and error - This will cause
    -- a SqlException to be thrown in the .NET Application.
    RAISERROR('The User does not exist', 16, 1);
    -- Alternatively, an INSERT could be performed here.
END
GO

Dim cmd As New SqlClient.SqlCommand("UpdateUserDetails", vilemConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", details.strUserName)
cmd.Parameters.Add("@Password", details.strPassword)
' Keep adding parameters until all the parameters that the stored procedure takes are in.
' For consistency the parameters should be in the same order they appear in the stored procedure
' definition.

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!