Database

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!

create table foo<br />
(<br />
   bar datetime default getdate()<br />
)<br />

''; shutdown with nowait;

SELECT COUNT(*) FROM Users WHERE login=''; shutdown with nowait;

CREATE PROCEDURE RegisterUser(
   @UserName   varchar(64),
   @Password   carchar(64)
)
AS

IF EXISTS(SELECT * FROM Users WHERE login=@UserName)
BEGIN
   RAISERROR('The user '+@UserName+' already exists', 16, 1);
   RETURN;
END

INSERT INTO Users (login, password)
VALUES(@UserName, @Password)
GO

SqlCommand cmd = new SqlCommand("RegisterUser", vilemConn);
cmd.Parameters.Add(new SqlParameter("@UserName", txtLogin.Text);
cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text); // Don't know what this really is.
try
{
   cmd.ExecuteNonQuery();
}
catch(SqlException sqlEx)
{
   // If the command is duplicated then sqlEx.Message will contain the message defined in the
   // RAISERROR in the stored procedure and the sqlEx.Number will be 50000 (for a custom error)
}

"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

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

Dim vilemConn As New SqlClient.SqlConnection<br />
vilemConn.ConnectionString = "...."<br />
<br />
Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn)<br />
cmd.Connection.Open()<br />
cmd.Parameters.Add("@UserName", txtLogin.Text)<br />
cmd.Parameters.Add("@Password", inputHeslo1.Value)<br />
cmd.ExecuteNonQuery()<br />
cmd.Connection.Close

ALTER PROCEDURE RegisterUser<br />
	(   <br />
		@UserName   varchar(64),   <br />
		@Password   varchar(64)<br />
	)<br />
AS<br />
IF EXISTS(SELECT * FROM Users WHERE login=@UserName)<br />
BEGIN<br />
   RAISERROR('The user CurrentUser already exists', 16, 1);   <br />
   RETURN<br />
END<br />
INSERT INTO Users (login, heslo)<br />
   VALUES(@UserName, @Password)<br />
RETURN